By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

FindFirst with AND not working on single record

P: 18
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate (Cancel as Integer)
  2. Dim FN As String
  3. Dim LN As String
  4. Dim rsc As DAO.Recordset
  5. Set rsc=Me.RecordsetClone
  6. FN=Me.FirstName
  7. LN=Me.LastName
  8. rsc.FindFirst "[LastName] = 'LN' And [FirstName] = 'FN'"
  9. If Not rsc.EOF Then
  10.   Me.Undo
  11.   MsgBox "This person has already been entered."
  12.   rsc.Close
  13.   Set rsc=Nothing
  14.   Exit Sub
  15. End If
  16. 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
Sep 17 '09 #1

✓ answered by Megalog

@Megalog
Sorry, line 10 above should be:

Expand|Select|Wrap|Line Numbers
  1. rsc.FindFirst "[LastName]= '" & LN & "' And [FirstName] = '" & FN & "'" 

Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
Try replacing

Expand|Select|Wrap|Line Numbers
  1. rsc.FindFirst "[LastName] = 'LN' And [FirstName] = 'FN'"
with
Expand|Select|Wrap|Line Numbers
  1. rsc.FindFirst "[LastName]= '" & Me.LastName & "' And [FirstName] = '" & Me.FirstName & "'"
Linq ;0)>
Sep 17 '09 #2

P: 18
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
Sep 17 '09 #3

Expert 100+
P: 266
I may be over simplifying this but try this..

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate (Cancel as Integer)
  2.  
  3.   if nz(DLookUp("FirstName", "Table1", "FirstName = '" & me.FirstName & "' AND LastName = '" & me.LastName.value & "'"), "") <> "" Then
  4.        MsgBox "This person has already been entered."
  5.        Cancel = True
  6.   End If
  7.  
  8. End Sub
  9.  
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
Sep 17 '09 #4

Megalog
Expert 100+
P: 378
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim FN As String
  3.     Dim LN As String
  4.     Dim rsc As DAO.Recordset
  5.     Set rsc = Me.RecordsetClone
  6.  
  7.     FN = Nz(Me.FirstName, "Null")
  8.     LN = Nz(Me.LastName, "Null")
  9.  
  10.     rsc.FindFirst "[LastName]= '" & Me.LastName & "' And [FirstName] = '" & Me.FirstName & "'" 
  11.  
  12.     If Not rsc.NoMatch Then
  13.         Me.Undo
  14.         MsgBox "This person has already been entered."
  15.     End If
  16.  
  17.     Set rsc = Nothing
  18. End Sub
Sep 17 '09 #5

Megalog
Expert 100+
P: 378
@Megalog
Sorry, line 10 above should be:

Expand|Select|Wrap|Line Numbers
  1. rsc.FindFirst "[LastName]= '" & LN & "' And [FirstName] = '" & FN & "'" 
Sep 17 '09 #6

P: 18
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
Sep 17 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.