Solved

Access textbox BeforeUpdate cancel does not work

Posted on 2014-03-13
9
2,186 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 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