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
- 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
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