Creating an error message while preventing the duplication of data in tables

  • Thread starter Thread starter jdtivoli
  • Start date Start date
J

jdtivoli

I have a table which contains fields representing the
serial numbers of equipment.

I have created the table and set the serial number field
to be indexed "Yes(No Duplicates)". And this works fine
for preventing duplicate data from being entered.

I would like to have an error message pop up telling the
user that an attempt was made to input data which already
exists in the table.

How can I do this?

Jesse
 
In the form's Error event you can trap this error and popup a message box.

Example:
If DataErr = 3022 Then
Msgbox "You've entered a duplicate serial number!", vbOkOnly +
vbInformation
Response = acDataErrContinue
Me.txtSerialNumber.Undo
End If

You could also try to catch this when they make the entry instead of waiting
until trying to save the new record. To do this, in the BeforeUpdate event
of the serial number control:

If DCount("SerialNumberField", "TableName", "SerialNumberField=" &
Me.txtSerialNumber.Text) > 0 Then
Msgbox "You've entered a duplicate serial number!", vbOkOnly +
vbInformation
Cancel = True
End If

If the serial number is a text field, you'd need to concatenate in some
quotes also:
"SerialNumberField='" & Me.txtSerialNumber.Text & "'"
 
Back
Top