title: Displaying a Crosstab Query with a Parameter as Subform Source Object
body: <p>Ok Access 2010 here.</p>
  
  <p>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:</p>
  
  <pre><code>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];
  </code></pre>
  
  <p>It's source query, as seen above, is called "TestQuery," and it looks like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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." </p>
  
  <p>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:</p>
  
  <pre><code>Me.SubformDisplay.SourceObject = "Query.TestCrosstab"
  Me.SubformDisplay.Requery
  </code></pre>
  
  <p>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. </p>
  
  <p>Any ideas on how to get around this? Would combining the source query and crosstab into one big monster fix this?</p>
  
  <p>Thank you for your input!</p>