Prevent Duplicate Values

C

Chrysos

I know how to set a field in a table to prevent duplicate values (primary key
or index), and understand this setting transfers to a form using the field.
However, I don't like the horribly technical message when the rule is
violated (my users are very non-technical) and also don't like the fact that
the message is displayed on exiting the record, not the field. How can I
change this? The values in this field (Membership Number) are not
necessarily entered in numerical order and have gaps (Members leave or die!).
Is there a validation rule I can use instead?

Any help very welcome.
 
A

Allen Browne

You can use the AfterUpdate event procedure of the control to DLookup your
table to see if another record has the same value.

This kind of thing:

Private Sub MembershipNumber_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
With Me.[MembershipNumber]
If IsNull(.Value) OR (.Value = .OldValue) Then
'do nothing.
Else
strWhere = "[MembershipNumber] = " & .Value
varResult = DLookup("MembershipNumber", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Dupe"
End If
End If
End With
End Sub
 
Top