Prevent duplicate record & goto original entry...

[Joven76]

I have researched this forum about this topic and have learned a few things... But it seemed like everyone trying prevent a duplicate entry was doing it based off of 2 fields... I only need to base it off one...

I have a database with a table and corresponding form called Products... It has the following fields:

Product ID - Autonumber, Primary Key
ProductNo - Text
ProductDesc - Text
COG - Number
CustCost - Number
Qty - Number

I want to prevent accidentally duplicating a product as I enter them into the database. I'm still learning the product numbers & names and have caught myself duplicating product entries...

What I want to happen is as soon as I enter the product number, it searches the database for a duplicate and displays an error message that it's a duplicate part and then takes me to the product already in the system so I can change the quantity on hand...

Here's what I know... I will probably need to write some VBA code and place it in the before_update portion... But do I put that code in the form's before_update or the field??? I have the field ProductNo indexed to "Yes (No Duplicates)"

I appreciate any help you can give...

 

[Grafixx01]

Joven,

I think that I have a book at home that shows how to do this. I believe that it's with an 'if / then' statement with a check. However, I think it's done at the end of the record before you update it. Are you doing this through a form or through datasheets? I know you can also do this through MS Excel, if that's easier than what you're doing overall.

But I will look when I get home.

 

[Joven76]

It's being done with a form... Actually I this database is much larger than just the Products table and form... It's an order entry/expense database... The products table and form are there so I can pull in the various products my wife and I sell when we generate orders...