duplicate check for multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two fields on a form, First Name and Last Name. When the user clicks
ADD NEW, I want his data entered to be checked against existing data. If BOTH
fields match, he gets an error. Is there a clean way to do this using the

DoCmd.GoToRec,,,acNewRec

command? What would I need to use in conjunction with that command in order
to do what I want?

Thanks!
 
You could do this at the table level by building a compound index and
setting it to "Unique".

Check the help files for how to create a compound index in a table.

Rick B
 
Something like ...

Private Sub cmdTest_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim boolFound As Boolean
Dim strSQL As String

'Note the additional test on the 'TestID' field in the example below.
Without this, you'll
'get false positives when a user attempts to save changes to an existing
record.
strSQL = "SELECT Count(*) AS TheCount FROM tblTest WHERE [First Name] =
'" & _
Replace(Me![First Name] & vbNullString, "'", "''") & "' " & _
"AND [Last Name] = '" & Replace(Me![Last Name] & _
vbNullString, "'", "''") & "' AND TestID <> " & Me!TestID

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
boolFound = rst.Fields("TheCount") <> 0
rst.Close

If boolFound Then
MsgBox "There's an existing record with this first and last name."
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If

End Sub

Note, though, that there is the very real possibility that two records with
the same first and last name may not be an error at all. Take a look at the
number of John Smith's in a UK or US phone book, or the number of Joseph
Murphy's in an Irish one, some time.
 
This is probably not the only way to do it, but I would use a SQL statement
with a separate recordset to check for the existence of the data and use the
recordcount to determine whether to add the new record.

e.g.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from TheTable where TheField = '"
& Me.txtName & "'")

If Not (rs.BOF And rs.EOF) Then
' Insert the data (with add record or Append SQL statement)
End If
....
 
Correction:
This is probably not the only way to do it, but I would use a SQL statement
with a separate recordset to check for the existence of the data and use the
recordcount to determine whether to add the new record.

e.g.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from TheTable where TheField = '"
& Me.txtName & "'")

If (rs.BOF And rs.EOF) Then ' Ensure the recordset is empty
' Insert the data (with add record or Append SQL statement)
End If
....
 
Back
Top