checking subquery for no results

[scottshiv]

I have report that I start with a command button on a form whose record source is a series of queries. The first query may not find any records. The last query that feeds into the report always has records regardless of the results of the first subquery, so the 'no data' event does not work.

If the first query gets no hits, I want to shutdown the process, display a message, and let the user try again.

How do I do that?

Thank you,

Scott

 

[ProgramError]

Can we see the code you are using.


Ian Mayor (UK)
Program Error
There's ALWAYS more than one way to skin a cat!
But only one way to get it RIGHT!

 

[scottshiv]

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.