How to let user no there are no records returned from Qry

[cer5yc]

I am having a bit of a problem trying to figure out how to do the following and am hoping someone can help provide some ideas. Here's the situation:

I have a form where when the user clicks on a button called "Contracts" another dialog form pops up where they can select values from two drop down boxes to filter the records returned. Once they hit the "Search" button on the dialog form a query runs to return the filtered records. I need a MsgBox to pop up on the screen saying "No Records Returned" if once the query runs there are no records returned. I am thinking the way to do this is once the user clicks the "Search" button on the dialog form, create a recordset, check the recordset to see if any records are returned, if no create the message box.

I am having some problems trying to figure out how to write this correctly in VBA and keep getting errors.

Here is the SQL for my filter query ("qryFilterData"):

SELECT Data.[Procurement Lead], Data.ProjectStatus
FROM Data
WHERE (((Data.[Procurement Lead])=IIf([Forms]![FilterForm]![FilterFormProcurementLead].[Value]="All",[Data].[Procurement Lead],[Forms]![FilterForm]![FilterFormProcurementLead])) AND ((Data.ProjectStatus)=IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]="Open","1",IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]="Closed","2",IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]="All",[Data].[ProjectStatus])))));

I could be way off base here so if there is an easier way to do this please let me know. If this is the right way to go could someone please provide some assistance on how to get it done in VBA (specifically how to create the recordset and check to see if it is empty). Thanks!!

 

[genomon]

Check out help on the DCount function. If it returns zero with the parameters you supply, you can pop up a msgbox without all the hassle of opening a recordset and actually executing the query in code.

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[cer5yc]

I looked at that previously but couldn't figure out how it would help me. Can you be more specific? I'm a bit confused ... sorry

 

[B827]

See

http://msdn2.microsoft.com/en-us/library/aa211471.aspx

Sandy