Solved Run time error 3021

[Kayleigh]

Hi,
Trying to write code to copy records which involve child tables. But when run it brings up error 3021. Can anyone spot where my error is please?
This is code of function and have enclosed part of database.

Private Sub cmdCopySession_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSQL As String    'SQL statement.
    Dim strSql2 As String
    Dim lngID As Long       'Primary key value of the new record.
    Dim intSDID As Long
    Dim intSDTID As Long
    Dim rstSD As DAO.Recordset
    Dim rstSDU As DAO.Recordset
    Dim rstSDT As DAO.Recordset
    Dim rstSDTU As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL3 As String
    Dim strSQL4 As String
    Dim intOLDSDID As Long
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !fldTermID = (Me.fldTermID + 1)
                !fldSubjectID = Me.fldSubjectID
                !fldLessonName = Me.fldLessonName
                !fldStaffID = Me.fldStaffID
                !fldAuxiliaryStaffID = Me.fldAuxiliaryStaffID
                !fldAuxiliaryStaff2ID = Me.fldAuxiliaryStaff2ID
                !fldClassID = Me.fldClassID
                !fldRoomID = Me.fldRoomID
                !fldPrivateLesson = Me.fldPrivateLesson
                !fldNote = Me.fldNote
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !fldSessionID
       Debug.Print "New Session ID" & lngID
            'Duplicate the related records: append query.
            
            strSQL3 = "SELECT * FROM [jtblSessionWeekday] WHERE [fldSessionID] = " & Me.fldSessionID
            Set db = CurrentDb
            Set rstSD = db.OpenRecordset(strSQL3)
            Set rstSDU = db.OpenRecordset("jtblSessionWeekday")
            
            
            rstSD.MoveFirst
            While Not rstSD.EOF
            
            intOLDSDID = rstSD!fldSessionDayID
            rstSDU.AddNew
                rstSDU!fldSessionID = lngID
                rstSDU!fldWeekdayID = rstSD!fldWeekdayID
                intSDID = rstSDU!fldSessionDayID
                    
                        Debug.Print "SDID    " & intSDID
                rstSDU.Update
                

                        
                        
                        
                    strSQL4 = "SELECT * FROM [jtblSessionDayTimes] WHERE [fldSessionDayID] = " & intOLDSDID
                    Set rstSDT = db.OpenRecordset(strSQL4)
                    Set rstSDTU = db.OpenRecordset("jtblSessionDayTimes")
                      
                            rstSDT.MoveFirst
                            
                           Debug.Print "SDID " & intSDID
                            While Not rstSD.EOF
                                  
                                    rstSDTU.AddNew
                                       rstSDTU!fldSessionDayID = intSDID
                                      rstSDTU!fldStart = rstSDT!fldStart
                                      rstSDTU!fldEnd = rstSDT!fldEnd
                                       intSDTID = rstSDTU!fldSessionDayTimesID
                                  rstSDTU.Update
                
                            
                    
                                rstSDT.MoveNext
                    
                            Wend

                        
                        
NextMove:
                        
                rstSD.MoveNext
                
            Wend
              
                rstSD.Close
                rstSDU.Close
                
                Set db = Nothing
                Set rstSDT = Nothing
                Set rstSDTU = Nothing
                Set rstSD = Nothing
                Set rstSDU = Nothing
            
            Debug.Print Me.lstCurrentStudents.ListCount
            'If Me.lstCurrentStudents.ListCount > 0 Then
            '    strSql2 = "INSERT INTO [jtblStudentSession] ( fldSessionID, fldStudentID ) " & _
             '       "SELECT " & lngID & " As NewID, fldStudentID " & _
              '      "FROM [jtblStudentSession] WHERE fldSessionID = " & Me.fldSessionID & ";"
              '  DBEngine(0)(0).Execute strSQL, dbFailOnError
            'Else
            '    MsgBox "Main record duplicated, but there were no related records."
            'End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        
    End With
    End If
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdCopySession_Click"
    Resume Exit_Handler
End Sub

 

[The_Doc_Man]

First, when reporting an error, you need to give us the text of the error as well as the number.
Second, does the debugger highlight a particular line?

When I look up error 3021, it says "No Current Record" - which means that one of your recordsets didn't work as intended. I.e. you told it to find a record for you but it didn't and yet you didn't test for/detect that failure. When you proceeded to actually try to USE the non-responsive recordset, you tripped over it.

If you allow the debugger to stop you at the failing line, you could check the status of each recordset to see which one is not responsive. If you stop at a line that involves a recordset operation, the recordset used in that line is the one that is failing.

 

[Kayleigh]

Here is my DB. See function for 'copy session for new term'.
Have used debugger many times and tried to spot where the recordset is unresponsive but have not managed to pinpoint where it is going wrong

 

[CJ_London]

not sure what your problem is with the debugger - clearly highlights where the problem exists


While Not rstSD.EOF

rstSDTU.AddNew
rstSDTU!fldSessionDayID = intSDID
rstSDTU!fldStart = rstSDT!fldStart
rstSDTU!fldEnd = rstSDT!fldEnd
intSDTID = rstSDTU!fldSessionDayTimesID
rstSDTU.Update


rstSDT.MoveNext

and it occurs because you move to the next rstSDT record and there isn't one. Suspect that your movenext is not moving the same recordset referenced on your while line

 

[Kayleigh]

Thanks for pointing that out. I tried taking out

rstSDT.MoveNext

but then it loops continuously creating infinite repetitive records. How can I change that?

 

[CJ_London]

Your code isn't documented, so not sure what you are actually doing, and don't have the time to try figure it out.

from my previous post I would expect either

rstSDT.MoveNext should be rstSD.MoveNext

or

While Not rstSD.EOF should be While Not rstSDT.EOF

or perhaps there needs to be another While loop?

 

[Kayleigh]

Sorted
Thanks