Check for an existing record and add if it doesn't exist

[Srubin]

I am working on an Access 2000 database in which I need to be able to verify that a record exists when the user enters the phone number (this is the first field and the Key). If it does exist, I need the record to be the current record for the user to view. If it doesn't exist I need it to prompt the user that it does not exist and display a blank screen to enter the new information.

I have found many examples but nothing that seems to work for my needs yet.

Thanks in advance for any advice.

 

[scottfarcus]

See if this works for you...

 

[Srubin]

check for existing record

Scott,
Thanks for the reply. This would work except I am needing for the user to be able to enter the phone number in the phone number field in the form, then check for an existing record and bring up the record if it exists. If it doesn't then allow the user to add a new record. Does this make sense or am I asking for the impossible?

 

[Srubin]

tried it, Access hates DAO

Scott,
I decided that the user will hae to give just a little on this project. So I incorporated the form and the code into the project. When testing this, it stops at the line:

Dim rs AS DAO.Recordset

Access 2000 apparently hates DAO. How can I recode this or tell Access to like it?

Thanks again.

 

[Srubin]

I think I got it

I changed the statement from:

Dim rs AS DAO.Recordset

to

Dim rs AS Object

This took care of the problem

 

[scottfarcus]

You can either add a reference to DAO OR you can just use:

Dim rs As Recordset