By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Help! Export a Query using a Do Loop Result (VBA Access)

P: 1
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 running through locations, where there will be many records per each location set in the parameter. The output needs to go to the same file location each time and then trigger a format macro in Excel.

The bit I am stuck with is taking the do loop result and making this a parameter for the query to then successfully output??!!! Ive hit a real dead end.. My do loop below. Any help or hint would be much appreciated!! Thanks.

Function LocationOutput()

Dim db As DAO.Database
Dim myset As DAO.Recordset
Dim Location As String

Set db = CurrentDB
Set myset = db.OpenRecordSet ("Customer_Tbl")

Do Until myset.EOF
Location = myset![Location Name]

'Output query result and run macro here...

myset.MoveNext
Loop
End Function
Jul 24 '07 #1
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
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 running through locations, where there will be many records per each location set in the parameter. The output needs to go to the same file location each time and then trigger a format macro in Excel.

The bit I am stuck with is taking the do loop result and making this a parameter for the query to then successfully output??!!! Ive hit a real dead end.. My do loop below. Any help or hint would be much appreciated!! Thanks.

Function LocationOutput()

Dim db As DAO.Database
Dim myset As DAO.Recordset
Dim Location As String

Set db = CurrentDB
Set myset = db.OpenRecordSet ("Customer_Tbl")

Do Until myset.EOF
Location = myset![Location Name]

'Output query result and run macro here...

myset.MoveNext
Loop
End Function

something like so will get you going

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function ExportTxt()
  3. Dim rs As dao.Recordset
  4. Dim qDf As QueryDef
  5. Dim strSQL As String
  6. Dim myLocation As String
  7. Dim myFile As Long
  8. Set rs = CurrentDb.OpenRecordset("select distinct Location from Locations;")
  9. If rs.EOF Then Exit Function
  10. rs.MoveFirst
  11. On Error Resume Next
  12. DoCmd.DeleteObject acQuery, "MyQuery"   '<--- This deletes the old temp query getting ready to make a new one
  13. myLocation = rs!Ballot_Style
  14. myFile = "C:\myfile.xxx"
  15. strSQL = "SELECT * FROM Locations WHERE Location='" & myMyLocation & "';"
  16. Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
  17. DoCmd.TransferText acExportDelim, , "MyQuery", myFile, False
  18. rs.MoveNext
  19. Loop
  20. rs.Close
  21. Set rs = Nothing
  22. End Function
  23.  
  24.  
It creates a "temp" query that you use to export to whatever file name you want. you can create a variable filename to match your location or whatever.
J
Jul 31 '07 #2

Post your reply

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