Access Loop / Query To Excel - DoCmd.OpenQuery?

[Rastro_Jetson]

Dear All,

Thank you in advance for any direction you may have. This is similar to my last post (looping, exporting to PDF). I'm trying to do that same thing with a query, exporting each Carrier's data into their own Excel file. The code runs. It creates separate files and they are correctly named. However, the records are not for a specific carrier. The records are for all carriers So, I know my strWhere is amiss. I'm using "OpenQuery" which doesn't seem to function like OpenReport in regards to the strWhere.

My table with the looped value is "tbl_Carrier_IDs". The looped field is "Carrier_ID". The query is "qry_For_Report".

*****
Option Compare Database
Option Explicit


Public Sub ExportToXLSX()
Const Folder = "C:\Test"
Const Domain = "tbl_Carrier_IDs"
Const LoopedField = "Carrier_ID"
Const QueryName = "qry_For_Report"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)
Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField)
FileName = LoopedFieldValue & ".xlsx"
FullPath = Folder & "" & FileName
strWhere = LoopedField & " = '" & LoopedFieldValue & "'"
'DoCmd.OpenQuery QueryName, acViewPreview *** WHERE TO PUT strWhere ***
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLSX, FullPath
DoCmd.Close acQuery, QueryName

rs.MoveNext
Loop
MsgBox "Process Finished."
End Sub

 

[pbaldy]

OpenQuery has no such argument. The simplest way to use what you already have is to add a criteria to the query that points to a form, and place the LoopedFieldValue in that textbox.

 

[Rastro_Jetson]

Perfect. Thank you so much for the creative solution in light of keeping the little code I already have. I also looked at TransferSpreadsheet, but that doesn't seem to fit the bill either. This forum and users are the best. Thanks again.

 

[pbaldy]

Happy to help!

 

[Rastro_Jetson]

Dear All,

So close to having this functional and on my way to cannibalizing it for several processes... :^)

I'm stuck on the actual loop advancement. This code runs, generates the individual EXCEL files, named for the correct client, etc. However, they are all for the same client. So, it's not advancing to the next client value as far as the query and producing identical reports. (other than the file name)

I've added the criteria in the query, pointing to the form.. added the [LoopedFieldValue] on the form... I'm just stuck with essentially identical reports. Any assistance would be greatly appreciated. Thx.

******
Option Compare Database
Option Explicit


Public Sub ExportToXLSX()
Const Folder = "C:\Test"
Const Domain = "tbl_Carrier_IDs"
Const LoopedField = "Carrier_ID"
Const QueryName = "qry_For_Report"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)
Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField)
FileName = LoopedFieldValue & ".xlsx"
FullPath = Folder & "" & FileName
strWhere = LoopedField & " = '" & LoopedFieldValue & "'"
'DoCmd.OpenQuery QueryName, acViewPreview *** WHERE TO PUT strWhere ***
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLSX, FullPath
DoCmd.Close acQuery, QueryName

rs.MoveNext
Loop
MsgBox "Process Finished."
End Sub

 

[pbaldy]

This:

LoopedFieldValue = rs.Fields(LoopedField)

Is setting the variable, not the form textbox. Use:

Forms!Form1.LoopedFieldValue = rs.Fields(LoopedField)

 

[Rastro_Jetson]

This process "as is" (including purple and red line), produces the correct file names, correct data in the file, but replaces the first value in my tbl_Carrier_ID with the last value when the process completes and I close the form. :^O

If I remove the line 1, it produces a .xlsx file with no name and no data.

I gotta be close... :^)) Thanks in advance.

*******
Option Compare Database
Option Explicit


Public Sub ExportToXLSX()
Const Folder = "C:\Test"
Const Domain = "tbl_Carrier_IDs"
Const LoopedField = "Carrier_ID"
Const QueryName = "qry_For_Report"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)
Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField) 'line 1
Forms!Form1.LoopedFieldValue = rs.Fields(LoopedField) 'line 2
FileName = LoopedFieldValue & ".xlsx"
FullPath = Folder & "" & FileName
strWhere = LoopedField & " = '" & LoopedFieldValue & "'"
'DoCmd.OpenQuery QueryName, acViewPreview *** WHERE TO PUT strWhere ***
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLSX, FullPath
DoCmd.Close acQuery, QueryName

rs.MoveNext
Loop
MsgBox "Process Finished."
End Sub

 

[pbaldy]

Per the picture above, the form control is bound to the field in the table. It should be unbound.

 

[Rastro_Jetson]

Paul, that worked !!!!! The "THANKS" button seems insufficient. :^)))) Thank you. Now to dissect this and learn how it works.... You are a patient rock star, my friend.

 

[pbaldy]

Happy to help!! Post back if you have questions about how it works.