If no records match then msgbox 'no records found' instead of opening blank form

[joe789]

Hi Folks,

I have a command button that essentially opens a form based on a date populated by the user so that any records matching that date will be returned:

DoCmd.OpenForm "Scheduling", , , "[Date of Appt] = Forms!FINDSCHEDULING!Text22" & " AND " & "[Agency] = Forms!FINDSCHEDULING!Text46"

It works perfectly fine; however, if a user searches for a date that results in no records match/returned, the form still opens ... is there a way that if no records are found, instead of opening the blank form I can just do a msgbox "No records found".

Thank you for any assistance you can provide,

Joe

 

[pbaldy]

You can use a DCount() the same criteria and only open the form if it's greater than 0.

 

[The_Doc_Man]

In the Report_Open routine, you can test your report's recordsource with whatever filters are enabled for it. If you open a recordset by

Set TestRS = Me.Recordsource.Clone
TestRS.MoveFirst
TestRS.MoveLast

X = TestRS.Count
Cancel = ( X = 0 )

MSGBOX "Report is empty", vbOKOnly, "No need to open report"

TestRS.Close
Set TestRS = Nothing

This works because the document _Open event can be canceled and you have access to the recordset (implied by .RecordSource as modified by any filters). So you can use the recordset clone to count records before you bother.

 

[pbaldy]

If it was a report, I'd just use the no data event.

 

[The_Doc_Man]

Whoops, forgot about that one, paul. I usually trap the "no data" case externally but was offering yet another way to skin cats. <<<<MEEEEOOOOWW>>>>

 

[pbaldy]

Your option is still valid here since it's for a form. It's a matter of checking before you open or after. I generally prefer before but your method would be simpler if the form might be opened from many places, as you'd only have the test in one place.