validation test

[Richardson]

I have a form, frmMembership, based on a table, Membership, with 2 fields of
importance, Fn which references FirstName in the table and Ln which
references LastName in the table.
I want to be able to alert the person entering data that they may be
entering duplicate entries but I do not want to make this a 2 field key
because there are times when the first name - last name combination should
be repeated.

I would like to put a validation on the before update event of both the
first and last name fields that will compare the values to the table. If
that combination exists, I want a message box with an alert. From that
point I know the coding I need.

I used a similar test in another form, but it doesn't seem to work here.
This is the condition I have entered that doesn't work
Dlookup(True,"Membership","[FN]=Membership![FirstName] and
[LN]=Membership![LastName].

Thank you in advance for any assistance you can offer.

Lori

 

[Allen Browne]

This example:
- does nothing until both a first name and last name are entered;
- does nothing if the name is unchanged;
- looks up the ID matching the name;
- reports the ID if it finds one.
The AfterUpdate of the last name runs the same code.

Private Sub Fn_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant

If Not (IsNull(Me.Fn) OR IsNull(Me.Ln)) Then
If (Me.Fn = Me.Fn.OldValue) AND (Me.Ln = Me.Ln.OldValue) Then
'do nothing
Else
strWhere = "(Fn = """ & Me.Fn & """) AND (Ln = """ & Me.Ln &
""")"
varResult = DLookup("ID", "Membership", strWhere)
If Not IsNull(varResult) Then
MsgBox "ID " & varResult & " has the same name."
End If
End If
End If
End Sub

Private Sub Ln_AfterUpdate()
Call Sub Fn_AfterUpdate
End Sub