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.
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.
Code:
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
Last edited: