Search for an existing record

[Galka]

Hello
I have a small database, where names of applicants are recorded with
some other data. If an applicant telephones twice, he can be recorded
twice.

I am trying to check - by name.

When in OnBeforeInsert event of the form, the following code starts
(checked by inserting breakpoint in the code) immediately after
pressing the first key. Naturally it doesn't find anything, because
nothing is entered yet.

When in OnAfterUpdate event, it always finds, - unique name or not. Of
course, the record is already saved.

Where would you recommend to run the following code:

Dim rst As DAO.Recordset, strStudent As String, Resp
Set rst = Forms!frmStudents.RecordsetClone
With rst
strStudent = "[Student Name] = '" & Me![Student Name] & "' AND
SURNAME='" & Me![Surname] & "'"
rst.FindFirst strStudent
If rst.NoMatch Then
Else
Resp = MsgBox("There is a student with the same name. Would you
like to see?", vbYesNo, "CON Faculty waiting lists")
If Resp = vbYes Then
Call FindStudent(Me![Student Name], Me!Surname)
End If
End If
End With
Set rst = Nothing
Thank you.
Galina

[Larry Linson]

Run it in the BeforeUpdate event of the Controls in which you enter the
field that identifies the applicant -- if it takes two fields, then run it
in the BeforeUpdate for each to determine if both have been entered.

Just a caution: It appears you are using First and Last Name, and that often
is not sufficient because many people can have the same First and Last Name.
And, sometimes people use "nicknames" -- you may have the applicant already
in the DB as "John Smith", when he reapplies as "Jonathan Smith".

Even better, include a ComboBox in the FormHeader with Row Source including
all Records... as the user enters the name, it will scroll, and you can set
it to automatically populate the Form (that's part of the ComboBox Wizard).
Then, if that is not the person, you can have a CommandButton to "Add a New
Applicant".

Larry Linson
Microsoft Access MVP
"Galka" <ga****@mail.ruwrote in message
news:11********************@j27g2000cwj.googlegrou ps.com...

Hello
I have a small database, where names of applicants are recorded with
some other data. If an applicant telephones twice, he can be recorded
twice.

I am trying to check - by name.

When in OnBeforeInsert event of the form, the following code starts
(checked by inserting breakpoint in the code) immediately after
pressing the first key. Naturally it doesn't find anything, because
nothing is entered yet.

When in OnAfterUpdate event, it always finds, - unique name or not. Of
course, the record is already saved.

Where would you recommend to run the following code:

Dim rst As DAO.Recordset, strStudent As String, Resp
Set rst = Forms!frmStudents.RecordsetClone
With rst
strStudent = "[Student Name] = '" & Me![Student Name] & "' AND
SURNAME='" & Me![Surname] & "'"
rst.FindFirst strStudent
If rst.NoMatch Then
Else
Resp = MsgBox("There is a student with the same name. Would you
like to see?", vbYesNo, "CON Faculty waiting lists")
If Resp = vbYes Then
Call FindStudent(Me![Student Name], Me!Surname)
End If
End If
End With
Set rst = Nothing
Thank you.
Galina