After update event triggered by changing tab (1 Viewer)

[Andy74]

Hello,

I have a main form with a subform, which is placed on a tab page of a tab control belonging to the main form.
The main form afterUpdate event should trigger an email notification. I am trying to include in the email notification some information which is placed in the records of the subform. The issue I have is that the when the user clicks on tab page which contains the main form the afterUpdate event of the main form is triggered before the subform is filled by the user. Hence, the notification is sent before filling the subform. Is there a smart way to avoid this?

Andy

 

[Gasman]

You use the BeforeUpdate event to ensure data is correct before doing anything.

 

[moke123]

Have you tried triggering your email in the subform afterupdate event?

 

[arnelgp]

maybe deliberately send the notification by adding a button on the main form.
the button will need to validate if the subform has some data.

 

[Andy74]

Have you tried triggering your email in the subform afterupdate event?

it cannot work, because the user will enter several records on the subform. The notification should be sent only after entering records on the suborm and when the users either close the form or save the record on the main form.

 

[Andy74]

maybe deliberately send the notification by adding a button on the main form.
the button will need to validate if the subform has some data.

yes, that's the ultimate solution. But I wanted to avoid this because the typical users simply forget to hit the buttom.

 

[Andy74]

You use the BeforeUpdate event to ensure data is correct before doing anything.

the problem is that BeforeUpdate triggers before the AfterUpdate, hence the problem is always there

 

[Gasman]

Yes, and that is why any validation should go in the Before event. Pointless validating after the data has been updated.

 

[Mark_]

Hello,

I have a main form with a subform, which is placed on a tab page of a tab control belonging to the main form.
The main form afterUpdate event should trigger an email notification. I am trying to include in the email notification some information which is placed in the records of the subform. The issue I have is that the when the user clicks on tab page which contains the main form the afterUpdate event of the main form is triggered before the subform is filled by the user. Hence, the notification is sent before filling the subform. Is there a smart way to avoid this?

Andy

Can you walk through what you expect your USER to do and at what point in the USER experience the email needs to be sent?
I am guessing "User goes in to parent form and creates/updates/selects parent record. User then goes in to subform and creates one or more child records. User then needs to do SOMETHING that actually commits everything and says 'DONE' to go to the next parent record". What is the condition that is "Done"?
That is the point you create the email. Unless you walk us through exactly how you know they are 'DONE' and the email needs be sent, we won't be able to give you the best answer.

 

[Minty]

If it needs sending when the form closes, why not use the forms close or unload event? (I may be missing something....)

 

[theDBguy]

yes, that's the ultimate solution. But I wanted to avoid this because the typical users simply forget to hit the buttom.

If the users don't click on the button, maybe you can then "click it" for them. For example, when the form or the app closes (or opens), you can run a procedure to check if there were any emails that needed to be sent and then send them at that time. Just a thought...

 

[The_Doc_Man]

the problem is that BeforeUpdate triggers before the AfterUpdate, hence the problem is always there

Technically, no. The actual act of updating triggers the BeforeUpdate and AfterUpdate events, but there is no visible Update event. That particular event is purely internal to Access itself. And the main form's update occurs when you return to the main form after updating one or more records in the sub-form. When you activate the main form after exiting a "dirty" sub-form, that update is automatic and is caused by the sub-form being linked to the main form.

 

[Andy74]

If the users don't click on the button, maybe you can then "click it" for them. For example, when the form or the app closes (or opens), you can run a procedure to check if there were any emails that needed to be sent and then send them at that time. Just a thought...

right, I think that's a good idea

 

[Pat Hartman]

I'm not sure how you are triggering the AfterUpdate event on the main form Before the subform record gets saved. You need to put in some message boxes in both the sub and main form's Before and After Update events and walk slowly through the process. Here is a link to a database and some videos on how form/control events work, expecially concentrating on the BeforeUpdate event. But, trying to use this sample will require watching at least one of the videos so you understand how the sample works. Then you have to import your forms so you can test them inside my training app. So, try the message boxes first, it is less work.

When focus moves from the subform to the main form, the subform record ALWAYS gets saved before control shifts to the main form and it makes no sense for the main form's AfterUpdate event to run if its BeforeUpdate event didn't run first. PS - your validation goes in the BeforeUpdate event since it is the last event that runs BEFORE the record gets saved and the AfterUpdate event doesn't run until AFTER the record is saved. So, if your validation code is in the Form's AfterUpdate event, you are closing the barn door after the horses have escaped. That bad data has already been saved. This was the impetus for making the example in the first place - people are not using the two events correctly.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[riktek]

Events occur when they do but sometimes not when documented. When conditioning code on them alone, the best one can do is to better understand their triggers and how to avoid them or make them happen, or to find another event, which often will be for another object.

Updates can be triggered expressly but also will occur unavoidably (along with their associated events) with a change in focus. Focus departs when the Access SQL engine runs, for a refresh, requery, or to load a subform. Constraining focus, e.g., by avoiding these, removing other objects from the tab order, or canceling keystrokes in the KeyDown event for the form or a control, can help. IIRC, one also can avoid form update events entirely if the form isn't bound.

This said, one can establish conditions that are evaluated when the relevant event occurs to permit or prevent execution. The mechanism can take the form of module-level Boolean variables that are set along the way, or a Boolean function (i.e., one that performs the test and returns True or False).

I did just this recently for a data entry form, where the only relevant event was the Change event for a single text box. I left the form unbound, removed all other controls from the tab order, and ran a mildly complex set of validation tests in a decision tree in that event procedure, with various lookups and counters being set along the way. I factored each test into its own Boolean function for simplicity. The key operation (record entry in this case, sending an email in yours) is triggered by this same event but only after all conditions are met.

Also, if you distinguish data entry from data presentation, you also might consider a separate data entry form that substitutes a combo box and pop-up (for data entry) for some or all of the subforms. It may not work for your desired UX but it will simplify the form, validation, and the handling of update triggers. You can trigger a pop-up expressly by double-clicking on its corresponding combo box, or with the combo box NotInList event if the user types in new data. Your main form's validation rule then might simply be that none of the combo boxes is Null. If the form is bound, you can validate in Form.BeforeUpdate (canceling the event on fail) and trigger the email in Form.AfterUpdate. If the form isn't bound, controls' Exit event may be most reliable.

Finally, reading your OP closely, you stated that the "main form" was on a tab page. Ordinarily, "main form" is understood to mean the form on which a tab control or subform exists. Any form on one of the tab pages, even the first tab page, is just another subform. Changing tabs will trigger updates for forms on the tab page one departs.

 

[Pat Hartman]

Events occur when they do but sometimes not when documented.

No. They occur at specifically described times.

Updates can be triggered expressly but also will occur unavoidably (along with their associated events) with a change in focus.

No, except when focus moves from a main form to a subform or vice versa. That causes the form you left to save. Just moving between unrelated forms or controls on a form does not trigger a save.

I left the form unbound,

Events related to updating do not run for unbound forms. Code also does not trigger them. If you know how to use Access, you know how to use bound forms. You do use the tool you have in the way it was intended to be used to get the most out of a tool. You can sink a nail with a screwdriver but you'll probably mash a few fingers in the process. Unbound forms certainly have a place but they are not "go to" methods.

Changing tabs will trigger updates for forms on the tab page one departs.

It is the moving from the subform to the parent form that triggers the update, NOT the changing of the tab pages. You should know that if you know how events work.

 

[riktek]

No. They occur at specifically described times.

Controls' Before and After Update events often do not occur, the documentation notwithstanding. Similarly, Form.Current and Form.Activate do not occur for subforms or pop-ups. The language describing controls' Change and Dirty events is identical but Dirty occurs only with the first change and Change occurs with every keystroke.

No, except when focus moves from a main form to a subform or vice versa. That causes the form you left to save. Just moving between unrelated forms or controls on a form does not trigger a save.

Updates are distinct from saves. Controls' Before- and AfterUpdate events occur when focus departs the control, without the record updating. Also, forms don't save (except in their design), records do.

Events related to updating do not run for unbound forms.

I said this.

Code also does not trigger them.

I did not say this. One can, however, expressly update a record with, e.g., Form.Dirty = False regardless of events.

Unbound forms certainly have a place but they are not "go to" methods.

I also did not say this. I did say they were a way of avoiding events relating to form updates.

It is the moving from the subform to the parent form that triggers the update, NOT the changing of the tab pages.

The OP was not referring to a "parent form" but a "main form" that he said was on a tab page. It isn't clear what he meant by the phrase "main form." It is correct that moving focus away from a form can trigger a dirty record to update but the OP described changing tab pages, so I framed the statement in those terms even though, as you noted, it is what causes focus to move from one subform to another.

If you know how to use Access, you know how to use bound forms.

You should know that if you know how events work.

I know how to use Access and I know how events work. I don't need the put-downs.

 

[Pat Hartman]

Controls' Before and After Update events often do not occur,

They always occur when the controls are BOUND - UNLESS you used code to modify the controls. The documentation tells you this.

Updates are distinct from saves.

In what universe?

Similarly, Form.Current and Form.Activate do not occur for subforms or pop-ups.

Wrong - partially. The Current event runs for ALL forms, at least once when they open. Activate does not run for subforms because I think it is controlled by Windows.

Reading from the bottom up, you can see that the subform's Current event DOES fire but its Activate event does not. When a form with a subform opens, the first event to run is the Open event of the subform. Then load, resize, current and gotFocus for the first control of the subform. Then the main form events run.

Updates are distinct from saves. Controls' Before- and AfterUpdate events occur when focus departs the control, without the record updating. Also, forms don't save (except in their design), records do.

If you are restricting the word "save" to refer to the form and "update" to refer to the record, I understand your confusion. Except that MS refers to records saving -
DoCmd.RunCommand acCmdSaveRecord
Most people use the words interchangeably. AND I was specific about the record save happening when focus moved from a subFORM to a mainFORM or vice versa and NOT when focus moved between controls.

I did not say this. One can, however, expressly update a record with, e.g., Form.Dirty = False regardless of events.

Actually, if the Form isn't dirty, newer versions of Access ignore the command and do not save the record.

I also did not say this. I did say they were a way of avoiding events relating to form updates.

You only need to avoid events relating to form updates if you don't understand what triggers them. This is seriously bad advice to give novices in general. Experts already know when they might need to use an unbound form. Novices see the recommendation and immediately think that "code is better" when it isn't. I've seen many Access "novices" who came to the tool as experienced programmers completely misuse Access because they didn't take the time to understand the event model and since they knew how to code, they just started writing code to "get around" normal Access event process logic. Access is a RAD tool. When you use a RAD tool, the best course of action is to use it as its designers envisioned. Once you become an expert, you will find opportunities to deviate but always because you know how do do something the "Access way" first. Access is amazing for what it does behind the scenes. I also came to Access with years of development experience behind me so I KNOW some of the kinds of code that are already baked into forms and reports and what they are actually saving me from having to handle with custom code because at one time if I wanted to scroll to a new page, I had to make it happen with 100% of my own code to manipulate the dataset and fill all the controls and make sure the previous record got saved if it needed to, etc, etc, etc.

The OP was not referring to a "parent form" but a "main form" that he said was on a tab page. It isn't clear what he meant by the phrase "main form." It is correct that moving focus away from a form can trigger a dirty record to update but the OP described changing tab pages, so I framed the statement in those terms even though, as you noted, it is what causes focus to move from one subform to another.

Except that changing tab pages isn't what causes the subform record to be saved. Moving from a subform to it's parent form or higher if the nesting is deeper than two, is what causes the subform you left to change, NOT pressing the tab on the parent form. It looks like it is the tab that causes the save but it is touching the tab that changes the focus to the main form and that is what triggers the save.

Download the database sample and import your own forms or modify mine to see when controls run. You will find some surprises given the way you currently think

I know how to use Access and I know how events work. I don't need the put-downs.

Sorry I offended you. But if you post expert comments with specific recommendations to use non-standard methods, you need to have your facts correct.

 

[riktek]

They always occur when the controls are BOUND - UNLESS you used code to modify the controls. The documentation tells you this.

This is a reasonable expectation, as is the case with forms, and the documentation suggests this, as you say. Nevertheless, I have found it not to be case on several occasions.

In what universe?

In the universe of controls.

Wrong - partially. The Current event runs for ALL forms, at least once when they open. Activate does not run for subforms because I think it is controlled by Windows.

My specific findings are that Activate and Deactivate do not occur for subforms. Activate (uncertain about Deactivate) and Current do not occur for popup forms. Current does occur for subforms.

If you are restricting the word "save" to refer to the form and "update" to refer to the record, I understand your confusion.

I am not restricting the word in that fashion and I am not confused. I am being rigorous. Save means saving a form design or updating a record. Note your example, acCmdSaveRecord, which makes the point, along with DoCmd.Save. Two meanings, two commands. Updating, however, means different things for forms and controls. Updating a form saves the current record. Updating a control only changes its .Value and does not save the current record. Forms' and controls' events (BeforeUpdate and AfterUpdate) nevertheless are identically named notwithstanding this distinction. Two meanings, identical event names. This is how, and why, updates and saves are distinct.

You only need to avoid events relating to form updates if you don't understand what triggers them.

Obviously false, not only on its face but also because all the support you can muster is further insult.

Moving from a subform to it's parent form or higher if the nesting is deeper than two, is what causes the subform you left to change, NOT pressing the tab on the parent form. It looks like it is the tab that causes the save but it is touching the tab that changes the focus to the main form and that is what triggers the save.

That's exactly what I said.

 

[Uncle Gizmo]

Obviously false, not only on its face but also because all the support you can muster is further insult.

I suspect Pat knows what she's talking about!

She has made a very clever form which monitors the events and records them as you can see in this video we did together:-

Bad Data is Bad for Business - Pat & Tony - Video 1

go.screenpal.com