Having Query Results Populate in a Drop Down (1 Viewer)

[Jambi46n2]

Hello,

I am new here, and new to using VBA in Access.

I've been presented with an existing code below.
The objective is to have Access Pull Query Results in a Drop down.

The code errors out on the line in red.

Can someone point me into the right direction to make this work?

Any assistance is greatly appreciated.

Thank you so much.

Private Sub DataQC()

''' Use Access instead of SQL and Pull Results in Dropdown '''
    
Dim qdf As DAO.QueryDef
Dim x As Integer
Dim strQueryNamesWithResults As String
Dim rs As DAO.Recordset

'On Error GoTo err
    If DataErr = 3061 Then
    Response = acDataErrContinue
    
Set db = CurrentDb

    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 6) = "qryQC_" Then
            Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic)
            With rs
                If .RecordCount <> 0 Then
                    x = x + 1
                    strQueryNamesWithResults = strQueryNamesWithResults & qdf.Name & ","
                End If
            End With
        End If

    Next
    End If

    
    'Declare recordset and set to results of sproc
    Dim oRS As ADODB.Recordset
    Set oRS = New ADODB.Recordset
    [COLOR="Red"]Set oRS = cmd.Execute()[/COLOR]
    
    'add recordset to combobox
    With oRS
     Do While Not .EOF
        cboDataQueryList.AddItem oRS(0)
        .MoveNext
  Loop
End With
    
    'cleanup
    oRS.Close
    oConn.Close

    'Set cboQueryList to QC query choices
    cboQueryList.RowSource = [Utility Functions].GetQueryList
    
Me.txtQCEndDt.Visible = True
Me.cboDataQueryList.Visible = True
Me.lblDataExceptionQueries.Visible = True
MsgBox "Data Exception Queries now available."
    
End Sub

 

[Ranman256]

you dont need ANY vb to populate a combo.
just set the rowsource of the combo to the query.

 

[Frothingslosh]

Well, for starters, I would recommend using Option Explicit, as DataErr is not a defined variable, nor is Response. Those two look like they're chopped from a Form_Error event rather than a UDF. DB isn't defined, either.

As to your error, technically you're erroring because you never assigned an actual SQL statemet to oRS. You created a blank recordset and then told it to execute.

Ranman is right, though - a normal combo box has a RowSource property you simply fill in with either a SELECT statement or the name of a query. If you're trying to create a value list, you're going about it completely the wrong way.

Why don't you take a step back and tell us, in plain English, just what precisely you are trying to do. What should the combo box have in it, and what precisely are you trying to do with it?

 

[Jambi46n2]

Thank you for your prompt responses.

After spending a few hours with trial and error I was able to resolve it myself.

The objective was to pull all results from all queries named like "qryQC_"

Then place them into a combo box for selection.

Not that it may be useful to anyone,
but the final code that is functioning is as follows:

Dim qdf As DAO.QueryDef
Dim x As Integer
Dim strQueryNamesWithResults As String
Dim rs As DAO.Recordset

'Ignore 3061 Runtime Error
    If DataErr = 3061 Then
    Response = acDataErrContinue
    
Set db = CurrentDb

    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 6) = "qryQC_" Then
            Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic)
            With rs
                If .RecordCount <> 0 Then
                    x = x + 1
                    strQueryNamesWithResults = strQueryNamesWithResults & qdf.Name & ","
                End If
            End With
        End If
    Next
    End If
    
    'Set cboQueryList to QC query choices
    cboDataQueryList.RowSource = [Utility Functions].GetQueryList

Me.txtQCEndDt.Visible = True
Me.cboDataQueryList.Visible = True
Me.lblDataExceptionQueries.Visible = True
MsgBox "Data Exception Queries now available."

 

[Jambi46n2]

Now that I'm able to place the qry results in the combo box..

How can I make it so the only choices to choose from are queries that begin with "qryQC_" ?

I've tried this, but it doesn't work unfortunately..

If qdf.Name Like "qryQC_*" Then

I must be missing something.

Screen Shot:
prntscr.com/eysmgs

Dim qdf As DAO.QueryDef
Dim x As Integer
Dim strQueryNamesWithResults As String
Dim rs As DAO.Recordset

'Ignore 3061 Runtime Error
    If DataErr = 3061 Then
    Response = acDataErrContinue
    
Set db = CurrentDb

    For Each qdf In CurrentDb.QueryDefs
[COLOR="SeaGreen"]        If qdf.Name Like "qryQC_*" Then[/COLOR]
            Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic)
            With rs
                If .RecordCount <> 0 Then
                    x = x + 1
                    strQueryNamesWithResults = strQueryNamesWithResults & qdf.Name & ","
                End If
            End With
        End If
    Next
    End If
    
    'Set cboQueryList to QC query choices
    cboDataQueryList.RowSource = [Utility Functions].GetQueryList

Me.txtQCEndDt.Visible = True
Me.cboDataQueryList.Visible = True
Me.lblDataExceptionQueries.Visible = True
MsgBox "Data Exception Queries now available."
    
End Sub

 

[Frothingslosh]

You need to have 10 posts before you can attach a photo. Try putting it in a ZIP file and attaching that instead.

Also, 'didn't work' is unusably generic. What precisely is the error message you're receiving? That line shouldn't be erroring out - I even rigged up a quick test and it worked just fine.

Public Function QueryCheck()
 
Dim qdf As DAO.QueryDef
Dim DB As DAO.Database
 
    Set DB = CurrentDb
    
    For Each qdf In DB.QueryDefs
        If qdf.Name Like "qry_*" Then
            Debug.Print qdf.Name
        End If
    Next qdf
 
    Debug.Print "Search Complete"
    
    If Not qdf Is Nothing Then Set qdf = Nothing
    If Not DB Is Nothing Then Set DB = Nothing
 
End Function

Also, what is [Utility Functions]? A class or a standard module?

 

[Cronk]

Change the Row source type of the combo' in its data properties tab to value list and in your code replace the line

cboDataQueryList.RowSource = [Utility Functions].GetQueryList
with
cboDataQueryList.RowSource =strQueryNamesWithResults

You might like to remove the trailing comma from the string.

 

[Frothingslosh]

One small housekeeping detail.

Set db = CurrentDb

    For Each qdf In CurrentDb.QueryDefs

Seeing as you went to all the work of assigning a database object to db, I would recommend changing that second line to

For Each qdf in db.QueryDefs

 

[Jambi46n2]

Thank you both again for the pointers.

I have tried both suggestions, and still no change in the result.

Here's the screen shot in a zip..

Everything works without errors.
It's just providing me with all the qry results.

I'm only wanting to see those that match Like "qryQC_*" in the combo box.

 

[Jambi46n2]

Also, what is [Utility Functions]? A class or a standard module?

Please excuse my ignorance, [Utility Functions] is something I just googled to make the qry's populate in the combo box. Which does work great. Only problem it pulls all qry's. The issue I'm having is the "Like "qryQC_*" not having any impact on the results that populate within the combo box.

I hope the screen shot provided in my previous post helps.

Thank you again for your time.

 

[Jambi46n2]

Finally was able to identify the line of code I wrote to Ignore the 3061 Runtime Error was actually skipping over the Like "qryQC_*" line.

This was resolved by utilizing proper error handling
using "On Error Resume Next"

Again, thank you all so much.
This was an excellent learning experience in working with existing code
outside of my comfort zone.

Private Sub DataQC()

''' Use Access instead of SQL and Pull Results in Dropdown '''
''' Search for Sub DataQC_Original for Version Before This Change

Dim qdf As DAO.QueryDef
Dim x As Integer
Dim strQueryNamesWithResults As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

'On Error GoTo nn
On Error Resume Next

    Set db = CurrentDb
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name Like "qryQC_*" Then
                
            Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic)
      
            With rs
                If .RecordCount <> 0 Then
                    [cboDataQueryList].AddItem (qdf.Name)
                End If
            End With
            
        End If
nn:
    'Ignore 3061 Runtime Error
    If DataErr = 3061 Then Response = acDataErrContinue
    Next qdf

    Debug.Print "Search Complete"
    
    'GetQueryList
    'strQueryNamesWithResults
    '[Utility Functions].GetQueryList

Me.txtQCEndDt.Visible = True
Me.cboDataQueryList.Visible = True
Me.lblDataExceptionQueries.Visible = True
MsgBox "Data Exception Queries now available."
    
End Sub

 

[Frothingslosh]

I assume this is for a class? Because a SQL statement pulling what you're after would be both much easier and much faster.

While I'm typing up my actual response, do us both a favor and at the top of the module where this code is, add 'Option Explicit' under 'Option Database', and then compile the code. You're in for a rude shock.

My next post may take some time, but I'll explain in detail why you have a perfect storm of errors here that is making it ignore what you want.

 

[Frothingslosh]

It looks like your issue happened partially because your indentation is wrong and something got missed. Here is what you posted:

Dim qdf As DAO.QueryDef
Dim x As Integer
Dim strQueryNamesWithResults As String
Dim rs As DAO.Recordset
 
'Ignore 3061 Runtime Error
    If DataErr = 3061 Then
    Response = acDataErrContinue
    
Set db = CurrentDb
    For Each qdf In CurrentDb.QueryDefs
       If qdf.Name Like "qryQC_*" Then
            Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic)
            With rs
                If .RecordCount <> 0 Then
                    x = x + 1
                    strQueryNamesWithResults = strQueryNamesWithResults & qdf.Name & ","
                End If
            End With
        End If
    Next
    End If
    
    'Set cboQueryList to QC query choices
    cboDataQueryList.RowSource = [Utility Functions].GetQueryList
 
Me.txtQCEndDt.Visible = True
Me.cboDataQueryList.Visible = True
Me.lblDataExceptionQueries.Visible = True
MsgBox "Data Exception Queries now available."
    
End Sub

A properly indented version is this:

Dim qdf As DAO.QueryDef
Dim x As Integer
Dim strQueryNamesWithResults As String
Dim rs As DAO.Recordset
 
'Ignore 3061 Runtime Error
    If DataErr = 3061 Then      [COLOR=red]'(1)[/COLOR]
        Response = acDataErrContinue
        
        Set db = CurrentDb      [COLOR=red]'(2)[/COLOR]
    
        For Each qdf In CurrentDb.QueryDefs     [COLOR=red]'(3)[/COLOR]
            If qdf.Name Like "qryQC_*" Then
                Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic)
                With rs
                    If .RecordCount <> 0 Then
                        x = x + 1
                        strQueryNamesWithResults = strQueryNamesWithResults & qdf.Name & ","
                    End If
                End With
            End If
        Next
    End If
    
    'Set cboQueryList to QC query choices
    cboDataQueryList.RowSource = [Utility Functions].GetQueryList       [COLOR=red]'(4)[/COLOR]
 
Me.txtQCEndDt.Visible = True
Me.cboDataQueryList.Visible = True
Me.lblDataExceptionQueries.Visible = True
MsgBox "Data Exception Queries now available."

Here are my notes. The numbers (except for the last one) match up to the numbered comments I added to your code.

1. DataErr and Response are the parameters used in the Form_Error event. By including them here, they are implicitly declared, meaning they're created on the spot with values of null. That means that your app is reaching this line, creating DataErr with a value of NULL, and comparing it to the number 3061. Because of that, it's skipping everything inside the IF/THEN loop.
2. On this line you set db = CurrentDb, but then you never again use it. Also, this is another implicit declaration - note that at no point did you Dim the variable db.
3. Either change CurrentDb to your variable db or else get rid of the line 'Set db = CurrentDb'. One or the other.
4. This is obviously a function you downloaded which lists every query in your database. Two things:
   • First off, you don't need to include the module name when calling a function inside it unless it's a class module, not a standard module. cboDataQueryList.RowSource = GetQueryList() would have worked fine.
   • Second, even if your IF/THEN loop weren't adjudged FALSE every time this runs, this line guarantees you always get all queries.
5. Replace [Utility Functions].getQueryList with Left(strQueryNamesWithResults,Len(strQueryNamesWithResults) - 1). (The Left function is to strip off the trailing comma.)

A couple other notes:

While I gave you the code to strip the trailing comma, it will error out if there are no entries at all in strQueryNamesWithResults. I would recommend actually checking for that before reaching this line, and only doing the Left function if the variable isn't empty.

While not precisely required here, it is generally good form to get into the habit of closing everything you open and setting to nothing everything you assign with a SET command. In this particular piece of code, I doubt anything would be left hanging open, but you can eventually run into weird issues in the future when you work with code and leave stuff open expecting the engine to clean up behind you. It does 99% of the time, but that last 1% can give some REALLY strange results.

Edited to add: What is the purpose of the X counter? You're incrementing it, but never use it. If you're not going to do anything with x, then you should get rid of every line using it.

 

[Minty]

To add to Frothy's response, you are opening a recordset with a load of unnecessary options - This line

Set rs = db.OpenRecordset(qdf.Name, dbOpenSnapshot, dbSeeChanges, dbPessimistic

A snapshot recordset is not editable, so opening it with dbPessisitic (locking), and dbSeechanges (recordset updates) are both redundant.

 

[Frothingslosh]

I figured I'd thrown enough of a wall of text at him.

 

[pbaldy]

Post 11 was moderated, now approved.

 

[Frothingslosh]

Post 11 was moderated, now approved.

Well, that certainly changes a few things.

And I wish I'd seen it before I wrote that wall of text!

 

[NauticalGent]

Allow me to ask a question that I could find with a simple google search: dbPessimistic, is that the same thing as choosing Record Lockimg - Editied Recored from the Options menu?

 

[Frothingslosh]

Allow me to ask a question that I could find with a simple google search: dbPessimistic, is that the same thing as choosing Record Lockimg - Editied Recored from the Options menu?

Yes

(10 char min)

 

[NauticalGent]

Thanks, this bit of info will come in handy...