Access VBA: Recordset should be NOT nothing

Question

I have a Front-End database setup for users to extract data regarding a list of information that they upload. The export function worked fine except they want the results to go to the open workbook add a sheet with the data without saving. The problem is that the created query has data when I run the query before or after the macro is not running. However as the macro is running the query returns nothing. The latest VBA I'm using is below. Please review and advise what I'm missing. Thank you,

MS Office - Access: 2010

Active Reference Library:

Visual Basic for applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft Excel 14.0 Object Library
Microsoft Office
14.0 Access database engine Object Library

Macro:

Private Sub ExpFile_Click()
Dim sql2export, s As String, blnExcel, blnWhere As Boolean, qdf As QueryDef, xlApp As Object, ws As Excel.Worksheet
Dim MyDatabase As DAO.Database, MyQueryDef As DAO.QueryDef, MyRecordset As DAO.Recordset
blnWhere = False

If Me. QueryASubform.Visible = True Then 'exceptions
    sql2export = "QueryA"
    blnWhere = True
ElseIf Me. QueryBSubform.Visible.Visible = True Then 'no Program Group for Build ID
    sql2export = " QueryB"
ElseIf Me. QueryCSubform.Visible = True Then 'Bill to and Type report.
    sql2export = " QueryC"
Else: Exit Sub
End If

If blnWhere = False Then
    s = "select * from " & sql2export & " Where (((" & sql2export & ". GPID)=[Forms]![frmFEFindQA]![GPID]));"
Else: s = "select * from " & sql2export
End If

On Error Resume Next
CurrentDb.QueryDefs.Delete "xlsExport"
Set qdf = CurrentDb.CreateQueryDef("xlsExport", s)

Set xlApp = GetObject(, "excel.application")
If (Err.Number = 0) Then
    Set xlApp = GetObject("Excel.Application")
    xlApp.Visible = True
    Set ws = xlApp.Sheets.Add
    Set MyDatabase = CurrentDb
   MyDatabase.QueryDefs.Delete ("xlsExport")
   Set MyQueryDef = MyDatabase.CreateQueryDef("xlsExport", s)
   Set MyRecordset = MyDatabase.OpenRecordset("xlsExport")    ‘<------ empty

    With xlApp
        .ws.Select
        .ActiveSheet.Range("a2").CopyFromRecordset MyRecordset
        For i = 1 To MyRecordset.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
    xlApp.Cells.EntireColumn.AutoFit
    End With
Else:
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "xlsExport", "C:\Users\" & Environ("USERNAME") & "\Documents\VehInfoExp", True
    xlApp.Workbooks.Open "C:\Users\" & Environ("USERNAME") & "\Documents\InfoExp.xls", True, False
End If
Err.Clear
On Error GoTo 0
Set xlApp = Nothing
End Sub

SakiSam SakiSam · Accepted Answer · 2016-03-14T15:13:24

Arg, I found the answer. After a week of trying I decided to post the question and then I figured it out an hour later.

The issue is in the "Where" clause of the SQL. I needed to capture the value of the form as a variable and put it into the equation. For some reason while the macro is running the referenced part of the form was valued as nothing. So nothing was returned.

Therefore, the following line of SQL:

s = "select * from " & sql2export & " Where (((" & sql2export & ".GPID)=[Forms]![frmFEFindQA]![GPID]));"

Became:

s = "select * from " & sql2export & " Where (((" & sql2export & ".GPID)=""" & strWhere & """));"

Thank you for letting me post.

Access VBA: Recordset should be NOT nothing

1 Answers