Use the After Update event procedure of the control to DLookup() the table
and see if there is another record with the same value.
Private Sub Text1_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
With Me.Text1
If (.Value = .OldValue) Or IsNull(.Value) Then
'do nothing
ElseIf
strWhere = "[Field1] = " & .Value
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate of ID " & varResult
End If
End If
End With
End Sub
Notes:
1. Replace "Text1" with the name of this text box, and "Field1" with the
name of this field. (These 2 things are often the same name.)
2. Replace "Table1" with the name of your table, and "ID" with the name of
the primary key field in this table.
3. If Field1 is a Text type field, you need extra quotes:
strWhere = "[Field1] = """ & .Value & """"
4. For more help with DLookup(), see:
http://allenbrowne.com/casu-07.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Bob H said:
I have a form for user input that is bound to table. One field in the table
is set to not allow duplicates. Is it possible to check to see if user
input
in this field on the form is a duplicate immediately after it is entered
rather than at the time the entire form data is attempted to be saved?
