Experts:
I had some great help earlier today with defining a process that allows me to a) select a values or number of values from a listbox and then b) output all reports in batch process based the value(s) selected in the listbox.
This process works great!!!!
However, I am opening this new thread as follow-up w/ some additional tweaking that may be required.
Here's the process with a few more details:
- The listbox's data source is linked to another table which include a larger list of organization values.
- For example, I may have 25 org values in the listbox
- Now, I can simply hightlight all 25 org values and then click "Extract Reports"
- At this time, the VBA generated 25 separate reports (one after the other... in a few seconds)
- However, in some instances, there may be a few organization which don't have any records that match the query.
- Thus, I have a few reports that are empty and show "#Type!" in the report header which shows the organization's name
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
'Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Q201_CFT_Ownership_Report_Ncode_Gonzales_RPT")
'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\Reports\CFT Ownership\CFT Ownership Report - "
ReportPathMsgBox = "C:\Users\dellc\Reports\CFT Ownership"
'Loop through the selected items in the list box and build a text string
If Me!lstCFTOwners.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstCFTOwners.ItemsSelected
strCriteria = "T11_CrossFunctionalTeam.CFT_Owner = '" & Me!lstCFTOwners.ItemData(varItem) & "'"
'Build the new SQL statement incorporating the string
strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
"T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, Count(T00_JunctionTable_BCFT.BilletIDfk) AS NoParticipants, " & _
"T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
"T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, " & _
"NCode_Group([N_Code]) AS NCode_Group FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
"RIGHT JOIN ((T99_SortingNCodes INNER JOIN T11_CrossFunctionalTeam ON T99_SortingNCodes.[NCode] = T11_CrossFunctionalTeam.CFT_Owner) " & _
"INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) " & _
"INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
"ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk " & _
"GROUP BY T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, " & _
"T11_CrossFunctionalTeam.CFT_Description, T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
"T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, NCode_Group([N_Code]) " & _
"" & "HAVING " & strCriteria & " " & _
"ORDER BY 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!lstCFTOwners.ItemData(varItem), "/", "_") & ".pdf"
OutputPathFileName = ReportPath & ReportFileName
'Outputs file(s) to specified file path
DoCmd.OutputTo acOutputReport, "R_CFT_Ownership_Report_Gonzalez", acFormatPDF, OutputPathFileName, False
Next varItem
'Throw message box upon successful extraction of the PDFs
MsgBox "The CFT Ownership reports were stored at 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 N-Codes!", vbInformation, "Information"
End If
'Empty the memory
Set qdf = Nothing
Set db = Nothing
End Sub
My question:
- For those reports where the query did not return any results, I'd like to skip the report generation. How can the code below be modified to skip any reports where there's no data match?
Thank you,
EEH