Check text field value against underlying table of a form

[antcraw]

Hello,
I have a form on which the user can enter new records or update
existing ones.
If the user enters a new record on the form, I would like to have an
'After Update' event on one of the text fields, which is the item# to
check if the value entered already exists in the underlying table,
tbl_item. How can I call the table from the 'After Update' event of
the text field and check for that value in the table?

Thanks in advance.

Regards,
A. Crawford

 

[Allen Browne]

To see if the form is at a new record, test the NewRecord property of the
form, e.g.:
If Not Me.NewRecord Then ...

To see if the value is already in the table, use DLookup(). Details and
examples in:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

If the user is editing an existing record, it might be a good idea to lookup
the value anyway, but only if the user did not change the field back to the
same value that was already there. The OldValue property gives you that, so
the code would be something like this:

Dim strWhere As String
Dim varResult As Variant
With Me.[Item#]
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
strWhere = "[item#] = " & .Value
varResult = DLookup("[ID]", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate"
End If
End If
End With