ADO, how to code for previous and Next records CMD buttons ??

I've been working on this project and can now get it open and putting data
from two databases into my form.

The following code is run on the Form OPEN event and shows the first record
in the database....my question is, I now want to code for a FirstRecord,
LastRecord, NextRecord and PreviousRecord command button on the form. How
would I do this?
for example for next record would I just put in:
rst1.movenext
rst2.movenext
Is that all I need to do or is there more to it? Also what abot the other
command buttons?
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")

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

tanks.

OK, here is what I have done, for the NEXT RECORD button and I get an
error....

Rst1.MoveNext

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")


'move to next row of rst2
Rst2.MoveNext
Rst2.Find "studentid=" & Rst1.Fields("studentid") & ""
Me.txtnokfname = Rst2.Fields("nokfirstname")
Me.txtnoklname = Rst2.Fields("noklastname")
Me.txtnokrelationship = Rst2.Fields("nokrelationship")

If I leave out the rst2.movenext and everything below it, all the info
is filled in fine.
When I add the rst2.movenext and below I get an error.
Run time error '-2147352567' (8000200009)
The value you entered isnt valid for this field

Even if I comment out the rst2.find comment because I thought it was
not needed, I get the same error...can anyone tell e what am doing
wrong?


RON wrote:
I've been working on this project and can now get it open and putting data
from two databases into my form.

The following code is run on the Form OPEN event and shows the first record
in the database....my question is, I now want to code for a FirstRecord,
LastRecord, NextRecord and PreviousRecord command button on the form. How
would I do this?
for example for next record would I just put in:
rst1.movenext
rst2.movenext
Is that all I need to do or is there more to it? Also what abot the other
command buttons?
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")

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

tanks.

I can't see much without the context the code is in but the first thing I
notice is that your code will error out if rst1 or rst2 is at EOF. Before
you refer to the Field values of the current row of the Recordset, you needs
to check whether the Recordset is at EOF or not.

Also, the construct of the statement:

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

is a bit strange. If StudentID is numeric, you don't need the & "" at the
end. If the StudentID is Text, you need:

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

(double-quote + single-quote + double-quote at the end).

There is also a fairly cryptic (for me, at least, since I rarely use Find)
message in Help topic "Find Method" which may be applicable to your code,

Quote:

"Note An error will occur if a current row position is not set before
calling Find. Any method that sets row position, such as MoveFirst, should
be called before calling Find."

--
HTH
Van T. Dinh
MVP (Access)



"Ron" wrote in message
oups.com...
OK, here is what I have done, for the NEXT RECORD button and I get an
error....

Rst1.MoveNext

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")


'move to next row of rst2
Rst2.MoveNext
Rst2.Find "studentid=" & Rst1.Fields("studentid") & ""
Me.txtnokfname = Rst2.Fields("nokfirstname")
Me.txtnoklname = Rst2.Fields("noklastname")
Me.txtnokrelationship = Rst2.Fields("nokrelationship")

If I leave out the rst2.movenext and everything below it, all the info
is filled in fine.
When I add the rst2.movenext and below I get an error.
Run time error '-2147352567' (8000200009)
The value you entered isnt valid for this field

Even if I comment out the rst2.find comment because I thought it was
not needed, I get the same error...can anyone tell e what am doing
wrong?


RON wrote:
I've been working on this project and can now get it open and putting
data
from two databases into my form.

The following code is run on the Form OPEN event and shows the first
record
in the database....my question is, I now want to code for a FirstRecord,
LastRecord, NextRecord and PreviousRecord command button on the form.
How
would I do this?
for example for next record would I just put in:
rst1.movenext
rst2.movenext
Is that all I need to do or is there more to it? Also what abot the
other
command buttons?
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")

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

tanks.

OK I have been playing with this some and am not really having luck.
Now I get an error either BOF or EOF is true

with this code:
Private Sub Command21_Click()

Rst1.MoveFirst
While Not Rst1.EOF

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
Wend

Rst2.MoveFirst
While Not Rst2.EOF

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

Rst2.MoveNext
Wend


End Sub


Van T. Dinh wrote:
I can't see much without the context the code is in but the first thing I
notice is that your code will error out if rst1 or rst2 is at EOF. Before
you refer to the Field values of the current row of the Recordset, you needs
to check whether the Recordset is at EOF or not.

Also, the construct of the statement:

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

is a bit strange. If StudentID is numeric, you don't need the & "" at the
end. If the StudentID is Text, you need:

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

(double-quote + single-quote + double-quote at the end).

There is also a fairly cryptic (for me, at least, since I rarely use Find)
message in Help topic "Find Method" which may be applicable to your code,

Quote:

"Note An error will occur if a current row position is not set before
calling Find. Any method that sets row position, such as MoveFirst, should
be called before calling Find."

--
HTH
Van T. Dinh
MVP (Access)



"Ron" wrote in message
oups.com...
OK, here is what I have done, for the NEXT RECORD button and I get an
error....

Rst1.MoveNext

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")


'move to next row of rst2
Rst2.MoveNext
Rst2.Find "studentid=" & Rst1.Fields("studentid") & ""
Me.txtnokfname = Rst2.Fields("nokfirstname")
Me.txtnoklname = Rst2.Fields("noklastname")
Me.txtnokrelationship = Rst2.Fields("nokrelationship")

If I leave out the rst2.movenext and everything below it, all the info
is filled in fine.
When I add the rst2.movenext and below I get an error.
Run time error '-2147352567' (8000200009)
The value you entered isnt valid for this field

Even if I comment out the rst2.find comment because I thought it was
not needed, I get the same error...can anyone tell e what am doing
wrong?


RON wrote:
I've been working on this project and can now get it open and putting
data
from two databases into my form.

The following code is run on the Form OPEN event and shows the first
record
in the database....my question is, I now want to code for a FirstRecord,
LastRecord, NextRecord and PreviousRecord command button on the form.
How
would I do this?
for example for next record would I just put in:
rst1.movenext
rst2.movenext
Is that all I need to do or is there more to it? Also what abot the
other
command buttons?
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")

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

tanks.

You need to check for EOF before the MoveFirst.

Here is a short sample code from one of my databases:

********
strSQL = "SELECT ProdCode, ProdDesc FROM dbo.tblProduct " & _
" WHERE (ProdCode Like '" & Left(Me.ProdCode, 5) & "%')"
Set rsa = New ADODB.Recordset
With rsa
.Open strSQL, fnGetCnnSQL, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not .EOF Then
strMsg = "The versions of the product a" & vbCrLf
.MoveFirst
Do
strMsg = strMsg & vbCrLf & .Fields("ProdCode").Value & ": " & _
.Fields("ProdDesc").Value & Space(10)
.MoveNext
Loop Until .EOF
End If
.Close
End With
********

--
HTH
Van T. Dinh
MVP (Access)



"Ron" wrote in message
ups.com...
OK I have been playing with this some and am not really having luck.
Now I get an error either BOF or EOF is true

with this code:
Private Sub Command21_Click()

Rst1.MoveFirst
While Not Rst1.EOF

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
Wend

Rst2.MoveFirst
While Not Rst2.EOF

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

Rst2.MoveNext
Wend


End Sub