Solved

Access textbox BeforeUpdate cancel does not work

Posted on 2014-03-13
9
2,052 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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