Before Update event

[Dale]

Hello
I have a form that checks for duplicate PPHN (Patient Personal Health
Number) on the before update event of the patient registration form. If a
duplicate is found, the user is presented with a msgbox giving 3 options:
Yes, No, Cancel. I would like to automatically populate the PPHN with a
random number (function call) when the user selects "No" but I'm hit with a
runtime error 2115. What I don't get is if I comment out the function call,
I can run the same function from a cmd button on the form without the
runtime error occurring. It seems to me I've duplicated the user actions in
code, so why the runtime error?

My guess...the code fails when the function tries to write the new value to
the PPHN field, even though I've prefaced the function call with the undo
action, I'm still seeing a value stored in the ?? form recordset.

Here is my beforeupdate code

Private Sub PPHN_BeforeUpdate(Cancel As Integer)
Dim db As Database, rst As Recordset
Dim Response
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblPPatient")

rst.Index = "PrimaryKey"
rst.Seek "=", Forms!frmpatientreg.PPHN.Value

If rst.NoMatch Then
Exit Sub
Else
Response = MsgBox("This PHN has already been entered." _
& vbCrLf & "Patient Name ='" & rst!PLName & ", " & rst!PFName &
"'" _
& vbCrLf & "DOB='" & rst!DOB & "'" _
& vbCrLf & "Phone='" & rst!Phone & "'" _
& vbCrLf & "Physcian='" & rst!PPhyscian & "'" _
& vbCrLf _
& vbCrLf & "If this is a NEW patient and the message above is "
_
& "displayed. Do one of the following: " _
& vbCrLf & "Click Yes to re-enter PHN, please validate your
entry." _
& vbCrLf & "Click No to generate a Random PHN." _
& vbCrLf & "Click Cancel to undo changes and close the form.",
vbYesNoCancel, "Duplicate PHN Found")
End If

Select Case Response

Case vbYes
Cancel = True
Me.PPHN.SelStart = 0
Me.PPHN.SelLength = Len(Me.PPHN)

Case vbNo
Cancel = True
Me.PPHN.Undo
'Call RandomPHN

Case vbCancel
Cancel = True
Me.PPHN.Undo
RunCommand acCmdClose
End Select

rst.Close ' Close the recordset.
Debug.Print Me.PPHN.Value

End Sub

 

[Ken Snell \(MVP\)]

You cannot assign a value to a control during that control's BeforeUpdate
event. That is the reason for the error message.

You'll have to use a different approach if you want to change the value in
that control. What I would do is to use the Form's BeforeUpdate event to run
your code. That event willl let you change the value of any of the controls
before the record is updated/saved.

 

[Dale]

Gee..yes that works better but I wanted the notice to the user to happen
more in keeping with exiting the field. There is a field at the end of the
form that fires a rule on exit as well so I have these two events competing
with each other...never dull!!

 

[Ken Snell \(MVP\)]

You could use the Exit event to run the code, so long as you don't cancel
that event and make it impossible for the user to leave the field at all.