• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

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
0
4charity
Asked:
4charity
  • 10
  • 5
  • 3
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is this a Bound control?  If not, UnDo only works on Bound controls.

mx
0
 
4charityAuthor Commented:
Yes, it is bound to a field in the underlying table.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Also, I would suggest (if Bound)


Private Sub InvoiceDate_BeforeUpdate(Cancel As Integer)

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

End If

End Sub

Also, you are comparing against Now() , which includes Time ... are you aware of that?

Maybe it needs to be

If Me.InvoiceDate > Date() Then

mx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
4charityAuthor Commented:
OK - I added the Cancel line and switched to Date instead of Now.
Still doesn't undo.
0
 
Dale FyeCommented:

If you are going  to use BeforeUpdate, then  try this:

Private Sub InvoiceDate_BeforeUpdate(Cancel As Integer)

    If Me.InvoiceDate > Now() Then
        MsgBox "Invoice Date Cannot be in the future. Please adjust."
        Cancel = true
    End If

End

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

End If
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Does Me.InvoiceDate include Time?
Is InvoiceDate bound to a real Date/Time data type ?

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Are you getting the Message box prompt ?

mx
0
 
4charityAuthor Commented:
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
0
 
Dale FyeCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
4charityAuthor Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
That almost always works ... unless something really weird is going on.

mx
0
 
Emil_GrayCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
Emil_GrayCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I think she got it to work >> ". this does work"

mx
0
 
Emil_GrayCommented:
Here is an example that works when using the Validation Rule. Validation Rule Property
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now