Solved

Access textbox BeforeUpdate cancel does not work

Posted on 2014-03-13
9
2,238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

736 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