Full Version: What Trigger Action To Use To Update And Send Email?
UtterAccess Forums > Microsoft® Access > Access Forms
eissakha
Hi all,
I am sorry my title doesn't have anything to do with what I am trying to do but I dont know how else to describe what i am trying to do here.
Ok, so I have a main form based on table (lets say) A. It has items such as employee number, name, etc., skills_List.
Then I have a subform based on table B. It contains multiple skills that the employee possesses. Its basically a one to many relationship between table A and table B. Now I was using the afterupdate trigger of the subform to say run some code to loop through the list of skills and come up with a string that contains the various skills seperated by a semicolon and store the value in the skils list field in table A. At the same time, using email field in the employee table, I send an email out regarding the changes in the skills of the employee. Ok, this is becoming a bad example, I wish I had used the real thing instead of this bad example, but oh well... Anyways, it was working fine until I just realized a major problem. when you are in the middle of selecting skills, after you select each one (but are not done with the form) it runs the after update trigger and it tries to send emails before you are done with listing all the skills. In my particular case I use checkboxes in my subform but its no differennt than having individual items within the subform. I tried moving the code up to the after update trigger of the skils list in table A but that would fire every time the skill list is updated (after every single item that's added in the subform). Bottom line is I want to wait till all the skills are filled in or deleted from the subform before I fire the trigger to update the skills list in table A and start sending out emails.
I am sorry, this is very confusing, I can explain better if you have a specific question but to summarize, the trigger goes off based on the entry of the partial information in the subform, I want it to wait until i am done entering / updating / deleting the items in the subform before firing the trigger.
Can anyone advise.
Thanks.
Edwin
theDBguy
Hi Edwin,
How about instead of using events from your field controls, just add a command button that the user would click to send the email when they are done with the form?
Just my 2 cents...
vtd
You set-up stores calculated values in the Table which violates the Normal Forms! In fact, part of what you are trying to do is to avoid inconsistencies between the set of sub-records and the concatenated list of skills which is cumbersome, inefficient and unreliable (you will have to make the it impossible to enter data into the Skills (sub-)Table without using your Form - this is not easy to accomplish as it looks).
The much better way is to simply create the concatenated list of Skills only when you need it. This way, the concatenated list is always correct/consistent with the data stored in the sub-Table.
The AfterUpdate Event of the SubForm will fire whenever you update a Record/row in the Subform regardless whether the SubForm is in Normal (single-Record) View or ContinuousFormView. Remember that even in ContinuousFormView, you still have only one CurrentRecord and if any update is done (on the CurrentRecord), the (Sub)Form_AfterUpdate Event will fire.
Thus, I don't think you can use the AfterUpdate Event to send the e-mail unless you have something to indicate that you have completed all the updates. Personally, I think a CommandButton on the main Form to send the email would be a cleaner set-up.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.