none
Runtime error 3420 RRS feed

  • Question

  • I am getting the error message Run-time error '3420' Object invalid or no longer set when I run this code. The debugger stops on the line which is underlined. Could someone help me to sort this problem out.

    Private Sub cmdSubmit_Click()
    Dim db As Database
    Dim qdf1 As QueryDef
    Dim qdf2 As QueryDef
    Dim qdf3 As QueryDef
    Dim rstStudents As Recordset
    Dim rstHouseStudents As Recordset
    Dim rstAssignStudents As Recordset
    Dim rstClassHouseStudents As Recordset
    Dim myWS As Workspace
    Dim recExist As Boolean
    recExist = True
    Set db = CurrentDb
    Set qdf1 = db.QueryDefs("qryYearClassStudents1")
    Set qdf2 = db.QueryDefs("qryYearClassHouseStudents")
    Set qdf3 = db.QueryDefs("qryHouseStudents")
    qdf1.Parameters(0) = Me.cboClass
    qdf2.Parameters(0) = Me.cboClass
    qdf3.Parameters(0) = Me.cboClass
    Set rstClassHouseStudents = qdf2.OpenRecordset()

    If rstClassHouseStudents.RecordCount = 0 Then
        recExist = False
    End If
    If recExist = False Then
        Set rstStudents = qdf1.OpenRecordset()
        Set rstHouseStudents = db.OpenRecordset("tblHouseStudents", dbOpenDynaset)
        Set myWS = DBEngine.Workspaces(0)
        myWS.BeginTrans
       
        Do Until rstStudents.EOF
            rstHouseStudents.FindFirst "[StudentID] = " & rstStudents!StudentID
            If rstHouseStudents.NoMatch Then
                rstHouseStudents.AddNew
                rstHouseStudents![StudentID] = rstStudents!StudentID
                rstHouseStudents![YearClassID] = rstStudents!YearClassID
                rstHouseStudents![HouseID] = Null
                rstHouseStudents.Update
                rstStudents.MoveNext
            Else
                rstStudents.MoveNext
            End If
        Loop
       
        myWS.CommitTrans
        rstHouseStudents.Close
        rstStudents.Close
       
        myWS.Close
        qdf1.Close
       
        Set rstHouseStudents = Nothing
        Set rstStudents = Nothing
       
        Set myWS = Nothing
        Set qdf1 = Nothing
       
       
    Else
        rstClassHouseStudents.Close
        qdf2.Close
        Set rstClassHouseStudents = Nothing
        Set qdf2 = Nothing
        MsgBox "Students already assigned", vbInformation + vbOKOnly, "Message"
       
    End If

    Set rstAssignStudents = qdf3.OpenRecordset()
    If rstAssignStudents.RecordCount = 0 Then
        MsgBox "Students already assigned", vbInformation + vbOKOnly, "Message"
        rstAssignStudents.Close
        Set rstAssignStudents = Nothing
        qdf3.Close
        Set qdf3 = Nothing
    Else
        DoCmd.OpenForm "frmHouseStudents", acViewNormal, acEdit
       
    End If
    End Sub

    Sunday, August 6, 2017 11:22 PM

Answers

  • I imagine this is your immediate issue:

    Set myWS = DBEngine.Workspaces(0)
    
    ' ...
    
    myWS.Close

    You close the workspace that Access is using, so naturally all the objects that depend on it are no longer valid.

    You didn't open DBEngine.Workspaces(0), you only got a reference to the existing workspace that Access is using, so you shouldn't close it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Gordon Swan Tuesday, August 8, 2017 10:21 AM
    Monday, August 7, 2017 10:39 PM

All replies

  • Add an error handler and line numbering and let see what happens:

    Private Sub cmdSubmit_Click()
    10        On Error GoTo Error_Handler
              Dim db                    As DAO.Database
              Dim qdf1                  As DAO.QueryDef
              Dim qdf2                  As DAO.QueryDef
              Dim qdf3                  As DAO.QueryDef
              Dim rstStudents           As DAO.Recordset
              Dim rstHouseStudents      As DAO.Recordset
              Dim rstAssignStudents     As DAO.Recordset
              Dim rstClassHouseStudents As DAO.Recordset
              Dim myWS                  As DAO.Workspace
    
    20        Set db = CurrentDb
    30        Set qdf1 = db.QueryDefs("qryYearClassStudents1")
    40        Set qdf2 = db.QueryDefs("qryYearClassHouseStudents")
    50        Set qdf3 = db.QueryDefs("qryHouseStudents")
    
    60        qdf1.Parameters(0) = Me.cboClass
    70        qdf2.Parameters(0) = Me.cboClass
    80        qdf3.Parameters(0) = Me.cboClass
    90        Set rstClassHouseStudents = qdf2.OpenRecordset()
    100       If rstClassHouseStudents.RecordCount = 0 Then
    110           Set rstStudents = qdf1.OpenRecordset()
    120           Set rstHouseStudents = db.OpenRecordset("tblHouseStudents", dbOpenDynaset)
    130           Set myWS = DBEngine.Workspaces(0)
    140           myWS.BeginTrans
    
    150           Do Until rstStudents.EOF
    160               rstHouseStudents.FindFirst "[StudentID] = " & rstStudents!StudentID
    170               If rstHouseStudents.NoMatch Then
    180                   rstHouseStudents.AddNew
    190                   rstHouseStudents![StudentID] = rstStudents!StudentID
    200                   rstHouseStudents![YearClassID] = rstStudents!YearClassID
    210                   rstHouseStudents![HouseID] = Null
    220                   rstHouseStudents.Update
    230               End If
    240               rstStudents.MoveNext
    250           Loop
    
    260           myWS.CommitTrans
    270       Else
    280           MsgBox "Students already assigned", vbInformation + vbOKOnly, "Message"
    290       End If
    
    300       Set rstAssignStudents = qdf3.OpenRecordset()
    310       If rstAssignStudents.RecordCount = 0 Then
    320           MsgBox "Students already assigned", vbInformation + vbOKOnly, "Message"
    330       Else
    340           DoCmd.OpenForm "frmHouseStudents", acViewNormal, acEdit
    350       End If
    
    Error_Handler_Exit:
    360       On Error Resume Next
    370       If Not rstClassHouseStudents Is Nothing Then
    380           rstClassHouseStudents.Close
    390           Set rstClassHouseStudents = Nothing
    400       End If
    410       If Not rstAssignStudents Is Nothing Then
    420           rstAssignStudents.Close
    430           Set rstAssignStudents = Nothing
    440       End If
    450       If Not rstHouseStudents Is Nothing Then
    460           rstHouseStudents.Close
    470           Set rstHouseStudents = Nothing
    480       End If
    490       If Not rstStudents Is Nothing Then
    500           rstStudents.Close
    510           Set rstStudents = Nothing
    520       End If
    530       If Not myWS Is Nothing Then
    540           myWS.Close
    550           Set myWS = Nothing
    560       End If
    570       If Not qdf1 Is Nothing Then
    580           qdf1.Close
    590           Set qdf1 = Nothing
    600       End If
    610       If Not qdf2 Is Nothing Then
    620           qdf2.Close
    630           Set qdf2 = Nothing
    640       End If
    650       If Not qdf3 Is Nothing Then
    660           qdf3.Close
    670           Set qdf3 = Nothing
    680       End If
    690       If Not db Is Nothing Then Set db = Nothing
    700       Exit Sub
    
    Error_Handler:
    710       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                     "Error Number: " & Err.Number & vbCrLf & _
                     "Error Source: cmdSubmit_Click" & vbCrLf & _
                     "Error Description: " & Err.Description & _
                     Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                     , vbOKOnly + vbCritical, "An Error has Occured!"
    720       Resume Error_Handler_Exit
    End Sub

    Also, a couple things stike me as odd:

    • You're using transactions, but have no rollback?
    • In the line  rstAssignStudents.RecordCount = 0 the check seems inversed, no?  0 = Students already assigned?

    Obviously, only you know, but it just seems odd.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Monday, August 7, 2017 1:01 AM
  • I imagine this is your immediate issue:

    Set myWS = DBEngine.Workspaces(0)
    
    ' ...
    
    myWS.Close

    You close the workspace that Access is using, so naturally all the objects that depend on it are no longer valid.

    You didn't open DBEngine.Workspaces(0), you only got a reference to the existing workspace that Access is using, so you shouldn't close it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Gordon Swan Tuesday, August 8, 2017 10:21 AM
    Monday, August 7, 2017 10:39 PM
  • Thank you for the assistance. I am working on it,
    Tuesday, August 8, 2017 10:19 AM
  • Thank you. I now realise what the problem is. I made the correction and the code is running without the error.
    Tuesday, August 8, 2017 10:21 AM