I have a query that's rather large, joining over a dozen tables, and I want to pull back records based on an id field (e.g.: between nStartID and nEndID
).
I created two parameters and tested them as criteria and they work fine.
The issue is, I need to run an insert query from this main query, and need the parameters where they are, in the main query. So, I need to pass parameters to it programmatically.
Anyone have a clue as to how this can be done?
Thanks.
This question is related to
vba
ms-access
parameters
ms-access-2010
You can also use TempVars - note '!' syntax is essential
Many thanks for the information about using the QueryDefs collection! I have been wondering about this for a while.
I did it a different way, without using VBA, by using a table containing the query parameters.
E.g:
SELECT a_table.a_field
FROM QueryParameters, a_table
WHERE a_table.a_field BETWEEN QueryParameters.a_field_min
AND QueryParameters.a_field_max
Where QueryParameters
is a table with two fields, a_field_min
and a_field_max
It can even be used with GROUP BY
, if you include the query parameter fields in the GROUP BY
clause, and the FIRST
operator on the parameter fields in the HAVING
clause.
Try using the QueryDefs. Create the query with parameters. Then use something like this:
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Your Query Name")
qdf.Parameters("Parameter 1").Value = "Parameter Value"
qdf.Parameters("Parameter 2").Value = "Parameter Value"
qdf.Execute
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
Plenty of responses already, but you can use this:
Sub runQry(qDefName)
Dim db As DAO.Database, qd As QueryDef, par As Parameter
Set db = CurrentDb
Set qd = db.QueryDefs(qDefName)
On Error Resume Next
For Each par In qd.Parameters
Err.Clear
par.Value = Eval(par.Name) 'try evaluating param
If Err.Number <> 0 Then 'failed ?
par.Value = InputBox(par.Name) 'ask for value
End If
Next par
On Error GoTo 0
qd.Execute dbFailOnError
End Sub
Sub runQry_test()
runQry "test" 'qryDef name
End Sub
Source: Stackoverflow.com