Move recordset based on list box selection (1 Viewer)

[dapfeifer]

Hey all,

I'm developing a gradebook database, and for grade input I have a list box of students that users can select multiple students at a time from.

I'm having a problem getting the recordset to the right record, and actually I don't think its moving to the right record at all. Here's the code as it stands for when user hits the form's save button (a bit messy, I know):

Private Sub cmdSave_Click()
Dim strQuery As String
Dim students As Recordset
Dim varItem As Variant
Set students = cmbStudID.Recordset
'Make sure students are selected
students.MoveLast
'If (cmbStudID.ItemsSelected() = 0) Then
    'MsgBox "Error: No students selected", vbCritical
   ' Exit Sub
'End If
students.MoveFirst
 
If (Grade.Value = "") Then
    MsgBox "Error: No grade entered", vbCritical
    Return
End If
DoCmd.SetWarnings False
'Do Until students.EOF
    For Each varItem In Me.cmbStudID.ItemsSelected
    txtHold.Value
        students.MoveFirst
        students.FindFirst (students.Fields("StEnroll_ID").Value = Me.cmbStudID.Value)
    'If cmbStudID.Selected Then
    If (has_grade(students.Fields("StEnroll_ID"), cmbTestID.Value) = True) Then
    ' If this is an updated record write an update query
        strQuery = "UPDATE tblGrades SET Grade_Score = " & Grade.Value & ", Grade_Date = #" & Now() & "# WHERE StEnroll_ID = " & students.Fields("StEnroll_ID") & " AND Test_ID = " & cmbTestID.Value & ";"
    Else
       strQuery = "INSERT INTO tblGrades(StEnroll_ID,Grade_Score,Grade_Date,Test_ID) VALUES (" & students.Fields("StEnroll_ID") & "," & Grade & ",#" & Now() & "#," & cmbTestID & ");"
    End If
    'End If
        DoCmd.RunSQL strQuery
    Next varItem
    'students.MoveNext
'Loop
DoCmd.SetWarnings True

There are additional functions, but I'm pretty sure the FindFirst function isn't actually doing what I want it to, which is to move to the corresponding student in the record list in order to grab its value and have the grade assigned to the correct StEnroll_ID.

Any help would be appreciate, and I can try to clarify better if needed.

 

[MarkK]

You don't need to navigate the recordset in the list, you need to use 'varItem.' 'varItem' will contain the data you need to identify the selected student.