Run various queries based on a combo box selection
I have a form where the user will select a value from a dropdown. Based on this selection, I would like to fulfill a number of queries. For example, if the user selects "A" then queries 1, 2 and 4. If the user selects "B", then queries 4, 2, 3 and 5 will run in that order. The number of requests will vary and there will be an order in which they are executed. I have a query subscription table that contains all the queries to be executed by every possible value in the dropdown and the order in which they will be executed.
tbl_subcription:
RowID: autonumber
SubscriptionID: User selected query list identifier
QrySequence: Query execution order, unique within each SubscriptionID
QryName: Name of query to be run
Example:
RowID SubscriptionID QrySequence QryName
1 A 1 qry1
2 A 2 qry2
3 A 3 qry4
4 B 1 qry4
5 B 2 qry2
6 B 3 qry3
7 B 4 qry5
I need something like:
For each query in QryList (filtered with SubscriptionID, sorted by QrySequence)
execute query
Next query
...Display results...
I am not using dynamic queries and no queries require additional parameters. All help is welcomed. Tom
source to share
Create a query that retrieves QryName values from rows whose SubscriptionID matches the dropdown ... query something like this:
SELECT QryName
FROM tbl_subcription
WHERE SubscriptionID = [dropdown]
ORDER BY QrySequence;
Then you can open DAO.Recordset
based on that query, traverse through the rows of the recordset, and execute each QryName:
Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim rs As DAO.Recordset Dim strSelect As String strSelect = "SELECT QryName FROM tbl_subcription " & _ "WHERE SubscriptionID = [dropdown] ORDER BY QrySequence;" Set db = CurrentDb Set qdf = db.CreateQueryDef(vbNullString, strSelect) qdf.Parameters("dropdown").Value = Me.YourDropdownName.Value Set rs = qdf.OpenRecordset(dbOpenSnapshot) With rs Do While Not .EOF db.Execute !QryName, dbFailOnError .MoveNext Loop .Close End With
Replace YourDropdownName with the name of your dropdown (combo box or list box).
This code expects YourDropdownName to be contained in a form that also contains this code. If your code and YourDropdownName are not in the same form, you can refer to the dropdown menu via its parent form in the collection Forms
:
Forms!YourFormName!YourDropdownName
source to share
In case you say The number of queries will vary and so will the order in which they are executed
. It looks like a function taking a ParamArray will handle what you are looking for.
Something like that:
Public Function RunQueries(ParamArray QueriesToRun())
Dim i As Long
Dim lngUBound As Long
'If the ParamArray is not empty
If UBound(QueriesToRun) >= 0 Then
lngUBound = UBound(QueriesToRun)
'For each value in ParamArray
For i = 0 To lngUBound
`Run Query: QueriesToRun(i)
Next
End If
End Function
This is untested code, but I think everything is correct and should do what you are looking for. I've only ever used it ParamArray
once, so the syntax might be off a bit.
source to share