Displaying a Crosstab Query with a Parameter as Subform Source Object

Ok Access 2010 here.

I have a crosstab, lets call it “TestCrosstab,” that has a parameter to access a form control in the query it uses as a reference. It looks like this:

PARAMETERS [Forms]![TestForm]![TextDaysPast] Short;
TRANSFORM max(val) AS MaxValue
SELECT col AS Criteria
FROM (SELECT [Date], 'Crosstab Column Name 0' as Col, [Query Column Name 0] as  val
  FROM TestQuery
  UNION ALL
  SELECT [Date], 'Crosstab Column Name 1' as Col, [Query Column Name 1] as  val
  FROM TestQuery
  UNION ALL
  '... etc ...
)
GROUP BY col
PIVOT [Date];

It’s source query, as seen above, is called “TestQuery,” and it looks like this:

SELECT SourceDatabase.Date, 
Count(*) AS [Query Column Name 0], 
Sum(IIf(Stuff=Stuff And Stuff=Stuff,1,0)) AS [Query Column Name 1], 
'... etc ...
FROM SourceDatabase INNER JOIN SecondDatabase ON SourceDatabase.ID = SecondDatabase.ID
WHERE (((SourceDatabase.Date) Between Date() And Date()-Forms!TestForm!TextDaysPast))
GROUP BY SourceDatabase.Date;

They both work great when the user enters a number into the form control “TextDaysPast” and you manualy run the queries in a “pop-up.”

What I am trying to do is attach the crosstab above as a source object of a subform purely for display purposes. In VBA, after the user inputs a number into “TextDaysPast,” this line, among others, is called to attach the crosstab to the subform:

Me.SubformDisplay.SourceObject = "Query.TestCrosstab"
Me.SubformDisplay.Requery

Upon entering a number into “TextDaysPast” and executing the query with a command button, the form appears to work correctly (no errors) except the crosstab never displays. If I change the source query to a static “WHERE,” the crosstab would lose it’s need for “PARAMETERS” and displays normally as a subform source object. Something in using “PARAMETERS” does not allow the crosstab to be displayed.

Any ideas on how to get around this? Would combining the source query and crosstab into one big monster fix this?

Thank you for your input!

Answer

You cannot refer to a cross tab with a parameter in a subform, as far as I know. However, you can build the query in VBA, this eliminating the need for a parameter and update the sql of the cross tab query referenced by the subform.

 sSQL="TRANSFORM Sum(Table1.ANumber) AS SumOfANumber " _
     & "SELECT Table1.AText FROM Table1 " _
     & "WHERE Table1.AText='" & Me.txtText & "' " _
     & "GROUP BY Table1.AText PIVOT Table1.AText2"

''Permanently change the sql for MyXtab
CurrentDB.Querydefs("MyXtab").SQL=sSQL
Me.MySubformControlName.Requery