After update event triggered by changing tab

[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

 

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

 

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

 

[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

 

[The_Doc_Man]

@riktek

Update and save have different meanings at a fine level. You can save a record on a form using the appropriate DoCmd.Save action along with the acCmdSaveRecord option. However, if you are going to Update something via a true Update query, you must update using VBA to trigger an SQL action. There will be no event in that case. Also, you can't make a form that actually does Updates based on an Update query. The reason is that an Update query returns no records so cannot be the basis of a form. Therefore you are in an apples-vs-oranges situation. The problem is that in Access, update is both a query type and the effect of a Save action; in the latter case, the underlying record IS updated (lower-case U) - by a Save action, not an Update (upper-case U).

Any form on one of the tab pages, even the first tab page, is just another subform.

With one exception... the main form participates on ALL of the tabs. If you put controls on a Tab control, they show up with and are controlled as part of the main form. If you put a form on a tab then you are right, that has to be in a sub-form control. But it is no different than a sub-form on a main form that doesn't have tab controls. You address tab-resident controls as though the tab didn't exist. Which is why you can't duplicate control names across two or more tab controls on the same form. Same name space.

Switching from one control to another in such a way as to cross tab controls has NO EFFECT on saving a record UNLESS the control losing focus was a sub-form. The presence of a tab control has NO EFFECT on saving the parent record. It is the presence of a populated sub-form control that makes the difference.

 

[riktek]

The presence of a tab control has NO EFFECT on saving the parent record.

No disagreement but that is beside the point, which is that it isn't clear from the OP that the parent record, or form, is in question. He said the "main form," not the "parent form," was on a tab page. A tab control's parent can't be on a tab page. Common usage of course is that "main form" means "parent form" but here, that isn't the configuration described.

 

[riktek]

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

Perhaps but if so, she ought to be able to respond substantively rather than ad hominem.

It's an interesting presentation but I've also studied event progression with equal or greater rigor, so I do, too. Perhaps she might learn something if she paid attention, read closely, and weren't so prickly.