get the name of a query from an access table, then run the query

Question

I have an access db that pulls volumes from a table of exceptions. Each volume has an ID. I've created queries to pull details, for all possible volumes, and saved each one with the same name as each volume ID. Each time the volume exceptions are pulled into this db, the volume IDs can change. So, there is a query that runs that updates the volume table with the new IDs.

Unless you know a way to do this with a query, I need to write Access VBA code that will loop through the volume table, identify the name of each query and then run those queries until it reaches the end of the table. For example, the code needs to look at the first record in the volume table, say it is 1040. This is the name of the query that needs to run. The code then needs to find the query named 1040 and run it. It is a make table query.

The table name is FacilityVolume and it has one field named Volume. The value in the field is shorttext format even though it is numeric.

I've tried a couple of different things. Here is my latest try.

    Dim db as Database
    Dim vol as Recordset
    Dim code as QueryDef

    Set db = CurrentDb()
    Set vol = db.OpenRecordset("FacilityVolume")
    Set Volume = vol.Fields("Volume")

    Vol.MoveFirst
    Do Until vol.EOF = True
        If QueryDef.Name = Volume Then
           DoCmd.OpenQuery
        Else MsgBox("The query does not exist")
        vol.MoveNext
    Loop
    End Sub

I've searched the internet for a few days and can't find any reference to this particular code. I'm sure other users would like to know how to do this. I'm a novice and still learning VBA so any help you can provide is greatly appreciated.

Answer 1

Your code will loop through, even if you found your query and you do not pass the Query-Name to the OpenQuery command... This won't work...

The collection CurrentDb.QueryDefs knows all existing queries, but there is no "Exists" or "Contains" method.

So: The approach would be a loop (as you tried it) or an Error handling.

It's quite a time ago since I've coded with VBA, but I think you could try:

On Error Resume Next
DoCmd.OpenQuery "YourQueryName"
If Err Then
    MsgBox("The query does not exist!")
    Err.Clear
End If
On Error Goto 0

Answer 2

I recommend using full DAO in VBA to accomplish your goal. DoCmd.OpenQuery is really a VBA function that mimics the Macro RunQuery action. You don't get much control or true error handling capability.

Here is a complete code function that

1. Gives you an example of how to select all or some records from your table that lists the queries, including the ability to only select "Active" records, and even sort them in a particular execution sequence
2. Handles the instances where the query name in your table does not exist
3. Allows you to display a message about any errors that occur
4. Allows you to return an exit code to the calling procedure so that you can possibly act on the results of running these queries (such as choosing not to do the next step in your code if this function encounters an error of any kind (returns a non-zero value)

Here is the code. You will need to modify the SQL statement for your correct table name and field names, but this should be a good example to get you on your way.

Public Function lsProcessQuerySet() As Long
On Error GoTo Err_PROC
    Dim ErrMsg As String
    Dim db As DAO.Database
    Dim rstEdits As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim mssql As String
    Dim ReturnCode As Long

    Set db = CurrentDb()
    '============================
    'Select the list of Queries you want to process
    '============================
    mssql = "SELECT tblQueryList.ID, tblQueryList.QueryName, "
    mssql = mssql & "tblQueryList.QueryShortDesc "
    mssql = mssql & "FROM tblQueryList "
    mssql = mssql & "WHERE tblQueryList.QueryActive = True "
    mssql = mssql & "ORDER BY tblQueryList.SortOrder;"
    Set rstEdits = db.OpenRecordset(mssql, dbOpenDynaset)
    DoCmd.Hourglass True
    '============================
    'Execute each query, allowing processing to continue
    'if the query does not exist (an error occurs)
    '============================
    Do While Not rstEdits.EOF
        Set qdf = db.QueryDefs(rstEdits("QueryName"))
        qdf.Execute dbSeeChanges
ResumeNextEdit:
        rstEdits.MoveNext
    Loop
    rstEdits.Close

Exit_PROC:
    lsProcessQuerySet = ReturnCode
    Set qdf = Nothing
    Set rstEdits = Nothing
    db.Close
    Set db = Nothing
    DoCmd.Hourglass False
    Exit Function

Err_PROC:
    Select Case Err.Number
        Case 3265   'Item Not Found in this Collection
            ReturnCode = Err.Number
            ErrMsg = "Query Not Found:" & vbCrLf & vbCrLf
            ErrMsg = ErrMsg & rstEdits("QueryName")
            DoCmd.Hourglass False
            MsgBox ErrMsg, vbOKOnly + vbCritical, "Function lsProcessQuerySet"
            Resume ResumeNextEdit
        Case Else
            ReturnCode = Err.Number
            ErrMsg = "Error: " & Err.Number & vbCrLf
            ErrMsg = ErrMsg & Err.Description
            DoCmd.Hourglass False
            MsgBox ErrMsg, vbOKOnly + vbCritical, "Function lsProcessQuerySet"
            Resume Exit_PROC
    End Select

End Function

Answer 3

The answer of @Shnugo is already good. Just to give you a complete VBA function, this should be working for you.

Public Sub MySub()
On Error GoTo err_mySub 
Dim db as Database
Dim vol as Recordset

Set db = CurrentDb()
Set vol = db.OpenRecordset("FacilityVolume", dbOpenDynaset) ' I don't know if you want to run all queries of the table "FacilityVolume". 
'So maybe you could also use something like "SELECT Volume FROM FacilityVolume WHERE Volume LIKE ""*10*"""

Vol.MoveFirst
Do Until vol.EOF = True
    DoCmd.OpenQuery vol!Volume
    vol.MoveNext
Loop

exit_MySub:
Exit Sub

err_MySub:
If Err.Number = 7874 Then
    MsgBox "The Query """ & Vol!Volume & """ wasn't found."
    Resume Next
Else
    MsgBox Err.Description
    Resume exit_MySub
End If
End Sub