Best practice form events (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
Hi, this may be difficult to describe. I have a subform in datasheet view, included is a 'duedate' field (which is populated from the preceding record). I also have an 'actualdate' field which is entered by the user upon completion of an inspection. Currently, i am trialling the onexit event to trigger a vba routine that generates a outlook event for the next scheduled inspection. Is this the best way?. Should i be using a form related event? Rather that a control triggered event. I would be interested in feedback.
 

June7

AWF VIP
Local time
Today, 00:26
Joined
Mar 9, 2014
Messages
5,425
Control event is fine as long as data needed for the Outlook message has been entered into other fields. But I would probably use AfterUpdate event.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 08:26
Joined
Sep 21, 2011
Messages
14,050
I would hazard a guess at the AfterUpdate event of the control.?
If it is amended another email would be sent.?
I would expect the OnExit to send an email every time you left the control, unless it is locked on initial entry?, and then it could not be amended, or is that the intention.?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
Hi, thanks. I'll adopt the after update event of the 'actualdate' & consider incorporating a "posted" flag to prevent reposts? Cheers.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:26
Joined
Oct 29, 2018
Messages
21,358
Hi. I could be way off, but to me, unless I am not understanding the question correctly, it would be better to use a deliberate action, such as the Click event of a Command Button when it comes to emailing something. If you use a form or control event to send out emails, then you could potentially send out numerous emails when only one would suffice. Of course, the drawback of using a deliberate event is that the user must consciously remember to perform the action. Just my 2 cents...
 

Mark_

Longboard on the internet
Local time
Today, 01:26
Joined
Sep 12, 2017
Messages
2,111
If you do use theDBGuy's suggestion, I'd add a field that would reflect that Outlook has been told, just so you can quickly identify if scheduled inspections have not been sent out. You can even get elaborate and make this a date that matches the "Scheduled date". If someone changes the Scheduled Date, you can then see if a new "Hey, its changed" notice needs to be sent.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:26
Joined
Feb 19, 2002
Messages
42,981
I would not use the AfterUpdate event of a control to send an email. What if the user cancels the update and doesn't save the changes?

I would use the AfterUpdate event of the form. However, during that event, the updates have already been applied so you have no way of telling which columns were changed. If you only want to send the email when the inspection date goes from null to not null, I would use the AfterUpdate event of that control to set a flag and then check the flag in the Form's AfterUpdate event.

Don't forget to reset the flag in the Current event so that it is cleared before any edits take place.
 

June7

AWF VIP
Local time
Today, 00:26
Joined
Mar 9, 2014
Messages
5,425
AfterUpdate of control could include code that commits record.

Always more than one way ...
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
Hi, as i said previously, it seems that the afterupdate event will work, in combination with a 'sent' flag. The actual test results are entered via a linked sub sub form in datasheet view. That way, the user can effectively navigate to that form by clicking the "little cross" that opens the sub sub form. I understand this feature only works in datasheet view. I'd really like that form to be displayed in continuous view as it provides me with better formatting options. Is there some way of doing this? By the way my vba code creates a task in outlook not an email, if that makes any difference.
 

isladogs

MVP / VIP
Local time
Today, 08:26
Joined
Jan 14, 2017
Messages
18,186
Hi, as i said previously, it seems that the afterupdate event will work, in combination with a 'sent' flag. The actual test results are entered via a linked sub sub form in datasheet view. That way, the user can effectively navigate to that form by clicking the "little cross" that opens the sub sub form. I understand this feature only works in datasheet view. I'd really like that form to be displayed in continuous view as it provides me with better formatting options. Is there some way of doing this? By the way my vba code creates a task in outlook not an email, if that makes any difference.

Are you referring to subdatasheets that can be opened/expanded by clicking on a small + sign next to the record selector? For example



If so, I would recommend you don't use them as
1. They can be confusing for end users
2. They each need to be loaded when your main form is loaded which causes a performance drain slowing down your database application.
A better approach is to use linked subforms
 

Attachments

  • Capture.PNG
    Capture.PNG
    14.9 KB · Views: 249

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
isladogs, yes exactly. I'll have a look @ linked forms. Cheers.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
OK, so now I have a mainform 'FRM_Equipment' & 2 subforms namely; 'FRM_Inspect_Record subform' (Linked Master & Child Fields 'EquipmentID') & 'FRM_Measurement' (this is where the actual measurements are recorded) rather than an embedded sub-sub form as before. I have created an unbound control on 'FRM_Equipment' called 'Link_Master' with control source =[FRM_Inspect_Record subform].[Form]![InspectID]. So when I select a record from the 'FRM_Inspect_Record subform' the InspectionID is replicated in 'Link_Master' of the main form. This works. But, I cannot link this pseudo Parent to the 'FRM_Measurement' to [InspectID]. I do not see the option of selecting 'link_Master' as the Parent. I bet I'm doing something stupid & obvious. Can you help?. Cheers
 

isladogs

MVP / VIP
Local time
Today, 08:26
Joined
Jan 14, 2017
Messages
18,186
If you are asking how to link master and child fields then go to the data tab of the properties sheet for the subform control in the main form.
Click on the ellipsis next to Link Master Fields and edit as appropriate



Normally Access will make an intelligent guess about what to link but you can adjust as needed.

If I've missed the point, a screenshot would be helpful
 

Attachments

  • Capture.PNG
    Capture.PNG
    13.2 KB · Views: 227

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
isladogs, I could not see the master field I required using the subform linker, but was able to manually add the links master to child. All seems good. Thanks for the prompt reply. The project continues....
 

isladogs

MVP / VIP
Local time
Today, 08:26
Joined
Jan 14, 2017
Messages
18,186
Excellent. Good luck with the next stage of your project
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:26
Joined
Feb 19, 2002
Messages
42,981
AfterUpdate of control could include code that commits record.
Not in my world. You really do not want to send email until AFTER the RECORD has actually been saved. The AfterUpdate event of the control takes place BEFORE the record is saved so there is always the possibility that the record will not actually get saved. Using the AfterUpdate event of the control is not equivalent to using the AfterUpdate event of the form. I wasn't just suggesting an alternative.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
So, Pat, i just put my code in the after update of the form instead?. It's simple to try. Appreciate it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:26
Joined
Feb 19, 2002
Messages
42,981
Yes, the AfterUpdate event of the form but as I said in my first response, you will need to use an internal flag to keep track of whether or not a date was changed since you cannot determine that information AFTER the record has been saved. You can set the flag in the control's AfterUpdate event or the Form's BeforeUpdate event. The code would be the same. You compare the data in the control to the .OldValue property.

Code:
If IsNull(Me.MyDate.OldValue) Then
    If IsDate(Me.MyDate) Then
       DateChangedFlg = True
    End If
End If
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:26
Joined
Apr 1, 2019
Messages
713
Pat, got it, will give it a go & let you know. Thanks & thanks to all for explaining the logic. I'm learning!!!!
 

Users who are viewing this thread

Top Bottom