When entering data through a form into a table how to check for dups and create custom message box that would display error message? The field in underlying table does not allow duplicates. Access displays error mes. that new value would create duplicates in value and indexes and that record can not be saved. But how I can customaze that?
You can use the BeforeUpdate event to check for duplicate values.
Let's assume the following:
Table name is 'tblData'.
Field name in table is 'Name'.
Form name is 'frmShow'.
Field name in form is 'txtName'.
Create the following event code:
Private Sub txtName_BeforeUpdate(Cancel As Integer)
If (DLookup("Name", "tblData", "Name =" & Forms!frmShow!txtName)) Then
MsgBox "Duplicate value in field txtName!"
The DLookup function looks for a duplicate value. It compares the form field value with the corresponding table field values. If the check is true you can send a message. The Undo method resets all changes in the current form.
I tried your code, but gives me error message: err.#3464, "Data type mismatch in expression"
Here is my code:
Private Sub txtBargUnitNum_BeforeUpdate(Cancel As Integer)
If (DLookup("BargUnitNum", "tblBargainingUnit", "BargUnitNum = " & Forms!frmAddEditBargUnit!txtBargUnitNum)) Then
(If...then - one line in code)
MsgBox "Duplicate value in Bargaining Unit Number!"
BargUnitNum is a text type field restricted to 3 chars.
Could you give a clue what causes the error?...
(If...then - one line in code) frmAddEditBargUnit!