Record does not exist, open an add form

[TomKat]

I want to add a feature to my query where if the record does not exist based on a field, it will inform the user and then try on another field. If both values do not exist then it will open a form to add the information.

Basically, I have a button that before it opens the form, it runs a query to search for a record. Even if the record does not exist,
it will open the form and will be blank. If there is a record, it will display that data. I want to add a feature where if the record
does not exist, to inform the user and try again based on another field from the same table. If neither entries exist, or the fields
contain a null value to display a msg to such effect and open a form to where the user can add the data.

I want to do the same for a table with user/employee information, where they would search on last name.

 

[ajetrumpet]

A good way to do this is to use the DCOUNT function before you run the query code (or macro that you are using). Check if the values are in the table with that function, and popup a message box if they are not found with some options on it (yes/no/cancel).

If the form is based on the query though, make sure it is not opening in all situations. You will have to write code for this, so if you're not using code now, you will have to change the method.

 

[TomKat]

Do you have an example of what you are suggesting?

I currently have a query setup to ask the user for only one value. If I can do it directly from the table and populate the form that would be great. I am tweaking the database based on user input and this is one of the suggestions.

You can give me an example based on two fields, "LastName" and "FirstName" from a Employee Table.

Thanks