Solved VBA AfterUpdate event

[AlliCarr]

Hi,

I have a control on a form which has an AfterUpdate event which should automatically update another control. See below:

Private Sub Approval_AfterUpdate()
On Error GoTo ErrorHandler

'Passes financial year ID to grant record based on approval date
    If Me.Recommendation = "Approval" Then
        Me.FinancialYear = DLookup("FinancialYearID", "FinancialYearsT", "StartDate<=#" & _
        Me.Approval & "# AND EndDate>#" & Me.Approval & "#")
    
'Update payment status, payment amount, status and status date
        Me.PaymentStatus = "Awaiting authorisation"
        Me.PaymentAmount = Me![TotalRequested]
        Me.Status = "Decision"
        Me.StatusDate = Me![Approval]

    End If
    
'Update status and status date
    If Me.Recommendation = "Rejection" Then
        Me.Status = "Rejected"
        Me.StatusDate = Me![Approval]

    End If

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg

End Sub

The code should look up the financial year ID from the FinancialYears table and add this to the grant record but it doesn't always work and I can't see why. When approving, the approver adds in their name in a drop down and then the date of the approval in this control. Once those details are added they then have to click a save button. If whoever adds the date to the 'Approval' control is not pressing Enter or tabbing from the control before they press the save button could this prevent the AfterUpdate event from triggering?

Is there a better way to achieve what I need to achieve? I need to add the financial year into each grant to ensure that the funding comes from the correct years budget and is easily reported on.

 

[Gasman]

Have you walked the code?

 

[The_Doc_Man]

Changing the contents of a form in an AfterUpdate event is probably wrong, because you just set up a situation where you update, then immediately dirty a form in another place. Except that the "dirty" event doesn't occur after a VBA-originated change, but the form data has been changed and thus still needs saving.

Here is a link to show the order of event execution. It includes a section on the order of events when a control update event is involved, which is relevant to your question.

Order of events for database objects - Microsoft Support

support.microsoft.com

Events occur for the controls before events occur for the overall form. For general data safety, I would put any tests and updates and VBA-triggered data changes in a BeforeUpdate event. But that doesn't seem quite right for the operation you suggested. I.e. your business operational flow isn't consistent with order of events. It appears that you suddenly require to save data on the same form twice.

 

[theDBguy]

but it doesn't always work

When the financial year doesn't work, do the other changes, like status date, not work as well?

 

[AlliCarr]

When the financial year doesn't work, do the other changes, like status date, not work as well?

Never thought to check that! Looking at the affected records, yes, the other changes do work.

 

[AlliCarr]

Changing the contents of a form in an AfterUpdate event is probably wrong, because you just set up a situation where you update, then immediately dirty a form in another place. Except that the "dirty" event doesn't occur after a VBA-originated change, but the form data has been changed and thus still needs saving.

Here is a link to show the order of event execution. It includes a section on the order of events when a control update event is involved, which is relevant to your question.

Events occur for the controls before events occur for the overall form. For general data safety, I would put any tests and updates and VBA-triggered data changes in a BeforeUpdate event. But that doesn't seem quite right for the operation you suggested. I.e. your business operational flow isn't consistent with order of events. It appears that you suddenly require to save data on the same form twice.

Thanks @The_Doc_Man

Looking at the details in the link, the code should still work. (shouldn't it?)

When the approval data has been added and the save button is clicked, there is code to check whether the form is dirty (which it would be if the approval details have just been added) and changes dirty to false. So the after update event would trigger when the save button is clicked (as the Approval control loses the focus), and then the save button checks whether the form is dirty and changes dirty to false. Would this not also save the updated control that is completed when the after update event is triggered?

It's a little confusing as it does sometimes work but I'm struggling to identify when it works and when it doesn't.

 

[moke123]

Is there a better way to achieve what I need to achieve? I need to add the financial year into each grant to ensure that the funding comes from the correct years budget and is easily reported on.

Are you dealing with fiscal years?

Function GetFiscalYear(Dt As Variant, Optional StartMonth As Integer = 7) As Variant

    If Not IsDate(Dt) Then Exit Function
    
    If Month(Dt) >= StartMonth Then
        GetFiscalYear = (Year(Dt) + 1)
    Else
        GetFiscalYear = Year(Dt)
    End If
    
End Function

 

[theDBguy]

Never thought to check that! Looking at the affected records, yes, the other changes do work.

So, there might be something off with your DLookup() then. Maybe try another approach?

 

[Gasman]

What is your date format?

 

[AlliCarr]

So, there might be something off with your DLookup() then. Maybe try another approach?

Would this work better with a query? Or would it be worth adding a separate save button which just runs the DLookup?

 

[AlliCarr]

What is your date format?

The financial year data is input manually in the FinancialYears table. I add in the start date, end date and the financial year this relates to. So this year the start date is 01/04/2025, the end date is 31/03/2026 and the financial year format is 2025/2026. The Dlookup checks the approval date and if the date falls within these dates that financial year ID will be added to the grant.

This then works with other code and reports to track the budget throughout that financial year. So as a grant is paid, it subtracts from the budget and enables me to keep track of spend and make reporting on spend much easier.

Sorry for the lengthy response to a simple question but thought some context would help.

 

[Gasman]

I believe your date should be in mm/dd/yyyy format then?
That might explain why it appears to work sometimes.

 

[Mark_]

I would recommend checking your StartDate and EndDate also. What would you expect to happen for dates 31/3/2025 and 01/04/2026 for your period?

 

[moke123]

So your basically using a fiscal year 4/1/2025 to 3/31/2026. Does each grant have a different start date?

 

[The_Doc_Man]

I have to ask a somewhat complex question. With regard to your business requirements, you are doing multiple things on the form and from the description, these sequential actions are on different parts of the same record. That's OK, we've all done that at one time or another. However, there are implications for having to do multiple things while staying on the same record. There is only one Form_AfterUpdate event to run, yet you have updated different parts at different times.

Does your AfterUpdate event (or any other related event) take an internal action that might not be appropriate for all of the possible manual actions you could have just performed or are about to perform?

You might have to parse that question a couple of times to be sure you understand it. I'm thinking of an issue in automata theory having to do with being at the same apparent state more than once but from different "logic paths" that would SUGGEST the need for a formal "state tracker" or "state differentiator" of some sort.

As a simple-minded example, I had a form for one of my Navy projects where once I called up a record, I could delete it, edit/update it, file a report on it, pass through it (i.e. navigate away from it),... or cancel an edit. But if I had just navigated to that record and had not changed anything, the UPDATE button had to be disabled because of logging and reporting requirements. If I HAD just edited a control content, the CLOSE and navigate options weren't permitted though UNDO was allowed. But in each case I had not moved away from the record YET. The form had states that defined what I could and couldn't do with it at that moment. There were states where certain "next options" weren't available and I had to make the automation behind the form recognize those states. It had to avoid any computations that weren't ready to be performed quite yet, for example.

I'm thinking that an analogous situation might apply here and that might explain why things work sometimes and don't work other times. It's not the same, but it could be something similar based on what the form does and what it COULD do next. I hope that makes sense to you.

 

[Isaac]

Hi,

I have a control on a form which has an AfterUpdate event which should automatically update another control. See below:

Private Sub Approval_AfterUpdate()
On Error GoTo ErrorHandler

'Passes financial year ID to grant record based on approval date
    If Me.Recommendation = "Approval" Then
        Me.FinancialYear = DLookup("FinancialYearID", "FinancialYearsT", "StartDate<=#" & _
        Me.Approval & "# AND EndDate>#" & Me.Approval & "#")
 
'Update payment status, payment amount, status and status date
        Me.PaymentStatus = "Awaiting authorisation"
        Me.PaymentAmount = Me![TotalRequested]
        Me.Status = "Decision"
        Me.StatusDate = Me![Approval]

    End If
 
'Update status and status date
    If Me.Recommendation = "Rejection" Then
        Me.Status = "Rejected"
        Me.StatusDate = Me![Approval]

    End If

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg

End Sub

The code should look up the financial year ID from the FinancialYears table and add this to the grant record but it doesn't always work and I can't see why. When approving, the approver adds in their name in a drop down and then the date of the approval in this control. Once those details are added they then have to click a save button. If whoever adds the date to the 'Approval' control is not pressing Enter or tabbing from the control before they press the save button could this prevent the AfterUpdate event from triggering?

Is there a better way to achieve what I need to achieve? I need to add the financial year into each grant to ensure that the funding comes from the correct years budget and is easily reported on.

While that may be a fairly common way to do it, you could also simplify and avoid worrying about code by just putting a conditional expression in the Properties...In fact even if it were a VBA function, (which it need not be, but if it was), I would say that would be an easier way to encapsulate your code and consider all contingencies compared to using AfterUpdate, if it's getting fairly complex.

 

[Isaac]

My bad, I thought OP was using the control's AfterUpdate event, (and I still think that, after re-reading their verbiage) ... which IS a common way to populate other controls, but I suggest considering the property window as well in that case.

 

[Isaac]

I think he's use a control's AfterUpdate event also but when you manipulate a control using VBA, that control's events don't fire so that may be what is causing the disconnect. In that case, the code needs to move to the form's BeforeUpdate event or you need to do something like separate the code you want to force to run into a separate procedure and call it from the two events.

this is one thing I hate when going in between access and excel vba. in excel, programmatically modifying a control does fire its events, unless you set Application.EnableEvents=False (then set back to true afterwards), gets really thorny!
so it's an annoying difference and i wonder why MS did that differently between the two

 

[Gasman]

Different teams. Left hand not knowing what the right hand is doing.

 

[The_Doc_Man]

so it's an annoying difference and i wonder why MS did that differently between the two

That might be the wrong question. Access and Excel were "acquired" by Microsoft back in the really early days before Office was a product. MS was NOTORIOUS about co-opting other programs. (Legally, in that they bought out small designer companies who were just jumping on the PC bandwagon.) Different companies originated the designs of those two programs. The REAL question is why MS didn't "unify" the design. Possible answer: Cheaper not to.