Send parameters to queries

[Merkava]

Hello All,



On a form I would like to take a user's choice from a combo box and send it to a stored procedure.

How can I do this?

I thank you in advance for any assistance.

Cheers,
Aaron

[dcrake]

Are we actually talking about stored procedures or queries? what is you back end data media?

David

[Merkava]

If my terminology is correct, I am using a stored procedure. I created a query, using the design view, and saved it. Attached is a screen shot of what I am refering to.



Cheers,
Aaron

[ChrisO]

Generic reply (don’t expect it to work first-up on a copy/paste): -

Option Explicit
Option Compare Text


Private Sub cboAgentSelection_AfterUpdate()
    Dim strSQL As String
    
    strSQL = " SELECT *" & _
             " FROM tblAgents" & _
             " WHERE AgentPK = " & Me.cboAgentSelection.Column(0)
             
    CurrentDb.QueryDefs("qryGetAllAgentInfoByAgentID").SQL = strSQL

End Sub

Hope that helps.

Regards,
Chris.

[Merkava]

Hi Chris,

Thanks for your reply, but I am trying to avoid having the SQL in the code, this is why I have created queries in the design view. Some of the SQL statements are utterly monsterous.

Cheers,
Aaron

[ChrisO]

Your original question was about sending to the Query and that has been done.

The idea that 'utterly monstrous queries' really doesn’t have a lot to do with that question.

The question was about the direction of information travel, namely, that the information travels to the Query and no request for that information was made or required by the Query.

If it's a read back from the Query, meaning the Query asks for the parameters it requires before the Query is populated, then that is a different story which I prefer not to do.

Using the Query builder does not preclude using the SQL view of the Query definition in VBA, it simply requires string manipulation.
In fact, getting the SQL of the Query definition is a good place to start when transferring the Query to VBA.

There are many reasons for writing Query definitions in VBA, and some reasons for not doing so, that’s up to the designer.

However, I doubt if the size of the SQL string is one of the reasons not to.

[Merkava]

Hi Chris,

I said "monsterous SQL statements". I would prefer to use a ready made query, to keep the code as uncluttered as possible.

However if it is preferrable to add extremely long SQL statements in the code, as opposed to using "ready made" queries, I am always open to learning something new.

So my original question stands, how can I pass parameters to a stored procedure (If I am using the correct terminology)

Cheers,
Aaron

[ChrisO]

This is the only way I could late bind it and I would prefer to write the entire SQL string to the Query because it would be persistent: -

Option Explicit
Option Compare Text


Private Sub cboAgentSelection_AfterUpdate()
    Dim qryTemp As Object
    Dim rstTemp As Object
             
    ' Ordinal position of Parameter.
    Const AgentID As Long = 0
             
    Set qryTemp = CurrentDb.QueryDefs("qryGetAllAgentInfoByAgentID")
    
    qryTemp.Parameters(AgentID) = Me.cboAgentSelection.Column(0)
    
    Set rstTemp = qryTemp.OpenRecordset
    
    MsgBox rstTemp!SomeText
    
    rstTemp.Close
    Set rstTemp = Nothing
    
    qryTemp.Close
    Set qryTemp = Nothing
    
End Sub

[Merkava]

Hello Chris,

Thank you for your assistance. I will give this a try.

Cheers,
Aaron