Code:
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.