473,249 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,249 software developers and data experts.

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 1336
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

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

Similar topics

3
by: Scott Morford | last post by:
I am developing a weed management database for the preserve I work on. One of the queries I'm working on will allow the user to run a query and see which weed patches have NOT been treated in the...
1
by: bdt513 | last post by:
I am trying to extract the values from a query using VBA. Specifically, I want to concatenate all the values of the "rosEmail" field from query "qselRosterEmailList" into one string (strEmails). I...
3
by: azzi2000 | last post by:
This should be rather simple. I have a query using different link tables and 2 parameters. The query works perfect. However I need to export or save the result in an Access table in order to...
4
by: Konrad Hammerer | last post by:
Hi! I have the following problem: I have a query (a) using another query (b) to get the amount of records of this other query (b), means: select count(MNR) as Number from...
1
by: Manners | last post by:
Hi! I am a VBA newbie, and cant seem to proceed past my current problem. I have a large query, where I need to output a select query based on the parameter set by the do loop. The do loop is...
3
by: Wayne | last post by:
I'm trying to automate the export of a query to a text file using code. If I export the query manually I get the exact result that I want i.e. If I select the query then choose File/Export from...
9
by: Mmmel | last post by:
I'm baaaack! I've searched for a while but could find no answers. Could you help me? I have table that contains, among other things, an email address and a date field for the day that I emailed...
2
by: mrsmontal | last post by:
I am trying to create a database for a new magazine company. I tried to break down all of the possible table parts and use the relationships properly. I am pretty new at this. I tried to make a...
6
by: PakerBaker | last post by:
Hi there I have a 20 character string and I want to get the numbers from position 10 to 14 when they are equal to 5600 I did my access query in design mode as follows but I keep getting syntax...
1
by: tallen | last post by:
ADezii and zmbd, I have one other question. There seems to be some other code which possibly should reflect the new DAO code. the following does not allow forms to open any longer and files were...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.