469,125 Members | 1,638 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

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

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
Expand|Select|Wrap|Line Numbers
  1. Function ExportRpt()
  2. Dim rs As Recordset
  3. Dim qDf As QueryDef
  4. Dim strSQL As String
  5. Dim myLocation As String
  6. Dim myFile As Long
  7. Set rs = CurrentDb.OpenRecordset("select ClientNumber from tblFileNames;")
  8. If rs.EOF Then Exit Function
  9. rs.MoveFirst
  10. On Error Resume Next
  11. DoCmd.DeleteObject acQuery, "MyQuery"   '<--- This deletes the old temp query getting ready to make a new one
  12. myLocation = rs!Ballot_Style
  13. myFile = "P:\LPSOutputs\FinishedReports\test.xlsx"
  14. strSQL = "SELECT * FROM QryData WHERE ClientNumber='" & myMyLocation & "';"
  15. Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
  16. DoCmd.TransferSpreadsheet acExport, , "MyQuery", myFile, False
  17. rs.MoveNext
  18. Loop
  19. rs.Close
  20. Set rs = Nothing
  21. End Function
Jun 6 '18 #1

✓ answered by twinnyfo

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Function ExportRpt()
  5. On Error GoTo EH
  6.     Dim RS          As Recordset
  7.     Dim qDf         As QueryDef
  8.     Dim strSQL      As String
  9.     Dim myLocation  As String
  10.     Dim myFile      As Long
  11.  
  12.     strSQL = "SELECT ClientNumber FROM tblFileNames;"
  13.     Set RS = CurrentDb.OpenRecordset(strSQL)
  14.     With RS
  15.         If Not .RecordCount = 0 Then
  16.             .MoveFirst
  17.             Do While Not .EOF
  18.                 DoCmd.DeleteObject acQuery, "MyQuery"
  19.                 myLocation = !Ballot_Style
  20.                 myFile = "P:\LPSOutputs\FinishedReports\test.xlsx"
  21.                 strSQL = "SELECT * FROM QryData " & _
  22.                     "WHERE ClientNumber='" & myMyLocation & "';"
  23.                 Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
  24.                 DoCmd.TransferSpreadsheet acExport, , _
  25.                     "MyQuery", myFile, False
  26.                 .MoveNext
  27.             Loop
  28.         End If
  29.         .Close
  30.     End With
  31.     Set RS = Nothing
  32.  
  33.     Exit Function
  34. EH:
  35.     MsgBox "There was an error exporting the client data!" & vbCrLf & vbCrLf & _
  36.         "Error: " & Err.Number & vbCrLf & _
  37.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  38.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  39.     Exit Function
  40. 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!

3 1024
twinnyfo
3,653 Expert Mod 2GB
bebesmom,

Welcome to Bytes!

Looks like you need

Expand|Select|Wrap|Line Numbers
  1. Do While Not rs.EOF
Between lines 10 and 11.

Hope this helps!
Jun 7 '18 #2
twinnyfo
3,653 Expert Mod 2GB
Also, I wanted to recommend a few minor changes as good practices:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Function ExportRpt()
  5. On Error GoTo EH
  6.     Dim RS          As Recordset
  7.     Dim qDf         As QueryDef
  8.     Dim strSQL      As String
  9.     Dim myLocation  As String
  10.     Dim myFile      As Long
  11.  
  12.     strSQL = "SELECT ClientNumber FROM tblFileNames;"
  13.     Set RS = CurrentDb.OpenRecordset(strSQL)
  14.     With RS
  15.         If Not .RecordCount = 0 Then
  16.             .MoveFirst
  17.             Do While Not .EOF
  18.                 DoCmd.DeleteObject acQuery, "MyQuery"
  19.                 myLocation = !Ballot_Style
  20.                 myFile = "P:\LPSOutputs\FinishedReports\test.xlsx"
  21.                 strSQL = "SELECT * FROM QryData " & _
  22.                     "WHERE ClientNumber='" & myMyLocation & "';"
  23.                 Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
  24.                 DoCmd.TransferSpreadsheet acExport, , _
  25.                     "MyQuery", myFile, False
  26.                 .MoveNext
  27.             Loop
  28.         End If
  29.         .Close
  30.     End With
  31.     Set RS = Nothing
  32.  
  33.     Exit Function
  34. EH:
  35.     MsgBox "There was an error exporting the client data!" & vbCrLf & vbCrLf & _
  36.         "Error: " & Err.Number & vbCrLf & _
  37.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  38.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  39.     Exit Function
  40. 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!
Jun 7 '18 #3
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!
Jun 7 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.