If no records do not open form

[DH]

I am trying to avoid having a form open if no records exist for the form.

I would rather have a message box pop up stating that there are no records to display rather than a blank shell of a form opening.

I there a way to count and determine if any records exist before displaying the form?

 

[Ziggurat]

Hi DH
You could try defining a recordset for your table/query.
Assuming you are using DAO3.6 then:-

dim db as Database
dim rst as Recordset
dim lngNumberOfRecords as Long
set db=currentdb()
set rst=dn.openrecordset(&quot;<Your Table Name>&quot
if rst.bof and rst.eof then
'There are no records so display a message box
msgbox &quot;No Records&quot;
Else
docmd.openform &quot;<Your Form Name>&quot;
end if

Miguel

 

[kwfrazier]

When I use this with a table, it works great; however, when I specify a Query, the function fails and wants to debug the line with the query name on it. My query has one field that uses a form control as the criteria.

Basically, Iwant to run a query against an entered ID number (not unique) and if there are no matching records, I want a pop up message. Basically what you have above - but working with a query.

I know I'm close, but I just can't get it to run with my query. Any help would be appreciated.

Ken

 

[DH]

I currently use the code with a query and it is working fine so far. You may have to set the ID Number you enter as criteria for the query.

1.) You can do this by creating a 1 field table called IDNumber.
2.) Next create an ID entry form where the user can enter an IdNumber.
3.) add an OK command button to the form that save the current form,close it, and open the form you wish to show.
4.) On the form you wish to show put the above code in the onopen event.
5.) in the &quot;if&quot; portion of the code add what ever message box you want and then add a line of code that will close the form you just opened.
6.) the else portion should be left as it is above, you may want to add Docmd.maximize to maximize the form.
7.) on the forms close event add cose to run a delete query to delete the IDnumber from the 1 field IdNumber table you created in step 1. This way, each time a user opens the ID entry form the IDNumber field will be blank.

Give it a try.