John -- finally... all I needed was that line of code.
Code:
And (" & strCriteria & ") And ((T01_Billets.TFFMS_Last_Update) Is Not Null)) " & _
Sometimes, things that are easy can be complicated (or can be made complicated). Based on what I experienced this weekend, I knew that line contained the error.
At this time, the SQL is outputting one PDF after the other in very rapid fashion. However, after report #26 is created, the VBA "crashes" and highlights line "qdf.SQL = strSQL".
Again, it's a bit stumbling since the first 26 reports were generated in just a few seconds. Is there a limit on how many listbox items I can run through a loop at once?
Code:
Option Compare Database
Option Explicit
Private Sub cmdExportReport_Click()
'Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim ReportPath As String
Dim ReportPathMsgBox As String
Dim ReportFileName As String
Dim OutputPathFileName As String
Dim NumReports As Integer
'Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Q353_MET_CFT_Individual")
'Reports are saved to the below file path -- upon change, ensure to update both **actual report path** AND **message box report path**
ReportPath = "C:\Users\dellc\Documents\01 -- Employers (Present)\USFF\Databases\Projects\System Management Database\Architecture v02\CFT Reports\METs\MET CFT Report - "
ReportPathMsgBox = "C:\Users\dellc\Reports\MET CFT"
'ReportPath = "\\naeanrfkfs101v\USFF02$\NFLT_FLTFORCOM-HQ_N00060\N02 Directorate\N02D\Projects\N1S1 -- Database Modification\Reports -- CFT and OPT\MET CFT Reports\MET CFT Report - "
'ReportPathMsgBox = "...\N02D\N1S Project\...\MET CFT Reports"
'Loop through the selected items in the list box and build a text string
If Me!lstMET_CFTs.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstMET_CFTs.ItemsSelected
strCriteria = "T11_CrossFunctionalTeam.CFT_Description = '" & Me!lstMET_CFTs.ItemData(varItem) & "'"
'Build the new SQL statement incorporating the string
strSQL = "SELECT T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder, " & _
"T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T11_CrossFunctionalTeam.CFT_Owner " & _
"FROM T11_CrossFunctionalTeam RIGHT JOIN ((T01_Billets LEFT JOIN T96_METS ON T01_Billets.aa_CSS = T96_METS.Core_CSS) LEFT JOIN T00_JunctionTable_BCFT " & _
"ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk " & _
"GROUP BY T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder, " & _
"T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T11_CrossFunctionalTeam.CFT_Owner, " & _
"T01_Billets.TFFMS_Last_Update " & _
"HAVING (((T96_METS.MET_Type) <> '[MET_Type -- TO BE DETERMINED]') And ((T11_CrossFunctionalTeam.CFT) Is Not Null) And (" & strCriteria & ") And ((T01_Billets.TFFMS_Last_Update) Is Not Null)) " & _
"ORDER BY T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
'Apply the new SQL statement to the query
qdf.SQL = strSQL
'Replaces any potential forward slashes in the N-Code (e.g., N2/N39) since "/" cannot be used as part of the filename
ReportFileName = Replace(Me!lstMET_CFTs.ItemData(varItem), "/", "_") & ".pdf"
OutputPathFileName = ReportPath & ReportFileName
'Outputs **all** file(s) to specified file path
'DoCmd.OutputTo acOutputReport, "R55_MET_CFT_Individual", acFormatPDF, OutputPathFileName, False
'Outputs only file(s) **with records** to specified file path
If DCount("*", "Q353_MET_CFT_Individual") > 0 Then
DoCmd.OutputTo acOutputReport, "R55_MET_CFT_Individual", acFormatPDF, OutputPathFileName, False
NumReports = NumReports + 1
End If
Next varItem
'Throw message box upon successful extraction of the PDFs
MsgBox NumReports & " MET CFT Participation reports were exported to the following location: " & ReportPathMsgBox, vbInformation, "Information"
Else
'Throw message box in the event user did not select any N-Codes prior to report execution
MsgBox "Please select one or more CFTs!", vbInformation, "Information"
End If
'Empty the memory
Set qdf = Nothing
Set db = Nothing
End Sub