Trapping the 'no duplicate values in a table' error

[John S. Ford, MD]

I have a TextBox on a form (frmPatientEntry), that allows users to enter a
value for field MedicalRecordNumber of a new patient. The form's underlying
table is set to not allow duplicate medical record numbers (only one patient
can have a given medical record number). If a user tries to enter such a
duplicate, Access 2000 fires the following message:

"The changes you requested to the table were not successful because they
would create duplicate values in the index...etc."

This is the error I wish to trigger but I want to write my own error
handler. But no error number is reported so I don't know how to trap this
particular error.

Can anyone help with this?

John

 

[Dirk Goldgar]

I have a TextBox on a form (frmPatientEntry), that allows users to enter a
value for field MedicalRecordNumber of a new patient. The form's
underlying table is set to not allow duplicate medical record numbers (only
one patient can have a given medical record number). If a user tries to
enter such a duplicate, Access 2000 fires the following message:

"The changes you requested to the table were not successful because they
would create duplicate values in the index...etc."

This is the error I wish to trigger but I want to write my own error
handler. But no error number is reported so I don't know how to trap this
particular error.

Can anyone help with this?

You can approach this either of two ways.

1. You can check (via DLookup or your own recordset) in the form's
BeforeUpdate event to see if there is already a record (aside from the
current one) for the same MRN. If there is, you can display your message
and set the Form_BeforeUpdate procedure's Cancel argument to True to cancel
the update.

2. You can trap the error -- error 3022 -- in the form's Error event. The
Form_Error event procedure would look something like this, assuming this is
the only error you want to trap:

'----- start of example code -----
Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then

MsgBox _
"This MRN already exists for another patient. " & _
"Please correct the MRN before attempting to save " & _
"this record.", _
vbExclamation, _
"Duplicate Medical Record Number"

Response = acDataErrContinue

End If

End Sub
'----- end of example code -----

Note that the above code assumes that the MRN is the only field in the table
that will be giving this particular error.

 

[John S. Ford, MD]

Thanks Dirk,

Solution 2 was exactly what I was looking for.

John