I see a couple of problems.
1) In the line where you assign the Surname as criteria, you are using
apostrophes (single quotes) as the text delimiter. This will work until you
run across a name that has an apostrophe in it, such as O'Hare. If you
double up the double quotes, that'll tell VBA that you want the double quote
as part of the string instead of as a delimiter. Replace each apostrophe
with 2 double quotes and that'll take care of the problem. You're probably
safe on the first name, but you could do the same thing there also if you
want to.
Example:
strStudent = "[Student Name] = '" & Me![Student Name] & "' AND SURNAME=""" &
Me![Surname] & """"
2) In your search, you're searching for FindFirst. This will give you the
first match found. The new entry has already been saved to the table, so is
available to be found. In fact, since you're making the original check in
the form's AfterUpdate event, I would suspect you'll always find a match.
The AfterUpdate event fires after the record has been saved, so when doing a
FindFirst, you'll always find at least the record you just entered.
This would normally be handled in the form's BeforeUpdate event. It would be
used to allow you to abort the current record and go to the other one. Even
better, you may want to make this check as soon as both the first name and
last name textboxes are filled in. This would save the user from filling in
a whole record, just to find that one already exists. Advise the user that
if they fill in the record by filling in the first and last names first,
that they may not have to complete the record if one already exists. Most
folks, not wanting to waste their time, will make sure they fill in the name
fields first. These two fields should also be 0 and 1 in your tab order. If
the users chooses to fill in the fields in a different order, the check will
still happen when these two fields are completed, they've just wasted their
time filling in the other fields if it wasn't needed.
--
Wayne Morgan
MS Access MVP
"Galka" <ga****@mail.ru> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hello
I have a form to enter names and some other personal information.
When a name is entered, it is checked against existing records: maybe,
such name was entered before? If yes, user is notified and asked, would
you like to see the previous record? If user answers yes, form on the
screen is scrolled to the record with the same name. All works well,
apart from number of record between navigation buttons: it is still the
same number of the last added record! This is very misleading. I need
the correct number of record.
Interesting that despite being shown as for example "30 of 30", if to
click Go To Last button, the form returns to the last record and
navigation buttons still show "30 of 30", in this case correctly.
A code, which processes the search is:
Private Sub Form_AfterUpdate()
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
End Sub
Public Sub FindStudent(StudentName As String, Surname As String,
Optional Age As Integer)
Dim rst As DAO.Recordset, strStudent As String, F As Form
Set F = Forms("frmStudents")
Set rst = F.RecordsetClone
With rst
rst.MoveFirst
strStudent = "[Student Name] = '" & StudentName & "' AND SURNAME='" &
Surname & "'"
If Not IsMissing(Age) And Age <> 0 Then strStudent = strStudent & " AND
AGE=" & Age
rst.FindFirst strStudent
If rst.NoMatch Then
MsgBox "There is no such student.", vbOKOnly, "CON Faculty waiting
lists"
Else
F.Bookmark = rst.Bookmark
End If
'End If
End With
Set rst = Nothing
End Sub
Any help will be greatly appreciated.
Galka
