Prevent Duplicate Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top