Duplicate value message (1 Viewer)

I have a table with two primary keys, SSN and EntryDate. From a form, I want a custom message box that tells the user that an applicant has already been entered, and not the long default message that access uses. I am using Access 2002 with the 2000 file format. I have tried a lot of stuff, but can't seem to make any thing work. Any help would be great.

First of all you don't have "two primary keys" in one table - you have a composite key - a primary key comprising two or more fields.

To make your own message you have to trap the Form's Error.

I don't know the number of this data error offhand but you can get it by putting the following line into the Code Builder's module of the Form's OnError event.


Run the code and cause the error. You'll get a message box displaying the number.

Now, replace the code with:


Replace **** with the error number...

MsgBox DataErr did not work still recieved the same message. Thanks for the info on the composite key. That makes perfect since.

sloaner14 said:

MsgBox DataErr did not work still recieved the same message.

Click to expand...

I never said it would fix your problem; it was only to get the error number as I couldn't tell you it offhand.

The second part solves that error.

Sorry for not explaining better. I didn't get a message box with an error number. I continued to get "The changes you requested to the table were not succesful because they would create duplicates values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redifine the index to permit duplicate entries and try again." message box.

You state at the outset you are getting the Access command, which means you are trying to enter a new record in a related table, which needs to be added separately first.

I have some code from some of our good friends here (Pat Cowley, Fornation, Jack Cowley and Doug) which shows me :
"IF J Bloggs and deptcode already in system don't enter twice." This is:


Private Sub Combo143_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Inits]", "GOVDOUG", "[Inits]= '" & Me![Inits] & "' And [Surname] = '" & Me![Surname] & "' And [DeptCode] = '" & Me![Deptcode] & "'")) Then
Beep
MsgBox "That full name and Deptcode already exists"
End If
Deptcode_Exit:
Exit Sub
Deptcode_Err:
MsgBox Error$
Resume Deptcode_Exit
End Sub

This is one check!
Any advanced users reading this who could help me add a function which will take me to the first instance of this record so the user can update the original??

The error number should be 3022