Example Forms ~ Newby Question Access 2000

  • Thread starter Thread starter R Bolling
  • Start date Start date
I've got it working about 90% of the way that was intended, and use it
for the PK (phone field) - NOT the SSN field which seemed to cause a
lot of discussion -- but I am also trying to use it in another way in
order to catch those situations where a person calls again with a
different phone number.

So in general,

- John Doe calls the first time and his information is populated:
Tel: 123-456-7890 FName: John LName: Doe
The code works excellent in this straightforward manner when you put
in the same telephone number again.

But in the situation where Mr. Doe calls back with a different number:

Tel: 123-567-8901 FName: John LName: Doe

This time I only want to bring it to the users attention that John Doe
may already be in the database.

What is happening when I apply this code to the BeforeUpdate of LName:

Because FName LName, were previously entered, I now get the
expacted message: Name John Doe already exists
Click OK to go to that record, or Cancel to Continue.

It works find as before when I select OK – but if I know that this is
a different John Doe, and select Cancel, what I want it to do is go to
the next field and continue to input the data (like it was doing
before it got to the BeforeUpdate Event of LName.)

Is there an easy way to do this?

What I have is your code slightly modified 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("Name " & Me![FName] & 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
‘ User selects Cancel <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Here's
where the user decides that this is actually a different John Doe
‘ and wants the input to continue as if the BeforeUpdate never
happened.

End If
Else
' New Customer -- Just let it be added
End If
End Sub

Thanks for any help with this. I hope my explanation was thorough
enough!

Robbie Bollinger
 
Back
Top