help with Key field in form

[hmho]

I have table that I put in product information and it has these fields
[ProdNumber] [ProdName] [FaceValue] and the table name is [TblProductInfo]. The ProdNumber is the key and i set up form to enter new products that comes in. How can I code it when someone tries to enter duplicate that message will come up and tell them the product is already in instead of access standard message also if the product is already in they can leave the form. right now when i tried to enter duplicate I got the standrd error and when i delete i got the error message says Key field can't be null. Please help.

 

[SOS]

Let me get this straight -

1. The users type in the primary key (ProdNum)?

2. If they type in a duplicate you want to exit gracefully?


To answer that -

1. I would not be having users entering the key (I would use an autonumber for the key field, if you aren't already).

2. You use the form's BEFORE UPDATE event to check to see if something exists and then if it does you can issue a Cancel = True to stop the attempt before it does and which will not then give you that error. But if they want to vacate the entire record then you would need to also use
Me.Undo
to remove all traces of what they were trying to do.

 

[hmho]

Each product has it is own number so I have to use that number not auto number. The first field is the first thing in the form and I want if they tried to enter duplicate to get message and to leave the record or enter the next product. I am not good with access so i hope this makes sence

 

[SOS]

Each product has it is own number so I have to use that number not auto number.

Actually, you do NOT have to use that number as the KEY. You can include it in the database, but it has been known to have two products have the exact same product number (usually you find this by accident). So, I would still suggest using the Autonumber as the primary KEY and you could set the prodnum as an indexed, no duplicates field but it wouldn't matter since you would be checking in the BEFORE UPDATE of the form anyway.

[/quote]The first field is the first thing in the form and I want if they tried to enter duplicate to get message and to leave the record or enter the next product. I am not good with access so i hope this makes sence[/quote]
You could use the Before Update event of the text box for the prodnum to check (using something like:

If DCount("[prodnum]", "TblProductInfo", "[prodnum]='" & Me!YourTextBoxOnForm & "'") > 0 Then
        MsgBox "This is a duplicate prodnum", vbOkOnly, "Duplicate"
        Cancel = True
        Me.Undo
End If