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

[Manners]

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

[JConsulting]

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

 
Function ExportTxt()
Dim rs As dao.Recordset
Dim qDf As QueryDef
Dim strSQL As String
Dim myLocation As String
Dim myFile As Long
Set rs = CurrentDb.OpenRecordset("select distinct Location from Locations;")
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 = "C:\myfile.xxx"
strSQL = "SELECT * FROM Locations WHERE Location='" & myMyLocation & "';"
Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
DoCmd.TransferText acExportDelim, , "MyQuery", myFile, False
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
 
 

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