Cannot pass parameter to query from VBA

[CraigInOz]

Using the Immediate Window I can see that the variables are set in VBA, but nothing is being passed to the Query. Every time the query is opened, a parameter pop-up window is displayed. Here is my code...

    Dim dbs As Database
    Dim qdf As QueryDef
    Dim code As Integer
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs![qRptActivityWorksheet_All]
      
    code = 22
    qdf.Parameters![ActivityCode] = code
    DoCmd.OpenQuery ("qRptActivityWorksheet_All")


' tested with these variants on syntax
'    Dim dbs As DAO.Database
'    Dim qdf As DAO.QueryDef
'    Set qdf = dbs.QueryDefs("qRptActivityWorksheet_All")
'    qdf.Parameters("ActivityCode").Value = 44
'    qdf.Parameters![ActivityCode].Value = 44
'    qdf.Parameters("ActivityCode").Value = code
'    qdf!ActivityCode = code

Here is the SQL for the query:
PARAMETERS ActivityCode Long;SELECT qfrmCon_DetailMemNewest_S2.MemNameForCard, etc.


I would be very gratefully for some advice.

[Welshgasman]

Does this help?
https://www.access-programmers.co.uk...6/post-1535632

[Gicu]

I would suggest to edit the SQL property of the QueryDef object instead:

Dim dbs As Database
Dim qdf As QueryDef
Dim code As Integer


Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs![qRptActivityWorksheet_All]
qdf.SQL="SELECT .... FROM... WHERE.. [ActivityCode] = " & code & " AND .....;"
.....

https://learn.microsoft.com/en-us/of...l-property-dao
Cheers,

[jojowhite]

you can try this:

    Dim code As Integer
      
    code = 22
    DoCmd.SetParameter "ActivityCode", code
    DoCmd.OpenQuery ("qRptActivityWorksheet_All")

[CraigInOz]

Thank you everyone, I shall give these a try. One more wrinkle, this query is used by a report, it is the data source for the report. So running the SQL inside VBA is not going to work. The DoCmd.SetParameter I hope will update the query and then the report will call it. Thanks again.

[jojowhite]

you can use the query in your report:

    Dim code As Integer
      
    code = 22
    DoCmd.SetParameter "ActivityCode", code
    'DoCmd.OpenQuery ("qRptActivityWorksheet_All")
    DoCmd.OpenReport "RptActivityWorksheet_All", acViewPreview

[June7]

If query is RecordSource of report, why would you even need to open query? Purpose of parameter is to filter query? Why use parameterized query? Why not apply filter to report with WHERE CONDITION argument of OpenReport?

[CraigInOz]

The DoCmd.SetParameter worked perfectly. I am going to hit the VBA/Access websites. I have dozens of queries in this database. How does the DoCmd know which query had THAT parameter? This code seemed intuitive.

    Dim qdf As QueryDef
    Set qdf = dbs.QueryDefs![qRptActivityWorksheet_All]
    qdf.Parameters![ActivityCode] = code

Point to the query, and then to the parameter in the query. The DoCmd just points to the parameter. What if I had used the same parameter name in another query? I gotta research how Access handles this. Again, Thank you. It is working as I need it to.

[jojowhite]

here is an explanation from ms:
DoCmd.SetParameter method (Access) | Microsoft Learn

if your Form/Report is using Query that has parameter,
you SetParameters first for all the query parameters then
open the Form/Report.

once the Form/Report opens, the Parameter collection is cleared.
so if you are going to open another form/report that uses query
that has parameters, you again set up the parameters using
DoCmd.SetParameters before opening the Form/Report.

[madpiet]

June's answer is on the money. That whole passing a parameter to a query was a huge red herring, because when you open a report, you can pass parameter values to the underlying query that the report is based on. So remove the parameters from the query and maybe the entire WHERE clause. Then pass the filter when you open the report. It's a whole lot more flexible that way. If there are filters you always apply, you can leave them in the query the report is based on, but not a great idea, because you want the report to be super flexible that you can apply any filter to when you open the report.

[Micron]

Often, what works to eliminate code failing over parameters is to use a variable as a go-between between the parameter in the vba generated sql and form controls. Code without defined parameters may not be able to resolve Me.txtCustomerID but it should resolve
Dim lngCustID As Long

lngCustID = Me.txtCustID

" ... WHERE tblCustomer.CustID = " & lngCustID & " AND ... "

[CraigInOz]

I took the advice of removing the parameter from the query and building the necessary "where" statement for the OpenReport. Works like a charm. Thank you everyone.