[AccessD] Cross Tab Query Help Requested

My thanks to Bruce Bruen; you pointed me in the right direction, and then 
I found the info in Rick Dobson's "Programming Microsoft Access Version 
2002". 

I'm including below my VBA code in the hope that it can help someone else. 
For those of you more knowledgeable than I (plenty of you on this list), 
feel free to pick it apart. Remember that it does work, though!

For any who haven't been following: I needed a report to be based on a 
parameter query, which was in turn based on a crosstab query that also 
used the same parameter. I wanted to use ADO, and couldn't figure out how 
to declare a parameter. Even though it was in the SQL, ADO wanted its own 
declaration.

I hope the syntax below will help someone else.

Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems

Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String
Dim intP As Integer

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "PARAMETERS [Forms]![frmWeeklyData]![fraMonths] Long; " 
& _
        [Very long SQL statement here]
    .CommandType = adCmdText
End With

Set prm = cmd.CreateParameter("[Forms]![frmWeeklyData]![fraMonths]", 
adInteger, adParamInput)
cmd.Parameters.Append prm
intP = [Forms]![frmWeeklyData]![fraMonths]
prm.Value = intP

Set rst = cmd.Execute
 

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
    intColCount = intControlCount
End If

For i = 1 To intColCount
    strName = rst.Fields(i - 1).Name
    Me.Controls("Head" & i).Caption = strName
    Me.Controls("Col" & i).ControlSource = strName
 
Next i

rst.Close

End Sub




This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.