1

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!

Phizon
  • 93
  • 4
  • 14
  • As far as I can tell, the problem is the parameter statement. How do you feel about building the sql for the crosstab in VBA and displaying the result? That way, you will not need a parameter because it will be written into the sql. – Fionnuala Feb 20 '13 at 14:30
  • That sounds like as good a solution as any. Is it possible to use the syntax I have in my queries and "port" them to VBA? Because the form control "TextDaysPast" is easily accessible within the form's VBA, a PARAMETER is not needed for the crosstab, correct? – Phizon Feb 20 '13 at 14:45
  • Yes, just copy the sql to vba being careful about quotes and append the reference to TextDaysPast: `WHERE Date()-" & Me.TextDaysPast & "...` Keep the query and just update the sql: `CurrentDB.Querydefs("MyXTab").SQL=sSQL` – Fionnuala Feb 20 '13 at 14:50

2 Answers2

1

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Ok, I'll get to work modifying the form's VBA. Is it better practice to have the SQL inside it's own function/call or to just have it where it is used (command button in this case)? Thanks, I'll keep you posted! – Phizon Feb 20 '13 at 14:58
  • That very much depends on what you are doing, you could even keep the sql in a query, save the sql string, update the query with the new sql and then set it back. – Fionnuala Feb 20 '13 at 15:09
  • Ok, after tooling around with SQL in VBA, I just modified my source query on-the-fly with '" & Me.TextDaysPast & "', works the same as a form control reference except now the crosstab doesn't need parameters (the Me.TextDaysPast must be passed as a static reference). Beautiful! Thank you so much! – Phizon Feb 20 '13 at 16:09
0

An alternative is to create a table from your crosstab and use that as the SourceObject

DoCmd.RunSQL ("SELECT * INTO CRTTable FROM CrosstabQ")
Forms!myForm!Subform.SourceObject = "table.CRTTable"

drop the CRTTable beforehand works as the number of columns vary in Crosstab queries.

Roger
  • 11
  • 7