CODEWITHSUNDEEP
ms-accessdrop-down-menuvba
Thomas Kerber
Thomas Kerber

Reputation: 99

Run various queries based on a combo box selection

I have a form where a user will select a value from a dropdown. Based on this selection, I would like a series of queries to execute. For example, if a user selects 'A', then queries 1, 2 and 4 would execute. If user selects 'B', then queries 4, 2, 3 and 5 would execute in that order. The number of queries will vary and so will the order in which they are executed. I have a 'query subscription' table that contains all of the queries to be executed by each possible value in the dropdown and their execution order.

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

Sample:

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 want something like:

For each query in QryList (filtered with SubscriptionID, sorted by QrySequence)
    execute query
Next query
...Display results...

I'm not using dynamic queries, nor do any queries require additional parameters. Any and all help is greatly appreciated. Tom

Upvotes: 3

Views: 1980

Answers (2)

Newd
Newd

Reputation: 2185

In the case where you say The number of queries will vary and so will the order in which they are executed. It sounds like a function accepting a ParamArray would handle what you are looking for.

Something like this: 

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 have only ever used a ParamArray once though so the syntax might be slightly off.

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Create a query which retrieves the QryName values from rows whose SubscriptionID matches the dropdown selection ... a query something like this:

SELECT QryName
FROM tbl_subcription
WHERE SubscriptionID = [dropdown]
ORDER BY QrySequence;

Then you can open a DAO.Recordset based on that query, move through the recordset rows, 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 control (combo or list box).

That code expects YourDropdownName is contained on the form which also contains that code. If the code and YourDropdownName are not both contained in the same form, you can reference the dropdown via its parent form's name in the Forms collection:

Forms!YourFormName!YourDropdownName

Upvotes: 2

Related Questions