search or match

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am trying to find a match for what the user is typing in a bound textbox
on a form, then if there is a match, stop and prompt a message and erase
their entry in the textbox
What would be the simplest way to do this
table is "vehinfo"
form is "vehinfo"
 
I test to see if a name exists in the Before Update event, but you can
modify it to suit your situation.

Dim strFirst As String
Dim strLast As String
Dim lngContactID As Long
Dim strSQL As String
Dim strCriteria As String
Dim strMsg As String
Dim rs As DAO.Recordset

strFirst = Trim(Me.txtFirstName)
strLast = Trim(Me.txtLastName)
lngContactID = Me.Contact_ID

strSQL = "Select Contacts.[Contact ID], Contacts.[Last Name],
Contacts.[First Name] " & _
"FROM Contacts"

strCriteria = "([First Name] = '" & strFirst & "' AND " & _
"[Last Name] = '" & strLast & "' AND [Contact ID] <> " & lngContactID
& ")"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rs.FindFirst strCriteria

If Not rs.NoMatch Then
strMsg = strFirst & " " & strLast & " is already in the database." _
& vbCrLf & "Are you sure you want to add this name again?"

If MsgBox(strMsg, vbYesNo, "Duplicate Name") = vbNo Then
Cancel = True
Me.txtLastName.SetFocus
Else
'Additional validation, if any, goes here.
End If
End If

rs.Close
Set rs = Nothing

Max
 
Back
Top