Solved

Access textbox BeforeUpdate cancel does not work

Posted on 2014-03-13
9
1,987 Views
Last Modified: 2014-03-13
Hi

Have you ever had a canceled BeforeUpdate that did not work ? I have, and I don't understand any of it. Here is my code, on an unbound textbox in a straightforward Access form (Access 2007 SP3):

Private Sub txtGlobalLimitReviewDate_BeforeUpdate(Cancel As Integer)
   
    If Me.txtGlobalLimitReviewDate > Now() + 365 Then
        MsgBox "Review date may not be later than one year from now, please ammend", , "Correction required"
        Cancel = True
    End If

End Sub

When I set the date in that textbox to a date later than one year, I get my MsgBox message alright, but the date does NOT revert to the date I had before the change, it stays at what I just input ! I thought the idea of setting Cancel to True was exactly to cancel the current change and to reset the content as it was BEFORE the change the 'box OldVAlue) ? It seems the setting of the Cancel variable just doesn't do that. It prevents the excution of an AfterUpdate event processing which I have, but not the resetting to the previous value.

What can be the problem on such a simple thing ?

Thanks for help
Bernard
0
Comment
Question by:bthouin
  • 4
  • 3
  • 2
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39926007
If you want the data to 'revert' you need to explicitly undo it:

Private Sub txtGlobalLimitReviewDate_BeforeUpdate(Cancel As Integer)
    
    If Me.txtGlobalLimitReviewDate > Now() + 365 Then
        MsgBox "Review date may not be later than one year from now, please ammend", , "Correction required"
         me.txtGlobalLimitReviewDate.Undo
        Cancel = True
    End If

End Sub

Open in new window


The Cancel = true prevents the data in the underlying field from being updated, and the AfterUpdate event from running but the text in the control remains the same (just not committed to the table).

See this article for a little more info on the differences between what you see in your form vs what is actually in your table:
http://www.experts-exchange.com/Database/MS_Access/A_10162-What-is-in-a-field-Value-vs-Text-and-the-differences-between-form-data-and-table-data.html
0
 
LVL 84
ID: 39926026
Setting Cancel = True in the BeforeUpdate event just stops VBA from moving forward. With an unbound textbox, it would be up to you to handle any other tasks after that. Unlike bound textboxes, unbound ones don't have a "previous value", so you have to handle this yourself.

You could use the Enter and Exit events of the textbox, along with a few variables declared in your Form's General Declarations section, to manage this.

1. In the General Declaration section, add these variables:

Dim _OldValue As String
Dim fReset as Boolean

2. In the Enter event of your unbound textbox:

_OldValue = Me.NameOfYourTextbox

3. In the BeforeUpdate event, when you need to Cancel:

fReset = True
Cancel = True

4. Now in the Exit event of your unbound textbox:

If fReset Then
  Me.NameOfYourUnboundTextbox = _OldValue
  fReset = False
End If
'/ be sure to clear the form-level var, in case it's used other places
_OldValue = ""
0
 
LVL 1

Author Comment

by:bthouin
ID: 39926131
@Scott

>>Unlike bound textboxes, unbound ones don't have a "previous value"<<
Didn't know that, learned something. Thanks.

But unfortunately, your code does not work. I get all the events (Enter, then BeforeUpdate, then AfterUpdate, then Exit) ONLY if I don't cancel the BeforeUpdate. As soon as I cancel in BeforeUpdate, it skips the Exit event AND the next Enter event when I again input a change ! So the flag doesn't get set and the old value is not stored (and not used, as the Exit event doesn't fire).

@mbizup
Me.txtGlobalLimitReviewDate.Undo doesn't do anything, I had tried it and removed it, now I tried it again, it does nothing. When one reads the help, although not specified explicitely, it seems to apply to bound fields.

So guys, no joy !
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39926158
Yup... missed the fact that the textbox is unbound.  You'll need to implement what Scott is suggesting or something similar.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:bthouin
ID: 39926188
>>implement what Scott is suggesting or something similar<<
Scott's solution unfortunately does not work. And I have no idea what "something similar" could be...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39926256
Is the whole form unbound, or just that one textbox?

I would personally - especially on an unbound form - do all of the validation checks immediately prior to saving the record as a whole, rather than validating at the control level.

For an unbound form, validation code would be called from the click event of a "Save" button; for a bound form, validation code could be called from the Form's before update event.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39926308
I'd tend to agree with mbizup's suggestion of doing your validation at Form level. While there are certain times when you need to validate at Control level, in general it's best to do so when the user tries to save the record.

ONLY if I don't cancel the BeforeUpdate.
Then don't Cancel the BU event in your unbound textbox. As I mentioned earlier, it will stop downstream events from firing. BU does nothing for unbound textboxes anyway, so it should not be used unless you really WANT to stop those downstream events (and you don't in this case).

So essentially the BU event for your unbound textbox would look like this:
Private Sub Text3_BeforeUpdate(Cancel As Integer)
  fReset = True
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:bthouin
ID: 39926428
Hi both of you

Validation at form level is in my case not on the cards. The form is unbound, from the 100 or so fields and subforms on it, I have only 6 or 7 fields which can be input, the rest is calculated or is from the DB in subforms based on one master field on the form. So although the famous date field is later saved to a table, there is no "record to save"  for the form.

But I'm glad to be able to report that mbizup latest suggestion works perfectly. I just had additionally to not do the AfterUpdate if the date was not acceptable, but now all events are fired and the date reverts to the previous date value if the new one is wrong.

So thank you, and given that last success, I award the points to mbizup.

Regards
Bernard
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39926581
You've got the Accepted Answer and points awarded correctly, but the names in your last post mistaken here,

Credit goes to Scott...
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

867 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

12 Experts available now in Live!

Get 1:1 Help Now