Can't use Undo on Form

I have a form on which I check the condition of one control (Denied). If it is checked (-1), then I don't want to allow the user to check off that the invoice is on hold (HoldInvoice).  Here is my code. I have it on the Before UPdate Event of the HoldInvoice control.

If Me.Denied = -1 Then
MsgBox "This invoice is Denied. It cannot be put on hold."
Me.HoldInvoice.Undo
End If

I get the MsgBox message, but it still allows me to check the box.

I also tried:
If Me.Denied = -1 Then
MsgBox "This invoice is Denied. It cannot be put on hold."
Me.HoldInvoice = 0
End If
 
This gives me a run-time error '-2147352567 (80020009)':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field.
4charityAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
Sorry this worked fine for me:

Private Sub Hold_BeforeUpdate(Cancel As Integer)
    If Me.Denied = True Then
        MsgBox "You cannot set Hold if Denied is On", vbInformation
        Cancel = True
        Me.Undo
    End If
End Sub


Sample attached

Database57-2011-04-05.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
This is an Interface/Data entry/Timing issue.

Are the "Denied" checkboxes already set when the form is opened, ...or is the data enter person trying to set the Denied checkbox, then immediately Not be able to change the same record?
(You may have to save the record first: Docmd.RumCommand.acsaveRecord)

Other notes:
When you Undo the record, you return the checkbox to its original state.

The catch here is that suppose you check the box by mistake.
Now you can never fix it...

Can you please walk us through a complete, detailed, step by step example of how you want the complete data entry sequence to work?

JeffCoachman
0
 
4charityAuthor Commented:
The Denied Checkbox has already been checked (most probably on another day....).
The user opens this screen, and tries to check the "HoldInvoice" checkbox.
The message box to comes up at that point alerting them that the invoice is denied.
Then, I want it to be so that the HoldInvoice checkbox is set to 0.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nick67Commented:
Sideways, the way to do this is in the current event of the form
Pseudocoded:
If Me.Denied = true then Me.HoldInvoice.Locked = True
That way they CAN'T change HoldInvoice and you don't need undo.

If you put this in the Current event of the form and the AfterUpdate event of Denied, you should get the effect you want

Me.HoldInvoice.Locked = Me.Denied.Value

--As an aside
<If Me.Denied = -1 Then>
looks good and works well, until you upgrade to SQL server.  
Then it goes BANG! because SQL Server uses 1, not -1, for true.
That was a habit that came back to bite me in the rear
Just sayin' :) --
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then you will have to consider the best event to do this on.

Most developer use the Before update event of the *FORM* to do all the validation.

As slick as it seems, ...trying to do things like this "On-the-Fly", (like you are requesting) opens up a whole can of interface worms...

Besides, if you want to use the Before Update event of a *Control* then you will have to Cancel the event as well (if the data is invalid)
(Possibly in addition to Undoing the record)


For ex: This worked fine for me as-is
Private Sub Hold_BeforeUpdate(Cancel As Integer)
    If Me.Denied = True Then
        MsgBox "You cannot set Hold if Denied is On", vbInformation
        Cancel = True
    End If
End Sub


JeffCoachman
0
 
Nick67Commented:
@boag2000

On your sample, if you click Denied and then HoldInvoice, it erases them both because Me.Undo is global to ALL the changes made to the record.
That is probably not desired or desirable.

I, myself, have never had anything but problems when I try to use a BeforeUpdate event.  Generally, I give the user unbound controls to set values in, and a command button to commit the changes.  The Click event then goes through the users' choices, ensures that they make sense, and either blows them in via some recordset work, or tosses the user back to the control with the nonsense value.

When I do present users with bound controls, I invariably have an 'editing' command button.  The current events locks everything, so the users can't inadvertently make changes while browsing.  The 'editing' button unlocks the controls that make sense for the user to change values in.  Generally, I try to keep the users from working directly with bound data.  Mine are too likely to screw it up :)

@4Charity
boag2000 is right about what ails your BeforeUpdate code.
If you don't want the changes to go through you need
Cancel = True

It would look and probably work like this
 
Private Sub Hold_BeforeUpdate(Cancel As Integer)
    If Me.Denied = True Then
        MsgBox "This invoice is Denied. It cannot be put on hold."
        Cancel = True
    End If
End Sub

Open in new window


Where I always run into grief is in doing exactly what you are trying to do.
The snippet will not allow the change to occur--but the control still has that changed value in it.
The user has to undo it--and sometime that annoys the user.
So we'd like to do it for them.
But that gets tricky because there's an event already on the go and a bunch of possible value changes.
It's hard to get it error-free.

Which is why I like Locked and AfterUpdate much better :)
It's simpler to prevent them from doing something wrong than undoing the error.

If you do need a message to the user when they are trying to change a locked control, you can put that in the GotFocus event

Private Sub HoldInvoice_GotFocus()
if Me.HoldInvoice.locked = true then
    MsgBox "This Invoice cannot be mark Hold.  It is presently Denied"
end if
end sub

0
 
4charityAuthor Commented:
Thanks. This gave me a lot of useful information on several things. The database supplied did have the problem of Undoing both the Denied and Hold information, but using this, and the other discussions, I came up with:

 If Me.Denied = True Then
        MsgBox "You cannot set Hold if Denied is On", vbInformation
        Cancel = True
        Me.Hold.Undo
    End If
End Sub
0
 
Nick67Commented:
Glad you got it going :)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Nick67:

<I, myself, have never had anything but problems when I try to use a BeforeUpdate event.  Generally, I give the user unbound controls to set values in, and a command button to commit the changes. >

Yes, but it was not clear if the OP could, or wanted to, change their current design...

Again, just from answering sooooo... many question on designs like this over the years here, ...I always recommend that new developers try not to "Fight" Access' built in functionality and
do all of the validation on the Before update event of the Form.
(Just like in Web Order forms)

This is not as slick as the "On-the-fly" techniques, but again, it saves new developers from twisting themselves in knots.

As they get better they can investigate more advanced designs like unbound control or unbound forms, where they have much more control, but have to adhere to stricter coding/interface practices.

;-)

JeffCoachman

0
 
Nick67Commented:
@JeffCoachman

The first form I ever wanted to build ran off an un-updateable query.
It was sink or swim with using recordsets to blow in the data.
Then I ran into grief with relations and primary keys.
The end user logic of how to enter the data conflicted with the necessity of creating the linchpin primary key first
It was sink or swim with unbound forms.

There are always two ways to learn something:
Progressively, a little bit at a time.
Or monstrously--doing the hardest d*mned thing first, and then being good to go with most everything else as a result.

Circumstance, in my case, usually dictates 'monstrously'.
It has also come to be my preference :)
'Hello World' just isn't my thing
0
 
4charityAuthor Commented:
Jeff,

Thanks again for the insight on the Form BeforeUpdate event. I will certainly take this into account, especially in the future. I have been reworking this database as it's scope has been growing over the last 1-1/2 years, and there are so many on-the-fly changes to it that it really is like a bowl of spaghetti.
0
 
Jeffrey CoachmanMIS LiasonCommented:
By "On-The-Fly" I meant trying to validate the data in another control, while still in (editing, or creating) the same record.

This can be problematic because of the order in which the user tabs (navigates) the app.
It can also be effected by nulls, empty strings, ...etc

For what you are asking, it could go either way.

I'll repeat here my standard reference.
An Amazon.com order
You enter all of your order information first
Name
Item
Shipper
Shipping method
CC number
...etc.

Then you click "Submit"...
Then the entire form (all the fields) are validated in one step.
A screen appears with all the errors listed.

In other words, as you are entering the data, Amazon.con wont stop you and tell you that you forgot to select a Shipper.
It won't stop you (again, in the middle of entering the data) and tell you that your CC is expired
It won't stop you and tell you forgot the CC security code.
...etc.

It validates all the data at the end, after you click "Submit". (as most websites do)
(Some people find this frustrating as well)

Access is set up to do this as well, however as a programmer, you can make Access act in anyway you want.

You can "Dream Up" the most elaborate, complex, user friendly Data entry systems that you want.

However, (as a wise person once said), ...with that great power comes great responsibility
Now you have to write code to protect the data (and the user) from every possible contingency.
(skipping the field, Nulls, pressing the space bar, New records, filters, unfamiliar errors, complex error handling, specialized validation, ...ect)

This is why I recommended to new developers to use Access default functionality at first.
(Validate the data on the Before update event of the form)
This way you can cancel the event, and do all of your validation in one place

There are literally Hundreds of questions here like this.
Where one custom interface feature creates an "issue".
The fix for that issue cases another issue somewhere else, ...ad infinitum.

The end quote usually ends with something like.
"Well, it's not perfect, but it will do for now..."

Again what you asked for is fairy basic.

;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.