Duplicate records

[Manton]

Access 2000.
I have a table called tbl_main
There are several fields in this table - two of which are called "Staff
Name" and
"Account Number".
I created a form (frm_data) which is linked to the table tbl_main.
In this form - the user would first type in the "Staff Name" (combo box
format) and then tab/enter to the next field --> account number.
Question: I would like to be able to enter the staff name and then a number
in the "Account Number" field and then i press ENTER to go to the next
field - But if that very account number already exists for the same "staff
name" (chosen earlier), I want to be able to create a message/dialogue box
to appear - indicating that there currently exists the very same account
number FOR that "staff name" and not allow the user to continue with any
data entry.
thanks for the help.

 

[Naresh Nichani MVP]

Hi :

You could add a Unique Index on Staff Name and Account Number. In Table
Design click View | Indexes. Then add the two field to a Index and name the
Index and set the Unique property to True.

When user tried to update record with a duplicate Staff Name and Account
Number it would not allow that.

You can trap this error message at the Form level in the Form_Error event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "Duplicate Account Number for staff"
MsgBox strMsg
End If
End Sub

Regards,

Naresh Nichani
Microsoft Access MVP