[AccessD] Cross Tab Query Help Requested

Hi Thomas

Here's an article on using ADO and parameters:

http://support.microsoft.com/kb/225897/en-us 

Also, look up in the archives subject "ADO code stopped working" from early February this year.

If it works now with DAO, I would leave it except, of course, if this is a learning experience.

/gustav

>>> ewaldt at gdls.com 14-05-2007 13:10 >>>
I am running into a problem with a cross tab query. 

I have a report based on a parameter query, which is in turn based on a 
cross tab query (which is based on the same parameter), and ADO doesn't 
seem happy. On MSFT's site, they use DAO in their (very complex) example, 
and they show how to specify a parameter (qdf = a QueryDef, and 
qdf.Parameters(xxx) = yyy). I really prefer to use ADO (trying to learn 
it), but I don't see how to specify a parameter's value in ADO. I'm 
assuming that's the problem , because Access keeps saying that I'm not 
specifiying required info. Also, when I went through and replaced all 
instances of parameters in the queries (query based on queries based on 
queries) with solid numbers, it worked. In the actual queries, the 
parameter is: 

[Forms]![frmWeeklyData]![fraMonths]

This simply refers to a frame containing option buttons so that I can 
specify the month I'm interested in. The month's number is then used by 
the queries.

Running the queries without the report works just fine. However, since a 
cross tab query is involved, and there can be varying numbers of columns, 
I have to use dynamic columns in the report, and that's where complexity 
rears its ugly head. The parameter query (that calls the cross tab query) 
is necessary because I have information in addition to the cross tab query 
itself which is needed in the report.

Here's the code portion that Access highlights:

rst.Open _
    Source:="qfrmWeeklyData", _
    ActiveConnection:=CurrentProject.Connection, _
    Options:=adCmdTable

I'd greatly appreciate any help with this.


Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems