Thanks Gina!
I think I run into an issue, and I might know what might be the cause already, but would like to ask for your advice.
Here is the code:
Code:
Function fSaveReportsAsPDF() As String
On Error GoTo Error_Proc
' From https://www.access-diva.com/
' https://www.access-diva.com/vba3.html
DoCmd.Hourglass True
Dim strSQL As String
Dim rs As Recordset
Dim strPath As String
strPath = "D:\Endeavor SBC\Consulting\16 - Mephisto\01 - IT\01 - DB\Reports\"
strSQL = "SELECT Location_Name FROM 20181018_Stock_Report"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
.MoveFirst
Do While Not .EOF
DoCmd.OpenReport "20181018_Location_Report", acViewPreview, , "[Location_Name] = '" & rs!Location_Name & "'" 'Select Report, Search Field
DoCmd.Minimize
DoCmd.OutputTo acOutputReport, "20181018_Location_Report", acFormatPDF, strPath & !Location_Name & ".pdf"
DoCmd.Close acReport, "20181018_Location_Report", acSaveNo
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Exit_Proc:
DoCmd.Hourglass False
Exit Function
Error_Proc:
Select Case Err.Number
Case 287:
Resume Exit_Proc 'ignore the error
Case Else:
MsgBox "Error encountered fSaveReports: " & Err.Description, vbExclamation, Err.Number
Resume Exit_Proc 'display a message then exit
End Select
End Function
20181018_Stock_Report is, as the name suggest, the report I use as the basis of the actual report in Access. Just a plain table listing each store + product sold + size.
20181018_Location_Report is my Access report, based on the table I just mentioned (+ 2 other tables), organized via crosstab and grouped by Location Name in order to generate a separate page for each location.
If I now execute the function, is works - up to a certain point where (I assume) it gets stuck in a loop. My question is: " strSQL = "SELECT Location_Name FROM 20181018_Stock_Report"" indicates the search term, correct? The item "Location_Name" in the Stock_Report table is not a unique name, meaning it is mentioned several times during the document. So, my guess would be that due to this the function is not properly finishing, as there are several hundred locations with the same name.
My guess would be that I add a unique identifier to my report (like location_ID) and set strSQL to the location master table instead of the report. In that case my values would be unique...
... but of course, this is just a guess as I donŽt have any clue in the end ;-)
Thanks and regards,
Mike