Answered by:
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 BooleanrecExist = True
Set db = CurrentDbSet 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 IfIf recExist = False ThenSet 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.CommitTransrstHouseStudents.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
ElseDoCmd.OpenForm "frmHouseStudents", acViewNormal, acEdit
End IfEnd SubSunday, 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
- Edited by Daniel Pineault (MVP)MVP Monday, August 7, 2017 1:07 AM Minor Code Cleanup
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