Query Returns No Record 1

[InfoNow]

How can I have a msgbox comes up if a query returns no record??

 

[wemeier]

How are you running the query? If you open the query (by double-clicking on the query name or by using the OpenQuery method, then the answer is no. If you are binding a form to a query or if you are opening a DAO recordset of a query, then the answer is yes.

For a form, in the OnOpen event of the form you can check if there are no records with the following:

If Me.RecordsetClone.EOF Then MsgBox "NO RECORDS FOUND"

Likewise, if you open a recordset using VBA code, you can check for EOF:

Dim db as Database
Dim rst as Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("queryname&quot
If rst.EOF Then MsgBox "NO RECORDS FOUND"

 

[Bry12345]

Use DCount with an If, Then.

If the DCount of the query = 0, then execute your message box, else 'do nothing.

B. - - - -

Bryan

 

[wemeier]

Good thought, Bry12345. That would let you test a query that is run by opening it.

However, keep in mind that this executes the query in order to evaluate the DCount. This means that you will exedute the query twice -- once for the DCount and once for real. If the query runs against a large recordset, this could seriously impact performance.

 

[InfoNow]

wemeier,
Thank you for your quick reply. The solution you suggested works perfectly. The form is based on the query and when open asked the user to enter a partnumber. If record exist, open the form, if not msgbox and close the form. Works great. Thanks again

Bryan,
Thank you for replying as well.