Query Criteria to limit results to existing records

[TKT_BEER]

Hi everybody, hope your new year was a good one! here it goes..

A LITTLE HISTORY
I have a SELECT QUERY that is attached to a form, every time you open the form a ENTER PARAMETER value box appears, Query is going after an AUTONUM so when you enter a REQ number all the information related to this number will populate.

BUT everytime that I enter a number that doesnt exist, the QUERY shows an EMPTY form.

Is there a way to limit the query to the existing records so that if you enter a REQ that is nonexisten (EX. you have reqs 1 thourgh 10 and you enter req. #11) a MSG box appears telling me that the req does not exist or is not available? any help is appreciated!

 

[drjekyll325]

Here's how I'd solve the problem:

1) Create a form that resembles a windows dialog box to ask for the req. number.
2) In the On Open event of your main form, open the dialog box.
3) In the click event for the OK button on your dialog box, open a recordset for the query (select reqnum from requisitions).
4) If the recordcount property of the recordset is zero, display the "Req. does not exist." message, otherwise close the dialog box, make the main form active and go to the appropriate record.

Good luck!

 

[TKT_BEER]

it's opening the ENTER PARAMETER VALUE MSG BOX before opening the form that I did... isnt there a way to do this in the SQL view for this query?

 

[drjekyll325]

Not if you want to display your custom message box. Sorry!