Passing parameters from forms to queries in VBA

[shanedavid1981]

Hey,

I'm a bit stuck as to how I would pass a parameter from a form to a query in VBA... look at this:

Private Sub Command5_Click()
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strquerytext As String
Combo2.SetFocus
strquerytext = Combo2.Text
strQueryName = "OpsAttSummarySearch"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
Set xlSheet = xlWorkbook.Sheets(1)
With xlSheet
    .Cells.CopyFromRecordset objRST
    .Name = strquerytext
End With
Set objRST = Nothing
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

    
End Sub

Now when I run this Excel opens, but no data is delievered.. an error message in the background tells me a parameter is required... the query requires one parameter from the form this code is triggered from... what do I need to do to get this parameter passed correctly?

Thanks )

 

[PHV]

First, acQuery is a global constant.
You have to play with the QueryDef.Parameters collection and the Eval function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shanedavid1981]

Okay, how would I go about that?

I've done direct SQL thru VBA,a nd outputting simple queries to e-mail with VBA, I didn't think there would be much difficulty in passing a parameter from a control that is already open on the form...

 

[PHV]

Another way is to open the query prior to play with the recordset:
DoCmd.OpenQuery strQueryName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886