Supplying query parameter in VBA

Bob Pierce · April 12th, 2009, 05:30 AM

I have a form based upon "myQuery" which includes one parameter. I am attempting to supply that parameter in code within Form_Load by modifying the QueryDef prior to the calling Form.RecordSource = "myQuery". Debug shows that the parameter has been correctly set but I am still prompted to supply the parameter. What am I missing?

gbianchi · April 12th, 2009, 09:54 AM

Probably the query doesn't work. Did you try it outside code??

SerranoG · April 14th, 2009, 03:14 PM

Remove the parameter from the query itself. Then when the form loads, ask the user via an input box what they want. Then set the form's record source via VBA code. View the query in SQL view to get the proper syntax for the VBA code, but replace the portion where you had the parameter with the proper input box answer.

e.g.

Code:

strCustomerID = Trim(Nz(InputBox("Enter the Customer ID.", "ID?"), ""))
 
If Len(strCustomerID) = 0 Then
    'Input box is empty.  Select all records.
    Me.RecordSource = "SELECT * FROM tblMyTable;"
Else
    'Input box is not empty.  Select specific records.
    Me.RecordSource = "SELECT * FROM tblMyTable " & _
        "WHERE [strCustomerID] = '" & strCustomerID & "';"
End If

I have not tested this.

Bob Pierce · April 14th, 2009, 05:02 PM

I got the solution from another forum, but thanks for your time. You may be interested in the answer.

This form is part of a working data entry sequence. Currently "param1" is known when the form is opened so I build the SQL in code with a WHERE clause to limit the recordset to "param1". Having read about modifying QueryDefs I thought that in future I could avoid all the coded SQL and base the form on a stored parameter query, and just supply "param1" by modifying the QueryDef as per my Wrox book. My question was: if the QueryDef reports that the parameter has been set, why is the parameter being requested? My code looked like this:

Private Sub Form_Load()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryCourse")
Debug.Print "param1: " & qdf.Parameters("param1").Value -> "param1: "
qdf.Parameters("param1").Value = pLanguageID
Debug.Print "param1: " & qdf.Parameters("param1").Value -> "param1: 99"
qdf.Close
Set qdf = Nothing
Form.RecordSource = "qryCourse"
End sub

The error here was that the instance of "qryCourse" used as the RecordSource wasn't the same one that I had supplied parameters for. The solution is:

Private Sub Form_Load()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryCourse")
qdf.Parameters("param1").Value = pLanguageID
Set Me.Recordset = qdf.OpenRecordset
qdf.Close
Set qdf = Nothing
End sub