Avatar of 4charity
4charity asked on

Undo Control Not working

I am trying to put some code on a date field, so that a date in the future cannot be accidentally typed in. If the date is in the future, I want a message box to appear, and the control to be cleared for a new (correct) date entered. The message box pops up, but the control doesn't clear. Any suggestions?


Private Sub InvoiceDate_BeforeUpdate(Cancel As Integer)

If Me.InvoiceDate > Now() Then
MsgBox "Invoice Date Cannot be in the future. Please adjust."
Me!InvoiceDate.Undo

End If

End Sub
Microsoft Access

Avatar of undefined
Last Comment
Emil_Gray

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Is this a Bound control?  If not, UnDo only works on Bound controls.

mx
ASKER
4charity

Yes, it is bound to a field in the underlying table.
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Bound ... ok:


Private Sub InvoiceDate_BeforeUpdate(Cancel As Integer)

If Me.InvoiceDate > Date() Then    ' *** compare against Date()
MsgBox "Invoice Date Cannot be in the future. Please adjust."
    Cancel = True ' Add this ***********
    Me!InvoiceDate.Undo

End If

End Sub
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
4charity

OK - I added the Cancel line and switched to Date instead of Now.
Still doesn't undo.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
4charity

If Me.InvoiceDate > Date Then
MsgBox "Invoice Date Cannot be in the future. Please adjust."
Cancel = True
Me!InvoiceDate.Undo

End If
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Does Me.InvoiceDate include Time?
Is InvoiceDate bound to a real Date/Time data type ?

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Are you getting the Message box prompt ?

mx
ASKER
4charity

Yes.

This works:

If Me.InvoiceDate > Date Then
MsgBox "Invoice Date Cannot be in the future. Please adjust."
Cancel = True
Me!InvoiceDate.Undo
Undo
End If
Dale Fye

Charity,

You should not need the Undo line, and it may cause an undo for the entire record, not just for the current control.

Dale
Your help has saved me hundreds of hours of internet surfing.
fblack61
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

But  Undo will undo the entire form ... ?

I see this form time to time ... Me.someControl.Undo just flat ass does not work ... not sure why.

mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Sometimes reversing makes it work:

If Me.InvoiceDate > Date Then
MsgBox "Invoice Date Cannot be in the future. Please adjust."
Me!InvoiceDate.Undo
Cancel = True
End If
ASKER
4charity

In checking for it to work, you have to make sure you really change the date alot, so that you realize it went back to the previous date, not a null field. this does work:

If Me.InvoiceDate > Date Then
MsgBox "Invoice Date Cannot be in the future. Please adjust."
Cancel = True
Me!InvoiceDate.Undo
End If
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

That almost always works ... unless something really weird is going on.

mx
Emil_Gray

This should be done as an AfterUpdate action.

Private Sub InvoiceDate__AfterUpdate()

If Me.InvoiceDate > Date Then
MsgBox "Invoice date cannot be in the future. Please adjust.
Me.InvoiceDate = Null
Me.Refresh
End If

End Sub
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well, AfterUpdate is After the Fact ... control has already be updated.  That's why it's called "After' Update.  The correct event for any validation is the Before Update event, either at the Control level or Form level.

mx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Emil_Gray

DatabaseMX, you're right. That is the best way but it appeared that he was having problems with the BeforeUpdate action so I just was suggesting an alternative solution. Another suggestion might be to use the Validation Rule as an input limiter.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I think she got it to work >> ". this does work"

mx
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.