been entered, and takes the user to that record if it is found -- as
follows:
Private Sub Contact_telephone___BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone
rs.FindFirst "[phone] = '" & Me![Contact Telephone #] & "'"
If Not rs.NoMatch Then
iAns = MsgBox("Phone " & Me![Contact Telephone #] & " already
exists;" _
& vbCrLf & "Click OK to go to that record, Cancel to start over",
_
vbOKCancel)
Cancel = True
If iAns = vbOK Then
Me.Undo
Me.Bookmark = rs.Bookmark
Else
Me.Undo
End If
Else
' New Customer -- Just let it be added
End If
End Sub
-- This works as designed when you enter a number that is already in
the database.
The problem is when the customer calls and opens a record with a
different telephone number. What I am trying to do to deal with this
possibility is use the same code (above) for the first name and the
last name -- to at least alert the user that there is a possibility
that the name may already be entered.
The code I am using is similar to the above as follows:
Private Sub LName_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone
rs.FindFirst "[FName]&[LName] = '" & Me![FName] & Me![LName] & "'"
If Not rs.NoMatch Then
iAns = MsgBox("Last Name " & Me![LName] & " already exists;" _
& vbCrLf & "Click OK to go to that record, Cancel to Continue", _
vbOKCancel)
Cancel = True
If iAns = vbOK Then
Me.Undo
Me.Bookmark = rs.Bookmark
Else
' <<<<<<<<<<<<<<<<<<<< Where problem is
>>>>>>>>>>>>>>>>>>>
End If
Else
' New Customer -- Just let it be added
End If
End Sub
So -- From the top, what happens:
1. Customer, John Smith with telephone 123-456-7890 -- it is entered.
2. Same customer, John Smith calls again with telephone 234-123-4567.
3. Above routine finds John Smith and indicates that the name may be
in the database. User has option to go to that record (OK), or
(Cancel to Continue), Here is the problem: As it is, when you select
Cancel, it stays in the Lastname field and when you try to advance to
the next field, it triggers the BeforeUpdate event again, etc. What
is the best way to set the environment up so that it is the way it was
before the BeforeUpdate event was triggered -- so that the user can
continue to input the record.
Thanks for any help with this.
Robbie Bollinger
What happens is when the first name and the last name is entered.