Suggestions on why I might be getting this error on a beforeupdate event

[templeowls]

I have a multi user DB and added in a ChangeLog. I am tracking changes made to one particular field in one of the forms.




Here's the function:

Function ChangeLog(lngID As Long, Optional strField As String = "", Optional strChangeSummary As String = "")
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String
    
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = CurrentDb.OpenRecordset("tblChangeLog", dbOpenDynaset, dbSeeChanges)
    
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !Fieldname = strControlName
        Else
            !Fieldname = strField
        End If
        !EventNumber = lngID
        !Change = strChangeSummary
        !Username = Username()
        !StaffMemberName = StaffName()
        !TimeStamp = Now()
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With


    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

I then call it in the form field's before update event:

Private Sub txtFollowUp_BeforeUpdate(Cancel As Integer)
    
    Call ChangeLog(ComplaintNumber, "FollowUp", "Updated complaint follow-up")


End Sub

It's working well for me, but two users have had both had an error message pop up which states "The expression Before Update you entered as the event property setting produced the following error: The expression you entered requires the control to be in the active window."

I'm not sure what that means. Idk why the expression wouldn't be in the active window. I also haven't gotten this error myself so it's difficult to triage. The users state that they change the field's value and go to save, and then that error pops up

Any suggestions on what the issue might be?

[CJ_London]

suggest look over their shoulder to see what they are actually doing. Perhaps the save button is in a different window.

Also check your vba tools>options>General tab - make sure the 'break in class module' option is ticked - code should then stop in the changlog function so you can see which line is causing the error, rather than where it is called.

it is probably the screen.activecontrol call and somehow the user has clicked away from the form before clicking save but it could be something else.

Not sure why you need to use screen.activecontol, why not just pass the control in the parameters. In fact I'm surprised it works since clicking the save button makes that the active control

[templeowls]

suggest look over their shoulder to see what they are actually doing. Perhaps the save button is in a different window.

Also check your vba tools>options>General tab - make sure the 'break in class module' option is ticked - code should then stop in the changlog function so you can see which line is causing the error, rather than where it is called.

it is probably the screen.activecontrol call and somehow the user has clicked away from the form before clicking save but it could be something else.

Not sure why you need to use screen.activecontol, why not just pass the control in the parameters. In fact I'm surprised it works since clicking the save button makes that the active control

What would you suggest changing the activecontrol to in the code?

[CJ_London]

Personally I would try to understand how and why the error is happening

but the code change would be

Function ChangeLog(ctl as control, lngID As Long, Optional strField As String = "", Optional strChangeSummary As String = "")

the replace all instances in the function of screen.activecontrol with ctl

this line

strFormName = Screen.ActiveForm.Name

would be replaced with


strFormName =ctl.parent.Name

[templeowls]

Personally I would try to understand how and why the error is happening

but the code change would be

Function ChangeLog(ctl as control, lngID As Long, Optional strField As String = "", Optional strChangeSummary As String = "")

the replace all instances in the function of screen.activecontrol with ctl

this line

strFormName = Screen.ActiveForm.Name

would be replaced with


strFormName =ctl.parent.Name

That worked! And it fixed the issue (I think). I experienced the error myself shortly ago and I think it was the activecontrol issue. I did a spell check on the field which opened up the spell check window and then I got that error

Thanks!

[templeowls]

Personally I would try to understand how and why the error is happening

but the code change would be

Function ChangeLog(ctl as control, lngID As Long, Optional strField As String = "", Optional strChangeSummary As String = "")

the replace all instances in the function of screen.activecontrol with ctl

this line

strFormName = Screen.ActiveForm.Name

would be replaced with


strFormName =ctl.parent.Name

So one last question after a few days of testing. This works well except for the "strFormName =ctl.parent.Name" line.

Because the field that it being tracked is in a tabcontrol, the ctl.parent.name is returning the name of the tab control and not the form.

Any suggestions on how to change that?

[Gicu]

Just add the form name as another (optional if you want) argument of the function and pass Me.Name to it. If you make it optional for the cases where you don't pass it use your old ActiveForm.Name.

Cheers,