> 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)" <kthsneisllis9[ at ]ncoomcastt.renaetl> wrote in message
> news:ulBti7nRJHA.4824[ at ]TK2MSFTNGP02.phx.gbl...
>> 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.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
http://www.accessmvp.com/KDSnell/>>
>>
>> "Dale" <dale[ at ]nowhere.com> wrote in message
>> news:OQqptsnRJHA.5348[ at ]TK2MSFTNGP02.phx.gbl...
>>> 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
>>>
>>
>>
>