Pass parameter to query from a table

[AndySuk]

Folks I have a table for invoices. When populating the table I had hoped to be able to select a list of Purchase Orders limited by the ClientId. This works fine when I set the criteria in the Purchase Order query to equal the ClientId in the table which then limits the Purchase Order lookup.

However I use the Purchase Order Query in several other places so I was wondering if one can pass the parameter from the table to the query rather than have to write multiple instances of the query each having a criteria referring to a different table or query.

Thanks

A

 

[jal]

Here's a couple examples of using parameters:

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim RsMissingTimes As DAO.Recordset
Set db = CurrentDb
Set qDef = db.QueryDefs("QryMissingTimes")
qDef.Parameters("Hourly_Rate") = Forms!FrmFunctions!txtHourlyRate
qDef.Parameters("Works_Overhead") = Forms!FrmFunctions!txtWorksOverhead
Set RsMissingTimes = qDef.OpenRecordset


Example 2 (well, something like this)

Dim cmd as New ADODB.Command
cmd.ActiveConnection = strConnection (or CurrentProject.Connection)
cmd.CommandText = "queryNameHere"
cmd.Parameters.Refresh
cmd.Parameters(0).Value = "10255"
cmd.Execute

 

[AndySuk]

Thanks Jal. I was hoping to pass the parameter direct from the table in order to get the functionality prior to using forms and VBA but I suspect that your way is the way to go.

Normally I wouldn't want to input direct into a table but time is pressing hence the reason for the question.

Thanks again

A

 

[LPurvis]

Once you have a properly constructed parameter query then you can use it in whatever way is appropriate, being called from code is one (very versatile) way. (And how you'll see many executions performed).

What would the "pass the parameter direct from the table" method actually look like?
(i.e. what do you envisage happening?)

If the parameter by which you intend to filter is stored in another table than you can create a function (or use a built in one, DLookup is very common) to retrieve that value to your query. What is more effort is if you have more than one value to pass as a range of parameters.
Hence (and especially in the latter case) it's often better to store your parameters in another table - and join to that table.
The database engine will optimise this request better than a convoluted expression of multiple values (queries love to join, especially in Jet ;-) and you're then just specifying your criteria by entering values into another table.