Cancel a change in the before update event of a txtbox

[Bill R via AccessMonster.com]

I'm trying to cancel a prohibited deletion of an existing date in a textbox
in a record on a continuous subform. What I want to happen is, when the
resulting change to the data is Null, display a message stating it is
prohibited, and revert to the value that was there before the user deleted it.
I run the following code in the before update event of the finish date txtbox:


If IsNull(Me.ActiveControl) Then
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If Not (rs.EOF And rs.BOF) Then
MsgBox strMsg, vbOKOnly + vbExclamation, strTitle
Me.ActiveControl.Undo
Cancel = True
Me.Dirty = False
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If

If there are no records in the underlying data that have an open finish date,
then rs will return no records and the null will be permitted.
The msg box displays properly, but when I hit OK, the box is still null. How
do I make this work?

Bill

 

[Allen Browne]

It might seem counter-intuitive, but use the AfterUpdate event of the
control, and change it back to the value that was there:

With Me.ActiveControl
If IsNull(.Value) And Not IsNull(.OldValue) Then
Set rs = dbEngine(0)(0).OpenRecordset(strSlq)
If rs.RecordCount = 0 Then
MsgBox strMsg
.Value = .OldValue
End If
rs.Close
End If
End With

 

[Bill R via AccessMonster.com]

Thanks Allen.

I will move the code to the afterupdate events.

It's more than counterintuitive. I have the following code in the
beforeupdate event of an option group, and it works just fine:

If MsgBox("You already have a set of dates for this location." & vbCrLf &
_
"Do you want to create an additional set of dates here?", _
vbCritical + vbYesNo + vbDefaultButton2, _
"Duplicate Location") = vbNo Then
Cancel = True
Me.Undo
End If

If he says no, the original value remains. If he says yes, the new value
replaces it and the afterupdate event code runs.

Very odd.

Bill

It might seem counter-intuitive, but use the AfterUpdate event of the
control, and change it back to the value that was there:

With Me.ActiveControl
If IsNull(.Value) And Not IsNull(.OldValue) Then
Set rs = dbEngine(0)(0).OpenRecordset(strSlq)
If rs.RecordCount = 0 Then
MsgBox strMsg
.Value = .OldValue
End If
rs.Close
End If
End With

I'm trying to cancel a prohibited deletion of an existing date in a
textbox

[quoted text clipped - 25 lines]

How
do I make this work?

 

[Allen Browne]

Bill, if you do go with the AfterUpdate event, you can get away with undoing
the whole form, or reassigning the OldValue to the control, but I should
warn that you cannot undo a control in its AfterUpate event.

Again for some odd reason, that fails silently, i.e. the code runs, but the
control is not undone.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.

I will move the code to the afterupdate events.

It's more than counterintuitive. I have the following code in the
beforeupdate event of an option group, and it works just fine:

If MsgBox("You already have a set of dates for this location." & vbCrLf
&
_
"Do you want to create an additional set of dates here?", _
vbCritical + vbYesNo + vbDefaultButton2, _
"Duplicate Location") = vbNo Then
Cancel = True
Me.Undo
End If

If he says no, the original value remains. If he says yes, the new value
replaces it and the afterupdate event code runs.

Very odd.

Bill

It might seem counter-intuitive, but use the AfterUpdate event of the
control, and change it back to the value that was there:

With Me.ActiveControl
If IsNull(.Value) And Not IsNull(.OldValue) Then
Set rs = dbEngine(0)(0).OpenRecordset(strSlq)
If rs.RecordCount = 0 Then
MsgBox strMsg
.Value = .OldValue
End If
rs.Close
End If
End With

I'm trying to cancel a prohibited deletion of an existing date in a
textbox

[quoted text clipped - 25 lines]

How
do I make this work?

 

[Bill R via AccessMonster.com]

Thank you Allen.
Bizarre behavior! The undo works in the before update event of an option
group, but not a text box.
I moved to code to the after update event.

Bill

Bill, if you do go with the AfterUpdate event, you can get away with undoing
the whole form, or reassigning the OldValue to the control, but I should
warn that you cannot undo a control in its AfterUpate event.

Again for some odd reason, that fails silently, i.e. the code runs, but the
control is not undone.

Thanks Allen.

[quoted text clipped - 39 lines]

 

[Allen Browne]

Just a crazy thought: any chance you have a text box with a different Name
than the field it is bound to, and you undid the field not the text box?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen.
Bizarre behavior! The undo works in the before update event of an option
group, but not a text box.
I moved to code to the after update event.

Bill

Bill, if you do go with the AfterUpdate event, you can get away with
undoing
the whole form, or reassigning the OldValue to the control, but I should
warn that you cannot undo a control in its AfterUpate event.

Again for some odd reason, that fails silently, i.e. the code runs, but
the
control is not undone.

Thanks Allen.

[quoted text clipped - 39 lines]

How
do I make this work?