We want to make sure the user enters a unique value for a field. It brings up the default error message, but we would like to customize it.

To the best of my knowledge, if you declare a field to require uniqueness, you lose the ability to say what needs to be said UNLESS...

The form doesn't actually enter data to the record from the control in which the tentative value has been entered. In that case, you can put a "before update" routine that does something like this:

Suppose the control is [Text1] and is unbound. The actual field is [UniText] in table MyTable. On the form, you have an invisible text box called [UniText] that is the REAL value to be stored with the rest of the record.

Here is how I would approach it.


Private Sub Text1_BeforeUpdate(Cancel As Integer)

Dim loCounter As Long
Dim stCriterion as String

stCriterion = "[UniText] = """ & [Text1] & """"

loCounter = DCount( "[UniText]", "MyTable", stCriterion )

if loCounter = 0 then

Cancel = 0

[UniText] = [Text1]

else

msgbox "Another entry in MyTable has the indicated value", vbOKOnly, "Duplicate Value"

Cancel = -1

end if