Runtime error 3420

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

Answer 1

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

Answer 2

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

Answer 3

Thank you for the assistance. I am working on it,

Answer 4

Thank you. I now realise what the problem is. I made the correction and the code is running without the error.