Mastering Microsoft Access: How to Search for Existing Records and Add New Ones with Ease

In this Microsoft Access tutorial I will teach you how to search to see if a value already exists in the table and if so open that record. If not, prompt to add it as a new record.

Sophie from Laramie, Wyoming (a Platinum Member) asks: Is there a way I can look up a customer by their phone number, and if it doesn't exist, ask the user to add them as a new record?

BECOME A MEMBER:

LEARN MORE:

PREREQUISITES:

LINKS:

RECOMMENDED COURSES:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, search, existing record, new record, data management, add if doesn't exist

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[paulojorgecasanova]

I love your explanations, for me you are the best... you are a great teacher, thank you.

[toddbigcat]

Might want to add that if you are looking up character vs numbers then the Dim function needs to be - Dim ID as String - . Using the Long option will give a Type mismatch error when entering any thing else but numbers. Also change - If ID ="0" - and the end of the DLookup statement needs to be "0" as well.

[joseancheta]

Thank you! Although my scenario is slightly different, this code is definitely useful.

[dohcvtec]

I love your videos and it taught me a lot from a beginner.
Here is my question, while in an opened form I’d like to check if a record existed when typing in the data field and if yes go to the record. Previously I built another database file, I was able to do the check and open a different form (like customer details) when data existed, but not able to figure out how to do the very same thing on the same form. 🙏 Thanks

[jsingh]

great channel!
I have a question.
I have a form set up with the search box and a search command button. when the user types in the search box and clicks on the search button, it should bring back the record in the list box with a message saying X number of records found else no record.
Please help

[christofzwanziger]

Thank you for your top video explanation. They helped me a lot in developing my db.

[rogerazevedo]

You’re a legend. Thanks very much for solving my problem.

[Jojosmith]

thumbs up to the BEST teacher as all time. Thanks Richard. Is there anyway to search a specific for the Form that displays no record when first opened? My DB is > 100, 000 records. So when form first opened it is set to blank DoCmd.GoToRecord, , acNewRec. (display no records when first opened)