Many of us dislike the system warnings for this kind of thing and 'roll our own,' using DCount():
Replacing Field1 with the actual name of your Field/Control, and Domain with the name of the Table or Query that holds the Field:
If Field1 is Text
Code:
Private Sub Field1_BeforeUpdate(Cancel As Integer)
If DCount("Field1", "Domain", "[Field1] = '" & Me.Field1 & "'") > 0 Then
Resp = MsgBox("This Value Already Exists! Please Enter New Value")
Cancel = True
End If
End Sub
If Field1 is Numeric
Code:
Private Sub Field1_BeforeUpdate(Cancel As Integer)
If DCount("Field1", "Domain", "Field1 = " & Me.Field1) > 0 Then
Resp = MsgBox("This Value Already Exists! Please Enter New Value")
Cancel = True
End If
End Sub
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007