ADO, how to move through this recordset

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

I have been working on this for days and can not get it to work.

With the current code for the next button I get an error that either
BOF or EOF has been reached.

Basically I want this code for the NEXT button to move through the
recordset table one record at a time and display it in the correct
textboxes.

With this code right before it errors RST1 textboxes display the last
record in the table, and rst2 textboxes do not get updated.

anyone help?
===
Private Sub Command21_Click()
'If Not Rst1.EOF Then

If Not Rst1.EOF Then
Rst1.MoveFirst
Do
Me.txtstudentid = Rst1.Fields("studentid")
Me.txtAddress = Rst1.Fields("studentaddress")
Me.txtStudentFName = Rst1.Fields("studentfirstname")
Me.txtStudentLName = Rst1.Fields("studentlastname")
Me.txtCity = Rst1.Fields("studentcity")
Me.txtState = Rst1.Fields("studentstate")
Me.txtZip = Rst1.Fields("studentzip")
Rst1.MoveNext
Loop Until Rst1.EOF
End If


If Not Rst2.EOF Then
Rst2.MoveFirst
Do
'move to next row of rst2

Rst2.Find "studentid=" & Rst1.Fields("studentid") & ""
Me.txtnokfname = Rst2.Fields("nokfirstname")
Me.txtnoklname = Rst2.Fields("noklastname")
Me.txtnokrelationship = Rst2.Fields("nokrelationship")

Rst2.MoveNext
Loop Until Rst2.EOF
End If

End Sub
 
I have not tested this, but at first glance it looks as though the problem
may be that you are testing for .EOF at the end of the loop rather than at
the beginning. Your code reaches the last record. At this point, .EOF is not
yet True, so the code moves past the last record. Now .EOF is True, but this
isn't tested until the end of the loop, *after* the attempt to assign values
to the text boxes. Try changing this ...

Do
....
Loop Until Rst1.EOF

.... to something like this ...

Do While Not Rst1.EOF
....
Loop
 
hi Ron,
If Not Rst1.EOF Then
Rst1.MoveFirst
Do
Me.txtstudentid = Rst1.Fields("studentid")
Me.txtAddress = Rst1.Fields("studentaddress")
Me.txtStudentFName = Rst1.Fields("studentfirstname")
Me.txtStudentLName = Rst1.Fields("studentlastname")
Me.txtCity = Rst1.Fields("studentcity")
Me.txtState = Rst1.Fields("studentstate")
Me.txtZip = Rst1.Fields("studentzip")
Rst1.MoveNext
Loop Until Rst1.EOF
End If
Click to expand...
The most used loop construct is:

' check if there is data
If Not Rst1.Bof And Not Rst1.Eof Then
' forward loop
Do While Not Rst1.Eof
Rst1.MoveNext
Loop
' backward
'Do While Not Rst1.Bof
' Rst1.MovePrevious
'Loop
End If


mfG
--> stefan <--
 
Your second loop refers back to the first loop, which has already completed:

Rst2.Find "studentid=" & Rst1.Fields("studentid") & ""

Should the Rst2 loop perhaps be inside the Rst1 loop?

Alternatively, since Me.txtstudentid contains the last value populated from
Rst1.Fields("studentid"), perhaps you want

Rst2.Find "studentid=" & Me.txtstudentid & ""
 
Back
Top