Msgbox for form

[Eric Starn]

I have a form based on a query. When it opens, if the underlying query has no
records to display I want it to tell the user this. I know it is suppose to
be some sort of msgbox. I don't know what code is needed for this and where
it needs to go.

Any help will be greatly appreciated

Eric

 

[Tom van Stiphout]

On Wed, 30 Dec 2009 08:28:01 -0800, Eric Starn

I would say it's already pretty obvious there is no data, but if you
insist:
In the Form_Load event write the one-liner:
If Me.RecordsetClone.RecordCount = 0 then Msgbox "Yo! No data here."

Note that reports have a NoData event for this purpose.

-Tom.
Microsoft Access MVP

 

[Chegu Tom]

Do you want to open the form if there is no data? Are you opening a form
with a 'where' criteria.

You could also check for records before you open the form. Using the same
criteria you use to open the form to do a Dcount() which will count the
number of records in that table that fit that criteria.

if dcount("yourcontrol","yourtable","criteria")=0 then
msgbox "no records"
else
open your form as you do now
endif

This way your form won't even open if there are no records.

 

[Tom van Stiphout]

Actually, the better place to do this is in the Form_Open:
If Me.RecordsetClone.RecordCount = 0 then
Msgbox "Yo! No data here. Closing..."
Cancel = True
End If

Why better? Because you have to write the code only in one place,
regardless of how it is being opened.

My code is also better because it uses the actual recordset underlying
the form to decide if there are zero records. Your DLookup approach
would have to carefully mimick the form, and if I change the query
underlying the form, I would have to remember to also update the
DLookup logic which will be in a different place in the code and I (as
the poor maintenance programmer) may not even be aware of it.

-Tom.
Microsoft Access MVP