Form Handling when Record not Found

[bergis]

I am looking for a way to handle an open form when there is no data found for a particular selection filter. I get a blank form with no way to go back to previous forms or close without closing form with R click of mouse and going back manually to the previous form.

Any help is appreciated a Beginner to Intermediate Access user.

JB

 

[JamesMcS]

You could use something like DCount I would think.

Sub form_open()
If dcount("*","FormRecordsource","[Filterfield]=Filtercriteria")=0 then
Msgbox "Nothing to display"
form.close

Or something along those lines....

 

[vbaInet]

You could use something like DCount I would think.

Sub form_open()
If dcount("*","FormRecordsource","[Filterfield]=Filtercriteria")=0 then
Msgbox "Nothing to display"
form.close

Or something along those lines....

You will do the above from JamesMcS on the Click event of the button and there's a slight change:

If DCount("*","[COLOR=black][B]FormRecordsource[/B][/COLOR]","[Filterfield]=Filtercriteria")=0 then
    MsgBox "Nothing to display"
Else
    DoCmd.OpenForm "FormName", acNormal
End if

Where FormRecordSource is the name of the query bound to your form. Yes, it needs to be a query.

 

[JamesMcS]

Ah, but what if there are records in the recordsource, just not matching whatever filter criteria the user has selected?

Misread the open for bit as form open, dummy....

 

[vbaInet]

Ah, but what if there are records in the recordsource, just not matching whatever filter criteria the user has selected?

I missed out your filter. Just added that back in.

 

[JamesMcS]

Might also be worth mentioning that the filtercriteria bit of that could be passed from elsewhere as a variable:

If DCount("*","Formrecordsource","[Filterfield]=" & variablename)

or if the filter is a string,

If DCount("*","Formrecordsource","[Filterfield]='" & variablename"'")