Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2761
  • Last Modified:

Access textbox BeforeUpdate cancel does not work

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
bthouin
Asked:
bthouin
  • 4
  • 3
  • 2
1 Solution
 
mbizupCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
bthouinAuthor Commented:
@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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
mbizupCommented:
Yup... missed the fact that the textbox is unbound.  You'll need to implement what Scott is suggesting or something similar.
0
 
bthouinAuthor Commented:
>>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
 
mbizupCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
bthouinAuthor Commented:
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
 
mbizupCommented:
You've got the Accepted Answer and points awarded correctly, but the names in your last post mistaken here,

Credit goes to Scott...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now