How to pass in parameters in openquery command?

[SweetDot]

Hi,
I have a parameter specified in a query, and i need to pass that in when i use the docmd.openQuery command, how do I pass in the parameter value? the value changes each time i need to open that query.

 

[EastWind]

You can't. In order to pass parameter(s) to a query, you would need to do it via an ADOX catalog procedure and an ADODB command. Below is an example of how I passed parameters to a query and then executed it:

Public Function upqryPmntMatchQtr()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Comments.                                                                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim cat As New ADOX.Catalog, cmd As ADODB.Command
Dim rst As New ADODB.Recordset

Set cnn = CurrentProject.Connection
Set cat.ActiveConnection = cnn

Set cmd = cat.Procedures("[b]Your Query Name Here[/b]").Command
cmd.Parameters("[b]Parameter Name 1 Here[/b]")=VALUEofParam1
cmd.Parameters("[b]Parameter Name 2 Here[/b]")=VALUEofParam2

rst.Open cmd, cnn, adOpenStatic, adLockReadOnly
        
'Do whatever else you need to do to your RecordSet here.

rst.Close
Set rst = Nothing
Set cat = Nothing
Set cmd = Nothing

End Function

It looks a little complicated, but the process itself is pretty simple:
[ol]
[li]define an ADODB connection, Command and Parameter & ADOX Catalog[/li]

[li]open the connection[/li]

[li]Set the Command from the Catlog of Procedures to the name of your query[/li]
[li]define the cmd parameter(s) by name[/li]

[li]close and set everything to nothing to clean-up the memory and severe connections[/li]
[/ol]