FindFirst with AND not working on single record

[JHite]

I'm using Access 2003 with MS XP.

Have table/records with FirstName (text field), LastName (text field), plus other fields. User enters FirstName, LastName and other info in form. Before updating table based on form input, I check to see if a record already exists for a person with that FirstName and LastName. If so, I give an error message and don't let the record be created. Pertinent part of form's BeforeUpdate code is as follows:

Private Sub Form_BeforeUpdate (Cancel as Integer)
Dim FN As String
Dim LN As String
Dim rsc As DAO.Recordset
Set rsc=Me.RecordsetClone
FN=Me.FirstName
LN=Me.LastName
rsc.FindFirst "[LastName] = 'LN' And [FirstName] = 'FN'"
If Not rsc.EOF Then
  Me.Undo
  MsgBox "This person has already been entered."
  rsc.Close
  Set rsc=Nothing
  Exit Sub
End If
End Sub

Table contains following records (only FirstName and LastName shown)

Jane Doe
John Smith
Sally White
Sam Johnson

case 1) If user enters Frank Jones, code works (i.e., allows record to be created).

case 2) If user enters Sally White, code works (i.e., gives error "This person has already been entered." and doesn't create record.

case 3) If user enters Sam Doe, code doesn't work (i.e., gives error "This person has already been entered." and doesn't create record.

In case 3) the code apparently finds Sam as FirstName in one record and Doe as LastName in a different record and decides it meets the criteria.

How do I write the code so FirstName and LastName must match in the same record before meeting the criteria (and generating the error message)?

Thanks for the help.

Janice

[missinglinq]

Try replacing

rsc.FindFirst "[LastName] = 'LN' And [FirstName] = 'FN'"

with

rsc.FindFirst "[LastName]= '" & Me.LastName & "' And [FirstName] = '" & Me.FirstName & "'"

Linq ;0)>

[JHite]

Ok. I tried your suggestion, but got the very same result as with my code. Case 3) still doesn't work correctly.

Are there any more suggestions?

Is there a better way to find duplication between records than using FindFirst?

Thanks again.

Janice

[ajalwaysus]

I may be over simplifying this but try this..

Private Sub Form_BeforeUpdate (Cancel as Integer)
 
  if nz(DLookUp("FirstName", "Table1", "FirstName = '" & me.FirstName & "' AND LastName = '" & me.LastName.value & "'"), "") <> "" Then
       MsgBox "This person has already been entered."
       Cancel = True
  End If
 
End Sub
 

This is a quick and dirty way, I will post another idea when I have more time.

I also must express my concern of using First name Last Name as a unique identifier, plenty of people have the same first and last name.

-AJ

[Megalog]

The reason the code isnt working is because you're not testing for the find results, you're simply looking to see if you're at EOF. Nothing is causing the recordset to advance, so you end up staying on the first record.

Try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim FN As String
    Dim LN As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
 
    FN = Nz(Me.FirstName, "Null")
    LN = Nz(Me.LastName, "Null")
 
    rsc.FindFirst "[LastName]= '" & Me.LastName & "' And [FirstName] = '" & Me.FirstName & "'" 
 
    If Not rsc.NoMatch Then
        Me.Undo
        MsgBox "This person has already been entered."
    End If
 
    Set rsc = Nothing
End Sub

[Megalog]

@Megalog
Sorry, line 10 above should be:

rsc.FindFirst "[LastName]= '" & LN & "' And [FirstName] = '" & FN & "'" 

[JHite]

Thanks to all. I used Megalog's (corrected) code and it worked. Thanks for explaining about the EOF check. I didn't think of that in relationship to FindFirst.

In response to AJ's comment, I realize two different people can have the same first and last name. This is a small DB for use at my church and I will include instructions on how to delineate two people with the same first and last names (e.g., adding a middle initial to one of the first names or using a nickname for one of the first names).

Thanks again to all.

Janice