Not allowing duplicates

[kateri4482]

I have two fields (FirstName and LastName) and after the user enters
information in those two fields, I want them to be alerted if that name
already exists. I am assuming I use the AfterUpdate control on the form and
DLookup, but I am not sure how to write the VB code so that it looks at both
fields. Any help is appreciated!

 

[tina]

something like

If Not IsNull(Me!FirstName) And Not IsNull(Me!LastName) Then
If DCount(1,"MyTableName","FirstName = '" _
& Me!FirstName & "' And LastName = '" _
& Me!LastName & "'") > 0 Then
MsgBox "This name is already in the table."
End If
End If

replace MyTableName with the correct name of the table, of course. i started
by checking to see if both fields have a value before checking for duplicate
names. if you don't want/need to do that, then just leave off the first line
and last line of the code above.

hth

 

[kateri4482]

Thank you! That worked pretty well - except that after the message appears
and I click the OK box, the form goes to the next record (not a problem in
itself), but when I clicked to the previous record in which I tried to enter
a duplicate name, the duplicate name was there! So I do want the message to
appear, but I don't want the table to accept that duplicate record. Does
that make sense?

 

[kateri4482]

Thanks John. That makes sense, but when I did that, no message box appeared
and the duplicate record was accepted without question. Is there something
wrong with my VB statement?

 

[John W. Vinson]

Thanks John. That makes sense, but when I did that, no message box appeared
and the duplicate record was accepted without question. Is there something
wrong with my VB statement?

Since you didn't post the VBA statement you actually used... I don't know. Did
you set the Cancel argument to true?

Please post the entire BeforeUpdate subroutine.

 

[kateri4482]

I used the statement that was provided in the first reply to my question.
Here it is:

If Not IsNull(Me!FirstName) And Not IsNull(Me!LastName) Then
If DCount(1, "TblEnhancedCodes", "FirstName = '" & Me!FirstName & "' And
LastName = '" & Me!LastName & "'") > 0 Then
MsgBox "This name already exists."
End If
End If

I don't know where the "Cancel" argument would go. Thanks for assisting.

 

[tina]

your first post said you wanted to alert the user, not anything about
stopping the update, and you referred to the AfterUpdate event, so i gave
you code that would work in that event procedure; there is no way to Cancel
an AfterUpdate event - at either the form or control level.

as John said, if you want to give the user the option to cancel the update,
then use the form's BeforeUpdate event procedure, as

If Not IsNull(Me!FirstName) And Not IsNull(Me!LastName) Then
If DCount(1,"MyTableName","FirstName = '" _
& Me!FirstName & "' And LastName = '" _
& Me!LastName & "'") > 0 Then
If MsgBox("This name is already in the table. " _
& "Do you want to add it again anyway?", _
vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
End If
End If
End If

the messagebox is set up with the No button as the default, so if the user
presses Enter without reading the message (as users are wont to do) then the
update is cancelled - in other words, nothing changes. if you want the Yes
button to be the default, then just remove the + vbDefaultButton2 bit from
the code.

and again: i started by checking to see if both fields have a value before
checking for duplicate names. if you don't want/need to do that, then just
leave off the first line and last line of the code above.

hth

 

[kateri4482]

You are correct. I have to remember to be more explicit when I am asking for
assistance. And your most recent reply is exactly what I was looking for -
and worked like a charm - the first time. And never worked again. Sigh.
This gets frustrating. Thanks so much.

 

[kateri4482]

Never mind. It IS working. For some reason, I noticed that all of the first
names have an extra space before the name - which is why duplicates would not
be recognized. Duh. Working great now. Thanks!

You are correct. I have to remember to be more explicit when I am asking for
assistance. And your most recent reply is exactly what I was looking for -
and worked like a charm - the first time. And never worked again. Sigh.
This gets frustrating. Thanks so much.

 

[tina]

you're welcome

Never mind. It IS working. For some reason, I noticed that all of the first
names have an extra space before the name - which is why duplicates would not
be recognized. Duh. Working great now. Thanks!