Export a Query using a Do Loop Result (VBA Access 2013)

[bebesmom]

I'm pretty new to VBA and I'm having a devil of a time trying to get this script to work...and I'll admit that I appropriated this from here!

What I'm trying to do is use the ClientNumber in tblFileName to filter the results of qryData and have the resulting data export to Excel. I keep getting a Loop without Do error and I can't figure out why.

If someone can also help pull the FileName field from tblFileName and use that as the exported file name (instead of the "test" I have shown below, that would be fantastic! THANK YOU!

MY (ok, your) CODE

Function ExportRpt()
Dim rs As Recordset
Dim qDf As QueryDef
Dim strSQL As String
Dim myLocation As String
Dim myFile As Long
Set rs = CurrentDb.OpenRecordset("select ClientNumber from tblFileNames;")
If rs.EOF Then Exit Function
rs.MoveFirst
On Error Resume Next
DoCmd.DeleteObject acQuery, "MyQuery"   '<--- This deletes the old temp query getting ready to make a new one
myLocation = rs!Ballot_Style
myFile = "P:\LPSOutputs\FinishedReports\test.xlsx"
strSQL = "SELECT * FROM QryData WHERE ClientNumber='" & myMyLocation & "';"
Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
DoCmd.TransferSpreadsheet acExport, , "MyQuery", myFile, False
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

[twinnyfo]

bebesmom,

Welcome to Bytes!

Looks like you need

Do While Not rs.EOF

Between lines 10 and 11.

Hope this helps!

[twinnyfo]

Also, I wanted to recommend a few minor changes as good practices:

Option Compare Database
Option Explicit
 
Private Function ExportRpt()
On Error GoTo EH
    Dim RS          As Recordset
    Dim qDf         As QueryDef
    Dim strSQL      As String
    Dim myLocation  As String
    Dim myFile      As Long
 
    strSQL = "SELECT ClientNumber FROM tblFileNames;"
    Set RS = CurrentDb.OpenRecordset(strSQL)
    With RS
        If Not .RecordCount = 0 Then
            .MoveFirst
            Do While Not .EOF
                DoCmd.DeleteObject acQuery, "MyQuery"
                myLocation = !Ballot_Style
                myFile = "P:\LPSOutputs\FinishedReports\test.xlsx"
                strSQL = "SELECT * FROM QryData " & _
                    "WHERE ClientNumber='" & myMyLocation & "';"
                Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
                DoCmd.TransferSpreadsheet acExport, , _
                    "MyQuery", myFile, False
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set RS = Nothing
 
    Exit Function
EH:
    MsgBox "There was an error exporting the client data!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function

I included the Option Compare and Option Explicit statements (You may already have these in the module, but it is good to be sure that you always use them.

I've also listed this function as Private, which simply mean that it can only be used on the particular Form in which it is found. This can be set to Public, if you want to use it throughout your project.

Notice how each set of nested statements is indented accordingly. This helps you in the future when you have to troubleshoot (and helps others, too). You may have found the missing Do While had you done so.

I've included a more robust Error handling procedure--which is recommended for all procedures.

In lines 12-13, I've assigned the SQL string to the variable and then set the recordset using the variable. This is not required, but as your SQL strings become more complex, it allows you to trouble shoot the value of the variable if the recordset does not execute properly.

Also, notice lines 15 and 17. .RecordCount and .EOF are not quite the same thing. Whenever checking to see if there are any contents in a Recordset, I always recommend using .RecordCount, as sometimes (very rarely) .EOF can give a false negative if there is only one record.

Hope this hepps!

[bebesmom]

Thank you so much not only for the code but for the explanation! As I muddle my way through learning VBA, this sort of thing is hugely helpful! Have a great day!