Opening and Displaying the Results of a Paramaterized Crosstab Que

[Guest]

I have a crosstab query that gets it's data from a parameterized query. The
data source for the parameters being inputs from a form.

Can anyone provide me with best practices on opening the crosstab query
based on a button being clicked so that the results display on screen the
same way they would for a DoCmd.OpenQuery?

TIA,

John

 

[Duane Hookom]

Check out the dynamic subform example at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
solution sets the Source Document of a subform control to the crosstab query
(or any query).

 

[Guest]

Duane,

Thanks for the reply. The example you refer to is a nice implementation,
but what I need is that the data coming up after the crosstab query are not
only visible, but can be exported to Excel (tools | microsoft office |
analyze it with Excel).

Am I correct to assume that there is no way to open a parameterized crosstab
query via docmdpenquery?

Thanks again,

John

 

[Duane Hookom]

John,
You sound fairly sharp about Access. You should be able to write some DAO
code that modify the SQL property of your crosstab query so the dynamic
criteria would be updated within the SQL property of the query. For example:

CurrentDb.QueryDefs("qxtbMyCrosstab").SQL = "TRANSFORM..."

 

[Guest]

Duane,

Thanks again for the reply. We must be great minds, because that's exactly
what I ended up doing. I had hoped that there was a more automated solution.

Best regards,

John

 

[Duane Hookom]

I just fall back to old, tried and true solutions that work.