I have an MS Access db split FE/BE on a network file share, that holds employee "Roster" info - Name, Division, Unit, Work location etc.

I recently added a simpler feature for updating "DivisionUnit" than what existed previously.

I also added code that - when "DivisionUnit" table records are updated - loops through Employee "Roster" table records and changes all impacted rows. This had to be done through code because "DivisionUnit" table was never linked to "Roster" table - which would have facilitated cascading updates.

Here's the Edit Code:

Public Sub EditDivision(aDivisionName As String, aUnitName As String)
    Const SUB_NAME As String = "EditDivision"
    Dim t As TTracking
    Dim rsTarget As ADODB.Recordset
    Set rsTarget = New ADODB.Recordset
    Dim con As ADODB.Connection
    Dim strTarget As String
    Dim res As Variant
    On Error GoTo ErrCond
    
    s1 = Me.Division.OldValue
    s2 = Me.Unit.OldValue
    

    Set con = New ADODB.Connection
    Set con = CurrentProject.Connection
    Set rsTarget = New ADODB.Recordset

     
    strTarget = "SELECT * from ROSTER where DivisionID = '" & s1 & "' and UnitID = '" & s2 & "'"

    rsTarget.Open strTarget, con, adOpenDynamic, adLockOptimistic
        With rsTarget
            Do Until .EOF
                'r1.EditMode
                'rsTarget.Update
                    rsTarget!DivisionID = Me.Division.Value
                    rsTarget!UnitID = Me.Unit.Value
                rsTarget.Update
                rsTarget.MoveNext
                DoEvents
            Loop
        End With
    Exit Sub:
    con.Close
    rsTarget.Close
    Set con = Nothing
    Set rsTarget = Nothing
    Me.Refresh
    Exit Sub
ErrCond:
    EventLogging AppSession.UserName, MSG_TYPE_ERROR, Err.Number, Err.Description, MOD_NAME & "." & SUB_NAME, AppSession.AppSilent
End Sub

In the above code, s1 and s2 are Public variables declared as String

The problem: There has been a persistent - intermittent - bug: (If the picture doesn't come through, it's Error 2467 "The expression you entered refers to an object that is closed or doesn't exit)

This is not the first time - or the first application on this share - that has generated this intermittent bug. Closing and reopening the object has consistently fixed the issue in the past, and the bug disappears for months (or a year) on end. Unfortunately, I've been informed that that workaround is unacceptable, and a permanent solution needs to be found.

I've scoured the internet as best I can. Possible causes and solutions are vague and all over the place - Graphics card, conflicting programs, anti-virus, etc.

How can I fix this intermittent issue?

I'm wondering if using DAO vs ADO is the solution here? And, if it is, we're soon moving to SQL Server backend. Will this bug return when I change code back from DAO to ADO for the purposes of connecting to SQL Server?

---

UPDATE 03/28/2022

Followed guidance to simplify the code, I am just running the following SQL Update statement

Public Sub EditDivision(aDivisionName As String, aUnitName As String)
    Const SUB_NAME As String = "EditDivision"
    Dim t As TTracking
    On Error GoTo ErrCond
    
    s1 = Me.Division.OldValue
    s2 = Me.Unit.OldValue
    
       
    DoCmd.RunSQL "UPDATE Roster SET DivisionID= '" & Me.Division.Value & "' and UnitID='" & Me.Unit.Value & "' WHERE DivisionID = '" & s1 & "' and UnitID='" & s2 & "'"      

    Me.Refresh
    Exit Sub
ErrCond:
    EventLogging AppSession.UserName, MSG_TYPE_ERROR, Err.Number, Err.Description, MOD_NAME & "." & SUB_NAME, AppSession.AppSilent
End Sub

Problem: In testing a change to Division, Division is being changed to -1

I can't even begin to explain HOW, when -1 is a boolean value. I will say that this application is overtly complicates. There is a lot of background VBA that runs through a host of permissions and table setting modules before a form even open.

I tried stepping through the code, and the SQL statement captured the values I WANTED to save to the table. But, once the code finishes running, -1 is what's saved instead.

I'm going to try and pause the code before it finishes running through the extraneous modules, and see if that helps. But I'm at a loss here.