My actual application is kinda complex, so let me give a simplified example. Suppose a user wants to know how many new hires were placed in each corp. division over a given time period and they want to know the count for each division even if it is zero.
In the application the user enters their date range on the new hire form and presses the 'print report' command button.
Query A: selects and counts the new hires for each division for the time period.
Query B: Joins query A results with the division description table so the names print instead of the division number. The join properties are a row for each division is created, so query B has a result even though query A may not find any new hires for the specified time period. I realize that in this example you could combine A & B, but in my application they need to be separate.
New Hire report uses query B as its record source, so a file is feed into the report even though there were no new hires for any division. Instead of printing a zero report, I want to display a message that there were no new hires for the selected time period. The 'no data' event code displays a no hit message but it is never executed because there is data even if there are no hew hires.
How do I stop after Query A if there are no rows and display my message or go on without any user intervention if there are hits?
Command Button Code:
Dim stDocName As String
Select Case [Forms]![All Titles]![Title]
Case "055"
stDocName = "State V"
Case "056"
stDocName = "State X"
Case "057"
stDocName = "State XX"
Case "060"
stDocName = "State All Titles"
End Select
DoCmd.OpenReport stDocName, acPreview
'No Data' event code:
Dim errormsg As String
errormsg = "No Recipients Found for the time period you selected"
MsgBox errormsg, vbExclamation
Cancel = True
Query A:
SELECT [PCN Input].PCN, [PCN Input].ClaimType, [PCN Input].DateOfService, [PCN Input].TPI_Base, [PCN Input].ProcedureCode, [HIPPA Crosswalk].NationalCode, [PCN Input].DetailAmtPaid, [PCN Input].ClientAge, [PCN Input].ClientCounty
FROM [HIPPA Crosswalk] RIGHT JOIN [PCN Input] ON [HIPPA Crosswalk].ProcedureCode = [PCN Input].ProcedureCode
WHERE ((([PCN Input].ClaimType) Between IIf([Forms]![All Titles]![Title]='060',"055",[Forms]![All Titles]![Title]) And IIf([Forms]![All Titles]![Title]='060',"057",[Forms]![All Titles]![Title])) AND (([PCN Input].DateOfService) Between [Forms]![All Titles]![Begin Date] And [Forms]![All Titles]![End Date]));
I can supply the code for all of the other queries but there are several of them that is why I used the simplified example.