search in form

[Joseph Frazer]

I have a form that has, among other things, an ID number, first name,
and last name. My problem is that when I enter a name (both parts) in
the form, it creates a new ID even if the person is in the table
already. Is there a way to set the ID to the persons name if they are
in the table, and maybe have a message box box if they aren't? I tried
using : txtID="SELECT ID FROM myTable WHERE ((fName = [firstName]) AND
(lname = [lastName]);"

but that did not work. Any help would be appreciated. Thanks in advance.

Joe

 

[Neil]

Hello Joseph,

You could use the BeforeUpdate event of the form to check if the person
already exists and then cancel the update if this is the case. Here is some
example code (using DAO - must have a reference to the DAO 3.6 object
Library) to check for something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rsTemp As DAO.Recordset
Dim intResponse As Integer
' Clone the recordset
Set rsTemp = Me.RecordsetClone
' Search to see if the person already exists
rsTemp.FindFirst "FirstName = '" & Me.txtFirstName & "' And LastName =
'" & Me.txtLastName & "'"
' Check the NoMatch property
If rsTemp.NoMatch Then
' Do nothing, no existing data exist's
Else
' Inform the user that this person already exists
intResponse = MsgBox("A person named '" & Me.txtFirstName & " " &
Me.txtLastName & "' already exist's. Do you want to add them to the database
again?", vbYesNo+vbInformation,"Person already exist's"
' Check the response
If intResponse = vbYes
' The user would like to add this person to the database - do
nothing
Else
' Cancel the event
Cancel = True
' Undo any changes made
Me.Undo
End If
End If
' Clean up
rsTemp.Close
Set rsTemp = Nothing

End If

HTH,

Neil.