If subroutine to get Now() value in date controls on parent form depending on an action on the subform

[MNewby]

I have a from called CAD_CallDispSplitF. As the name implies, it is a split form. The form has a subform named CAD_Log_DispF. Link Master Field is ID
Link Child Field is ID_Activity
The link works fine.

The record source for the subform is

SELECT CADLogT.ID, CADLogT.EntryDateTime, CADLogT.Notes, CADLogT.ActionID, CADLogT.Dispo, CADLogT.ID_Activity, CADLogT.EmployeeID, CADLogT.TourID, TourT.UnitAvailable, ActivityT.DispDateTime, ActivityT.EndingDateTime, ActivityT.BeginDateTime
FROM (CADLogT INNER JOIN TourT ON CADLogT.TourID = TourT.ID) LEFT JOIN ActivityT ON CADLogT.ID_Activity = ActivityT.ID;
 

I have this BeforeUpdate event on the subform.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")
 
    If IsNull([EntryDateTime]) Then
        [EntryDateTime] = Now()
    End If
 
    Select Case ActionID.Value
 
        Case Is = 1: UnitAvailable = 0
        Case Is = 2: UnitAvailable = 0
        Case Is = 3: UnitAvailable = 0
        Case Is = 4: UnitAvailable = 1
        Case Is = 6: UnitAvailable = 1
        Case Is = 7: UnitAvailable = 1
        Case Is = 8: UnitAvailable = 1
        Case Is = 10: UnitAvailable = 1
        Case Is = 24: UnitAvailable = 1
 
    End Select
 
    If IsNull([DispDateTime]) Then
        If ActionID = 1 Or 2 Then
                DispDateTime = Now()
        End If
    End If
 
    If IsNull([BeginDateTime]) Then
        If ActionID = 3 Then
                BeginDateTime = Now()
        End If
    End If
 
    If IsNull([EndingDateTime]) Then
        If ActionID = 6 Or 7 Or 8 Then
                EndingDateTime = Now()
        End If
    End If
 
 
 
 
 End Sub
 

Everything works fine up to the If syntax.

To spell it out in English, I will talk about only the first IF statement. What I want to happen is if the ActionID is 1 or 2 AND the DispDateTime field/control is empty, the the DispDateTime field/control should =Now(). If the DispDateTime field/control is not empty, then nothing should happen because I do not want it overwritten with a new date and time. If anyone can help me with the proper IF syntax, I can probably apply it to the other following IF statements. Thanks in advance..... Mark

[NeoPa]

Hi Mark.

I told you already what to expect if you try to update a record from within the Form_BeforeUpdate() event procedure. In fact I told you once then reminded you later.

Unless you're very careful with you logic you will get into an interminable loop as the very procedure you're running will be triggered again when you do an update.

However, at this stage you're just looking at the VBA syntax which is wrong on line #36 of your posted code. The appropriate syntax is :

        If ActionID = 6 _
        Or ActionID = 7 _
        Or ActionID = 8 Then

[MNewby]

NeoPa, That corrected code worked. I know that you cautioned me about using the BeforeUpdate event, but when I put it in the AfterUpdate event, it locks up, and I must hit ESC to get it to release. When I have it in the BeforeUpdate, it works without a hitch.

So if I should not put it in the BeforeUpdate, where should I put it?

Mark

[NeoPa]

Mark:
So if I should not put it in the BeforeUpdate, where should I put it?

To be fair Mark, if it's working OK in the Form_BeforeUpdate() Event procedure then it looks like your logic has avoided the problem.

Normally, and I actually did earlier, I'd suggest to use the Form_AfterUpdate() one instead, but if you have problems with that perhaps we can leave it where it is and look into those problems another time.

[MNewby]

Thank you NeoPa.... I just want to do things the right way and I'm trying to learn how. Again thanks... you're the greatest. Problem solved.

[NeoPa]

Mark:
I just want to do things the right way and I'm trying to learn how.

That's music to my ears :-)

In short, if your Form_BeforeUpdate() routine updates the data in all circumstances then you have an interminable loop. The update triggers the Form_BeforeUpdate() routine. When it runs that triggers a further update. That triggers the Form_BeforeUpdate() routine again - which triggers another update, and on and on ad infinitum. Bear in mind the first requested update is never completed.

Access continues to follow the process until it exhausts all the resources available and crashes unceremoniously. Not pretty. Worth avoiding at all costs.

To be fair, if the logic is wrong you can get a similar loop even if you allow the update to complete before requesting a new one, but at least that doesn't crash Access and/or Windows. Make sure your logic doesn't depend on the update having completed if it's in the Form_BeforeUpdate() routine.

[MNewby]

Thank you for the explanation. Hopefully I can avoid that problem in the future. Have a great weekend....although I will be working on this LOL

[NeoPa]

Hi Mark.

I'm around most of the weekend so feel free to drop a new question in if you need to. Not all such work is limited to working hours of course ;-)