list box, "no current record"

T

TRM

I have a list box for which I change the rowsource, depending on the
selections of the user. I am performing a loop of code based off the query
on the list box- it updates a table for each record in that current query.
The update works the first time through, but if the user wishes to make a new
selection, then update the table based on the new query/recordset, I get an
error message "no current record". I have a requery on the list box, and I
have stepped through the code to find that there are records, but a bookmark
isn't set, like it's at the end of the recordset. I have put in the command
"rs.movefirst" prior to the statement where I get the error, but that
generates a different error ("invalid operation", I THINK). Any suggestions?
I have included a snippet of the code below.

Dim ctlSeriesList As ListBox
Dim db As Database
Dim rs As DAO.Recordset
Dim strMSG, strSeriesName, strSQL, strClassCTL, strResponse As String

intEmpID = Me.EmpID
strSeriesName = Nz(DLookup("[TrainingCategory]", "TrainingCategory",
"[CatID]= " & Me.lstTrainingList & ""))

strMSG = "Click 'OK' to register " & Me.FirstName & " for '" & strSeriesName
& "'. Click 'CANCEL' to escape."

strResponse = MsgBox(strMSG, vbOKCancel)
If strResponse = 2 Then
GoTo cmdAddSession_Click_Exit
Else

Set db = CurrentDb
Set rs = Me.lstTrainingList.Recordset 'db.OpenRecordset("copyofselected")
Dim intcount As Integer
intcount = rs.RecordCount
****works fine as is first time through
strSeriesName = rs.Fields("TrainingCategory") **** <--error, "no record"
here
rs.MoveFirst **** <-- moved this to before above statement, generates new
error

Do Until rs.EOF
If strClassCTL <> rs.Fields("Class") Then
varSessionID = rs.Fields("ClassID")
varSession = rs.Fields("Class")
* more code *
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
rs.MoveNext
Loop
strMSG = Me.FirstName & " has been registered for all classes within the " &
strSeriesName & "."
MsgBox (strMSG)
End If

rs.close
Set rs = Nothing
 
Top