Solved Using Recordset Value in File Name Path (1 Viewer)

[Bgwade]

Hello,

I have applied some code (below) that will filter and export reports based on a table field value, locations. I have folders with each location as it's title. I am now trying to automatically have the report filtered to "Tampa" go to the Tampa folder. I have tried several ways but none have worked (examples below). Any advice would be appreciated. Thank you in advance.

Private Sub VBA_Click()

Dim strFile As String
Dim RPT As String
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("Location from tblProcessingFO")
strFile = "C:\Users\Bwade\Documents\Projects\"
RPT = "Report1"

Do While Not rs.EOF
DoCmd.OpenReport "Report1", acViewPreview, , "Location = '" & rs(0) & "'"
DoCmd.OutputTo acOutputReport, RPT, acFormatPDF, strFile & rs(0) & ".pdf"
DoCmd.Close acReport, RPT
rs.MoveNext
Loop

Set rs = Nothing
End Sub

Individual attempts I made

strFile = "C:\Users\Bwade\Documents\Projects\" & rs(0) &"\"

The above resulted in all of the reports going into the alphabetically first folder.

strFile = "C:\Users\Bwade\Documents\Projects\" & rs &"\"

Reports went into unconnected files

DoCmd.OutputTo acOutputReport, RPT, acFormatPDF, strFile & rs(0) & ".pdf"

Added Location to file name. I figured my error out with this one.

 

[theDBguy]

The above resulted in all of the reports going into the alphabetically first folder.

Just move that line inside the Do While loop.

 

[Bgwade]

Just move that line inside the Do While loop.

That did it. Thank you so much.

 

[theDBguy]

That did it. Thank you so much.

You're welcome. Glad we could help. Good luck with your project.