Run-Time Error '3265': Item Not Found In This Collection

[Voodeux2014]

Private Sub cmdSaveClass_Click()

Dim mydb As DAO.Database
Dim rst As DAO.Recordset
Dim strCheck As String
Dim msgboxResponse As Integer


Set mydb = CurrentDb


Set rst = mydb.OpenRecordset("tbl2ClassTaken", dbOpenDynaset, dbAppendOnly)


With rst
For x = 0 To Me.lstSelectedStudents.ListCount - 1
Me.lstSelectedStudents.Selected(x) = True


strCheck = DCount("ClassID", "tbl2ClassTaken", "ApprovedClassID=" & Me.cboClassSelection & " AND StudentID=" & Me.lstSelectedStudents.ItemData(x))


If strCheck = 0 Then
    GoTo CheckEC
Else
    GoTo AlreadyTaken
End If


AlreadyTaken:


    msgboxResponse = MsgBox(lstSelectedStudents.Column(1) & " has already taken this class. Would you still like to add it?", vbYesNo, "Class Already Taken")
        
    If msgboxResponse = vbYes Then
        GoTo CheckEC
    Else
        GoTo ContinueLoop
    End If
    
CheckEC:


    If Me.chkEC = True Then
        strClassName = DLookup("ClassName", "tbl1Classes", "ClassID=" & Me.txtClassID)
        strClassLength = DLookup("SuggestedLength", "tbl1Classes", "ClassID=" & Me.txtClassID)
        
        Set rst = Nothing
        Set rst = mydb.OpenRecordset("tbl1Classes", dbOpenDynaset, dbAppendOnly)
            .AddNew
            ![ClassName] = strClassName & " (EC)"
            ![SuggestedLength] = strClassLength
            .Update
            
        strNewClassID = DMax("ClassID", "tbl1Classes")
                
        Set rst = mydb.OpenRecordset("tbl3ApprovedGrantClasses", dbOpenDynaset, dbAppendOnly)
            .AddNew
            ![GrantID] = [Forms]![frmMain]![cboGrant]
            ![TrainerID] = [Forms]![frmMain]![lstAuthorizedTrainers].ItemSelected(x)
            ![ClassID] = strNewClassID
            ![CurSuggestedHours] = txtCurSuggestedHours
            ![CostPerStudent] = InputBox("How much would you like to charge to budget?", "Budget Info")
            ![CostPerCourse] = 0
            .Update
            
        strNewApprovedClassID = DMax("ApprovedClassID", "tbl3ApprovedGrantClasses")
        
        Set rst = mydb.OpenRecordset("tbl2ClassTaken", dbOpenDynaset, dbAppendOnly)
            .AddNew
            ![StudentID] = lstSelectedStudents.ItemData(x)
            ![ApprovedClassID] = strNewApprovedClassID
            ![ClassID] = strNewClassID
            ![ClassDate] = txtClassDate
            ![HoursTaken] = txtClassHours
            ![EmployerContribution] = chkEC
            .Update
        
        GoTo ContinueLoop
    Else
        GoTo AddClassTaken
    End If
    
AddClassTaken:
        .AddNew
        ![StudentID] = lstSelectedStudents.ItemData(x)
        ![ApprovedClassID] = cboClassSelection
        ![ClassID] = ClassID
        ![ClassDate] = txtClassDate
        ![HoursTaken] = txtClassHours
        ![EmployerContribution] = chkEC
        .Update
        
ContinueLoop:


Next


End With


DoCmd.Close acForm, "frmAddClassRoster"


rst.Close
Set rst = Nothing
mydb.Close
Set mydb = Nothing


DoCmd.Maximize
End Sub

The error is on any lines that start with ![FieldName] = DesiredResult



It appears that it is not changing the Recordset that it is looking into.

Not sure how to correct this issue, or if it is even possible to work within multiple Recordsets. Any help will be greatly appreciated.

[ranman256]

'3265' from your code, prob. means you don't have that field name spelled correctly.

Also looking at the code, it seems you did this the hard way by using code. This could have been accomplished using queries, not code.

[Voodeux2014]

Dim rs As DAO.RecordsetDim numGrantID As Double, numClassID As Double, numTrainerID As Double


numGrantID = Me.cboGrant
numProviderID = Me.lstAuthorizedTrainers


strSQL = "SELECT tbl1Classes.ClassID "
strSQL = strSQL & "FROM tbl1Classes INNER JOIN tbl3ApprovedGrantClasses ON tbl1Classes.ClassID = tbl3ApprovedGrantClasses.ClassID "
strSQL = strSQL & "WHERE tbl3ApprovedGrantClasses.GrantID = " & numGrantID & " AND tbl3ApprovedGrantClasses.TrainerID = " & numProviderID & " AND tbl3ApprovedGrantClasses.Unapproved = No;"


Set rs = CurrentDb.OpenRecordset(strSQL)


If rs.RecordCount > 0 Then
    rs.MoveFirst
    While rs.EOF = False
         rs.Edit
             rs("Unapproved") = Yes
         rs.Update
        rs.MoveNext
    Wend
End If


strSQL = "SELECT tbl1TrainingProviders.ProviderID "
strSQL = strSQL & "FROM tbl1TrainingProviders RIGHT JOIN tbl2GrantTrainers ON tbl1TrainingProviders.ProviderID = tbl2GrantTrainers.TrainerID "
strSQL = strSQL & "WHERE tbl2GrantTrainers.GrantID = " & numGrantID & " AND tbl2GrantTrainers.Approved = No;"


Set rs = CurrentDb.OpenRecordset(strSQL)


If rs.RecordCount > 0 Then
    rs.MoveFirst
    While rs.EOF = False
        rs.Edit
            rs("Approved") = Yes
        rs.Update
        rs.MoveNext
    Wend
End If


rs.Close
Set rs = Nothing

[JrMontgom]

Private Sub cmdSaveClass_Click()

Dim mydb As DAO.Database
Dim rst As DAO.Recordset
Dim strCheck As String
Dim msgboxResponse As Integer


Set mydb = CurrentDb


Set rst = mydb.OpenRecordset("tbl2ClassTaken", dbOpenDynaset, dbAppendOnly)


With rst
For x = 0 To Me.lstSelectedStudents.ListCount - 1
Me.lstSelectedStudents.Selected(x) = True


strCheck = DCount("ClassID", "tbl2ClassTaken", "ApprovedClassID=" & Me.cboClassSelection & " AND StudentID=" & Me.lstSelectedStudents.ItemData(x))


If strCheck = 0 Then
    GoTo CheckEC
Else
    GoTo AlreadyTaken
End If


AlreadyTaken:


    msgboxResponse = MsgBox(lstSelectedStudents.Column(1) & " has already taken this class. Would you still like to add it?", vbYesNo, "Class Already Taken")
        
    If msgboxResponse = vbYes Then
        GoTo CheckEC
    Else
        GoTo ContinueLoop
    End If
    
CheckEC:


    If Me.chkEC = True Then
        strClassName = DLookup("ClassName", "tbl1Classes", "ClassID=" & Me.txtClassID)
        strClassLength = DLookup("SuggestedLength", "tbl1Classes", "ClassID=" & Me.txtClassID)
        
        Set rst = Nothing
        Set rst = mydb.OpenRecordset("tbl1Classes", dbOpenDynaset, dbAppendOnly)
            .AddNew
            ![ClassName] = strClassName & " (EC)"
            ![SuggestedLength] = strClassLength
            .Update
            
        strNewClassID = DMax("ClassID", "tbl1Classes")
                
        Set rst = mydb.OpenRecordset("tbl3ApprovedGrantClasses", dbOpenDynaset, dbAppendOnly)
            .AddNew
            ![GrantID] = [Forms]![frmMain]![cboGrant]
            ![TrainerID] = [Forms]![frmMain]![lstAuthorizedTrainers].ItemSelected(x)
            ![ClassID] = strNewClassID
            ![CurSuggestedHours] = txtCurSuggestedHours
            ![CostPerStudent] = InputBox("How much would you like to charge to budget?", "Budget Info")
            ![CostPerCourse] = 0
            .Update
            
        strNewApprovedClassID = DMax("ApprovedClassID", "tbl3ApprovedGrantClasses")
        
        Set rst = mydb.OpenRecordset("tbl2ClassTaken", dbOpenDynaset, dbAppendOnly)
            .AddNew
            ![StudentID] = lstSelectedStudents.ItemData(x)
            ![ApprovedClassID] = strNewApprovedClassID
            ![ClassID] = strNewClassID
            ![ClassDate] = txtClassDate
            ![HoursTaken] = txtClassHours
            ![EmployerContribution] = chkEC
            .Update
        
        GoTo ContinueLoop
    Else
        GoTo AddClassTaken
    End If
    
AddClassTaken:
        .AddNew
        ![StudentID] = lstSelectedStudents.ItemData(x)
        ![ApprovedClassID] = cboClassSelection
        ![ClassID] = ClassID
        ![ClassDate] = txtClassDate
        ![HoursTaken] = txtClassHours
        ![EmployerContribution] = chkEC
        .Update
        
ContinueLoop:


Next


End With


DoCmd.Close acForm, "frmAddClassRoster"


rst.Close
Set rst = Nothing
mydb.Close
Set mydb = Nothing


DoCmd.Maximize
End Sub

The error is on any lines that start with ![FieldName] = DesiredResult

It appears that it is not changing the Recordset that it is looking into.

Not sure how to correct this issue, or if it is even possible to work within multiple Recordsets. Any help will be greatly appreciated.

Every time I get this error it points to a "short in my typing mechanism". Also using the query to do it is a lot easier once you get the hang of typing the query correctly. I usually go to Create - Query and then after I get the query to work I select the SQL view and copy the query string then input any variables using the correct delimiters around the query string.