Code:
Private Sub GenerateGroupReports_Click()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim sFolder As String
Dim qry As String
' Open the select folder prompt
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose your save location"
.ButtonName = "OK"
If .Show = -1 Then ' if OK is pressed assign folder name to sFolder variable
sFolder = .SelectedItems(1)
Else
Exit Sub
End If
End With
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT DISTINCT ManagerName, Prod From GroupManagerForReportQ", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then 'make sure your results actually returned something or you'll get an error
rs.MoveFirst
Do While Not rs.EOF
temp = rs("ManagerName")
MyFileName = "Group Access to " & rs("Prod") & " Resources by Manager Report - " & rs("ManagerName") & ".PDF"
DoCmd.OpenReport "GroupManagerR", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
DoCmd.OutputTo acOutputReport, "GroupManagerR", acFormatPDF, sFolder & "\" & MyFileName
DoCmd.Close acReport, "GroupManagerR"
rs.MoveNext
Loop
End If
rs.Close
'DoCmd.SetWarnings False
' This works INSERT INTO ReverReportDetailsT
'DoCmd.RunSQL "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
' "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
' "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
' "FROM GroupManagerForReportQ;"
'use db.Execute rather than RunSQL so you don't have to risk turning warnings off and on
qry = "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
"SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
"GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
"FROM GroupManagerForReportQ;"
db.Execute qry, dbFailOnError
'DoCmd.SetWarnings True ' I turned warnings back on to see if I would get any messages, nothing.
' Neither of the below work (Yes, I remove the comment tags)
' I've commented out the options I've tried that don't work. Keeping here so I know what I've already tried
qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
"SELECT DISTINCT MgrSerNum, EmployeeID, UserIDPK, SystemID, Production " & _
"FROM ReverUserIDsForAccessLogTQ;"
db.Execute qry, dbFailOnError
qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
"VALUES (""" & MgrSerNum & """, EmployeeID, UserIDPK, """ & SystemID & """, """ & Production & """)" & _
" FROM ReverUserIDsForAccessLogTQ;"
db.Execute qry, dbFailOnError
qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
"VALUES (""" & MgrSerNum & """, """ & EmployeeID & """, """ & UserIDPK & """, """ & SystemID & """, """ & Production & """)" & _
" FROM ReverUserIDsForAccessLogTQ;"
db.Execute qry, dbFailOnError
'DoCmd.OpenQuery "ReverUserIDsForAccessLogTQ"
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbCrLf & vbCrLf & "SQL:" & vbCrLf & qry, , "Error #" & Err.Number
Resume ExitHandler
End Sub