0

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) Error 2467

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.

JuniperSquared
  • 143
  • 1
  • 12
  • Why not run an update query instead of looping? `Update roster where division =x and unit =y` Not sure if your recordset is changing as you perform your update. If you update the recordset after the loop does it still happen? – Nathan_Sav Mar 24 '22 at 14:30
  • The recordset is changing as it's written. The code *does* work as intended. Even when the intermittent bug pops up. But I can move the Update line to after Loop and see what happens. I haven't thought about an update query because x and y could be anything. The values come from data entry on a form. – JuniperSquared Mar 24 '22 at 14:36
  • I moved the Update line to after "Loop". Code still works. I'm going to have this tested. If this fixes it, I'll post answer so the question doesn't linger. This might take a day or 2 - depending on how long confirmation testing takes. Thanks! – JuniperSquared Mar 24 '22 at 14:42
  • I did more testing (multiple impacted records vs 1), and moving the "Update" line actually breaks the code when EOF is true. Error 3021: "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. I had to move the "update" line back within the Loop - which fixed the 3021 Error. Sorry, this didn't work for me.... – JuniperSquared Mar 24 '22 at 14:59
  • 1
    `update roster set divisionid='" & Me.Division.Value & "' and unitid='" & Me.Unit.Value & "' where DivisionID = '" & s1 & "' and UnitID = '" & s2 & "'"` – Nathan_Sav Mar 24 '22 at 15:54
  • In the future you might want to add line numbers to this code and update your error handler to display the line number causing the error (or at least adding MsgBox("Error at line " & Erl) in the ErrCond sub. This way at least you'll know exactly which object is getting de-referenced. See https://www.fmsinc.com/free/newtips/VBA/ErrorHandling/LineNumber.html for more. – deluxeinformation Mar 24 '22 at 17:45
  • I think something got corrupted. I was testing, everything was working. Now, all changes are being saved in the table as "-1" – JuniperSquared Mar 24 '22 at 18:05
  • I'm pulling from backups and starting over.... – JuniperSquared Mar 24 '22 at 18:10
  • 1
    There's a lot of extraneous stuff in this code, i.e. the arguments passed in aren't used, variables declared but not used, unnecessary use of 'new' keyword. This could be rewritten much more reliably (and almost undoubtedly would operate faster) using a single SQL update statement as @Nathan_Sav noted, e.g. DBEngine(0)(0).Execute "update...." provided ROSTER is a linked table in your FE. – deluxeinformation Mar 24 '22 at 21:39
  • I've tried this over and over, and something in this keeps breaking. I'm going to update original post. Not enough characters in comments box for this. – JuniperSquared Mar 28 '22 at 18:47

0 Answers0