Problem trying to make text box BeforeUpdate procedure undo a null

Richard Sherratt

I'm trying to stop users deleting an existing value in a field. If
they go to an existing record and hit delete, I want to give an error
message and reverse the change. The BeforeUpdate event is firing and
they get the error message. But when they click "OK", the change is
not undone. They can hit enter and go on to the next field with the
null not being undone. However, if they change to an illegal (not
null) value, the change is undone.

The code is:

If IsNull(Me![whatever]) then
MsgBox "blah blah"
Me![whatever].Undo
Cancel = True
End If

I've tried it with the .Undo and the Cancel reversed, with SendKeys
"{ESC}" and various combination. No joy. However, if I manually hit
the Esc key, it usually undoes the change.

It's a bound control on a bound form. Access 97 and SQL Server 2000.
RecordSource returns one record.

This does not compute.

--
Regards.
Richard.

Jul 20 '07 #1

Allen Browne

Use the AfterUpate event of the control if you want to undo it.

Example:

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Undo
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Sherratt" <ri**************@NOTHINGHEREbrunsley.com.auwrot e in
message news:77********************************@4ax.com...

I'm trying to stop users deleting an existing value in a field. If
they go to an existing record and hit delete, I want to give an error
message and reverse the change. The BeforeUpdate event is firing and
they get the error message. But when they click "OK", the change is
not undone. They can hit enter and go on to the next field with the
null not being undone. However, if they change to an illegal (not
null) value, the change is undone.

The code is:

If IsNull(Me![whatever]) then
MsgBox "blah blah"
Me![whatever].Undo
Cancel = True
End If

I've tried it with the .Undo and the Cancel reversed, with SendKeys
"{ESC}" and various combination. No joy. However, if I manually hit
the Esc key, it usually undoes the change.

It's a bound control on a bound form. Access 97 and SQL Server 2000.
RecordSource returns one record.

This does not compute.

--
Regards.
Richard.

Jul 20 '07 #2

Richard Sherratt

On Fri, 20 Jul 2007 16:58:36 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>Use the AfterUpate event of the control if you want to undo it.

Example:

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Undo
End If
End With
End Sub

Thanks, Allen. The .Undo still doesn't work, but it gave me an idea.

This works.

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Value = .OldValue
End If
End With
End Sub

BTW, when did we change from Me![ControlName] to Me.[ControlName]?

--
Regards.
Richard.

Jul 21 '07 #3

Allen Browne

Okay: assigning the old value would work.

My preference for the dot is that:
a) Access autocompletes (greater accuracy when typing),
b) It doesn't compile with a misspelled name (e.g. if you remove the
control.)

Anything that helps guarantee the software is right before it makes it out
the door is worthwhile, even if it wasn't less effort (as in this case.)

Andy Baron has a good article on dot verses bang:
http://doc.advisor.com/doc/05352

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Sherratt" <ri**************@NOTHINGHEREbrunsley.com.auwrot e in
message news:a4********************************@4ax.com...

On Fri, 20 Jul 2007 16:58:36 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>>Use the AfterUpate event of the control if you want to undo it.

Example:

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Undo
End If
End With
End Sub

Thanks, Allen. The .Undo still doesn't work, but it gave me an idea.

This works.

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Value = .OldValue
End If
End With
End Sub

BTW, when did we change from Me![ControlName] to Me.[ControlName]?

--
Regards.
Richard.

Jul 21 '07 #4

Arch

On Sat, 21 Jul 2007 18:14:25 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>Okay: assigning the old value would work.

My preference for the dot is that:
a) Access autocompletes (greater accuracy when typing),
b) It doesn't compile with a misspelled name (e.g. if you remove the
control.)

Anything that helps guarantee the software is right before it makes it out
the door is worthwhile, even if it wasn't less effort (as in this case.)

Andy Baron has a good article on dot verses bang:
http://doc.advisor.com/doc/05352

Allen, the link appears to be broken.

Jul 21 '07 #5

Allen Browne

Rats! That was a really good article.

Ah here it is:
http://advisor.com/doc/05352

Looks like they've restructured the way they present those articles.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Arch" <se*****@spam.netwrote in message
news:2r********************************@4ax.com...

On Sat, 21 Jul 2007 18:14:25 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>>Okay: assigning the old value would work.

My preference for the dot is that:
a) Access autocompletes (greater accuracy when typing),
b) It doesn't compile with a misspelled name (e.g. if you remove the
control.)

Anything that helps guarantee the software is right before it makes it out
the door is worthwhile, even if it wasn't less effort (as in this case.)

Andy Baron has a good article on dot verses bang:
http://doc.advisor.com/doc/05352

Allen, the link appears to be broken.

Jul 21 '07 #6

UrbanSpaceman

Allen, the url is now:

http://advisor.com/doc/05352

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

Okay: assigning the old value would work.

My preference for the dot is that:
a) Access autocompletes (greater accuracy when typing),
b) It doesn't compile with a misspelled name (e.g. if you remove the
control.)

Anything that helps guarantee the software is right before it makes it out
the door is worthwhile, even if it wasn't less effort (as in this case.)

Andy Baron has a good article on dot verses bang:
http://doc.advisor.com/doc/05352

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Sherratt" <ri**************@NOTHINGHEREbrunsley.com.auwrot e in
message news:a4********************************@4ax.com...

>On Fri, 20 Jul 2007 16:58:36 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>>>Use the AfterUpate event of the control if you want to undo it.

Example:

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Undo
End If
End With
End Sub

Thanks, Allen. The .Undo still doesn't work, but it gave me an idea.

This works.

Private Sub whatever_AfterUpdate()
With Me.whatever
If IsNull(.Value) And Not IsNull(.OldValue) Then
.Value = .OldValue
End If
End With
End Sub

BTW, when did we change from Me![ControlName] to Me.[ControlName]?

--
Regards.
Richard.

Jul 21 '07 #7

Arch

On Sun, 22 Jul 2007 01:12:35 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>Rats! That was a really good article.

Ah here it is:
http://advisor.com/doc/05352

Looks like they've restructured the way they present those articles.

That link works. The article is definitely worthwhile. Thanks.

Jul 21 '07 #8