CODEWITHSUNDEEP
ms-accessvbams-access-2010
ThatGuy723
ThatGuy723

Reputation: 23

Using recordset in function causes error 3420 in main sub code

I have a function that writes an entry into a table whenever a user makes a change to a record. However, after the function runs, I get an error 3420 'Object invalid or no longer' whenever I try to reference a Me.Recordset object or method in any part of my code. If I restart Access, the problem resets until that function runs again. What can I do to keep this from happening? Code is below:

    Function fAddAuditRecord(lngSysID As Long, intChangeType As Byte, Optional strComments As String, Optional intAttach As Integer, _
    Optional intNewStatus As Integer)
    Dim blnInTrans As Boolean
    On Error GoTo Err_handle
    Select Case intChangeType   'Select comments based on type of change
        Case 1
            strComments = "Record Added"
        Case 2                  
            strComments = "Record Edited" & strComments
        Case 3                  
            strComments = "Attachment " & intAttach & " Created"
        Case 4                  
            strComments = "Status Changed to " & intNewStatus
    End Select
    Dim WS As DAO.Workspace, sqlNewStep As String, dbNewStep As DAO.Database, intNewStep As Byte
    intNewStep = DCount("[StepNumber]", "tblAuditTrack", "[SystemIDCode] = " & lngSysID) + 1
    Set dbNewStep = CurrentDb
    Set WS = DBEngine.Workspaces(0)
    sqlNewStep = "INSERT INTO tblAuditTrack (SystemIDCode, StepNumber, InputUser, DateCreated, Comments, " _
        & "ChangeType) VALUES (" & lngSysID & ", " & intNewStep & ", '" & strUserName & "', Date(), '" _
        & strComments & "', " & intChangeType & ");"

    WS.BeginTrans
    blnInTrans = True
        dbNewStep.Execute sqlNewStep
    WS.CommitTrans
    blnInTrans = False
    dbNewStep.Close
    WS.Close

Upvotes: 2

Views: 790

Answers (1)

HansUp
HansUp

Reputation: 97101

It would probably help to see the other code where you're having a problem with Me.Recordset. But first see whether these changes improve the situation.

WS.CommitTrans
blnInTrans = False
'dbNewStep.Close
'WS.Close

Upvotes: 1

Related Questions