Solved

Undo Control Not working

Posted on 2010-11-15
20
364 Views
Last Modified: 2012-05-10
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
Comment
Question by:4charity
  • 10
  • 5
  • 3
  • +1
20 Comments
 
LVL 75
ID: 34139481
Is this a Bound control?  If not, UnDo only works on Bound controls.

mx
0
 

Author Comment

by:4charity
ID: 34139500
Yes, it is bound to a field in the underlying table.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 167 total points
ID: 34139503
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
 
LVL 75
ID: 34139514
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
 

Author Comment

by:4charity
ID: 34139530
OK - I added the Cancel line and switched to Date instead of Now.
Still doesn't undo.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 167 total points
ID: 34139537

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
 

Author Comment

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

End If
0
 
LVL 75
ID: 34139561
Does Me.InvoiceDate include Time?
Is InvoiceDate bound to a real Date/Time data type ?

0
 
LVL 75
ID: 34139575
Are you getting the Message box prompt ?

mx
0
 

Author Comment

by:4charity
ID: 34139581
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 34139616
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
 
LVL 75
ID: 34139626
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
 
LVL 75
ID: 34139659
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
 

Author Comment

by:4charity
ID: 34139848
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
 
LVL 75
ID: 34139873
That almost always works ... unless something really weird is going on.

mx
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 34141444
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
 
LVL 75
ID: 34142296
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
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 34142523
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
 
LVL 75
ID: 34142539
I think she got it to work >> ". this does work"

mx
0
 
LVL 8

Assisted Solution

by:Emil_Gray
Emil_Gray earned 166 total points
ID: 34142583
Here is an example that works when using the Validation Rule. Validation Rule Property
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now