I used to use Access 2003 and I wrote my own function to use the Command(ADODB) object for executing an existing query with parameter. You have to put the values for parameter in the sequence they appear in the saved query, no need to mention the names of the parameters. You may put the names of textbox containing the values for parameter but wrap that withe the function CStr(). For example CStr([txtProductType]). Here is my function:
Public Function ExecuteQuery( _
QueryName As String, _
cn As ADODB.Connection, _
ParamArray params() As Variant) As ADODB.Recordset
Dim cm As New ADODB.Command
cm.ActiveConnection = IIf(IsNull(cn), CurrentProject.Connection, cn)
cm.CommandText = QueryName
cm.CommandType = adCmdStoredProc
Set ExecuteQuery = cm.Execute(Parameters:=params)
Set cm = Nothing
End Function
Regards,
munim
Originally Posted by
mwhcrew
I have a form, ClientLookupFrm which has combo box ClientLookupCmb on it. I would like to select a value on the client lookup combo box and have it run the ClientItemQry with parameters. The parameter is that the bound column on the combo box goes to one of the values on the query, ClientAutoID. I have updated the afterupdate value to read:
Private Sub ClientLookupCmb_AfterUpdate()
DoCmd.OpenQuery ("ClientItemQry", , ,)
WHERE "ClientAutoID = " & Me.ClientLookupCmb
End Sub
But this is not working. I clearly don't know [COLOR=blue ! important][COLOR=blue ! important]VBA[/COLOR][/COLOR] and am just fumbling through here, so please help! I have put the criteria
[Forms]![ClientLookupFrm]![ClientLookupCmb]
on the query (which runs well if I put the actual value in as a criteria instead of the code).