Can I display all the records of a DAO.Recordset in continuous forms textboxes?

[Richard Hollenbeck]

I have the following query in my form's code:

Private Function Get_Data(fieldNum As Integer)
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT [lName] & "", "" & [fname] AS Student,
activities.activityDescription, studentScores.score FROM groups INNER JOIN
(students INNER JOIN (activities INNER JOIN studentScores ON
activities.activityID = studentScores.activityID) ON students.studentID =
studentScores.studentID) ON groups.groupID = activities.groupID WHERE
(((students.studentID) = " & Forms!frmFindStudent!lstStudentName.Column(0) &
")) ORDER BY groups.groupOrder, activities.activityOrder,
activities.activityDescription;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Get_Data = rs.Fields(fieldNum)
rs.Close
db.Close
End Function

and the text boxes in the continuous forms have the following Control
Source:
=Get_Data(0), =Get_Data(1), =Get_Data(2), etc.

The problem is that it only shows the first record. However, when I make a
similar query under queries, I get all the records (though I need to hard
code the studentID). What might I be doing wrong? I DO have the form set
to continuous forms.

Thanks,
Rich Hollenbeck

[Larry Linson]

First, let me urge that you include your question in the body of your post
so it doesn't get truncated and deprive you of the assistance of people
whose newsreaders didn't show the whole thing. There are other good
suggestions on effective use of newsgroups at
http://www.mvps.org/access/netiquette.htm.

Second, if you have not omitted some details for clarity, you can do what
you want simply by using the Query you show as the RecordSource for a Form.
Continous forms view is designed for use as a bound Form. There is no need
to open a Recordset.

I'm not certain what you mean by "the textboxes in the continuous form have
the following control source" -- if you mean you think you have assigned a
different subscript to each row in the continuous form, then I fear you are
mistaken. There is only a single definition in CFV, which is repeated for
each row.

If I have misunderstood your question, please clarify. If I am not able to
follow up on the clarification, I'm sure someone will.

Larry Linson
Microsoft Access MVP

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:K3AQd.28861$wc.17518@trnddc07...

I have the following query in my form's code:

Private Function Get_Data(fieldNum As Integer)
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT [lName] & "", "" & [fname] AS Student,
activities.activityDescription, studentScores.score FROM groups INNER JOIN
(students INNER JOIN (activities INNER JOIN studentScores ON
activities.activityID = studentScores.activityID) ON students.studentID =
studentScores.studentID) ON groups.groupID = activities.groupID WHERE
(((students.studentID) = " & Forms!frmFindStudent!lstStudentName.Column(0) & ")) ORDER BY groups.groupOrder, activities.activityOrder,
activities.activityDescription;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Get_Data = rs.Fields(fieldNum)
rs.Close
db.Close
End Function

and the text boxes in the continuous forms have the following Control
Source:
=Get_Data(0), =Get_Data(1), =Get_Data(2), etc.

The problem is that it only shows the first record. However, when I make a similar query under queries, I get all the records (though I need to hard
code the studentID). What might I be doing wrong? I DO have the form set
to continuous forms.

Thanks,
Rich Hollenbeck

[Richard Hollenbeck]

Thanks, Larry!

I found a different solution. I have a multipurpose temporary table I use a
lot. It is not for permanently holding data and I empty it out after each
use. So I made the button delete all data from the temp table, then
assemble the neccessary data and insert it into the temp table. Then I open
the form with the continuous forms with the temp table set as its
RecordSourse. All this is in VBA behind the command button of the first
form. I set the OnClose event of the second form to send the updated data
to the appropriate tables then delete the contents of the temp table. This
seems to work nicely, and I don't have to mess with a DAO.RecordSet.

Rich Hollenbeck

"Larry Linson" <bo*****@localhost.not> wrote in message
news:%DBRd.12498$uc.7093@trnddc01...

First, let me urge that you include your question in the body of your post
so it doesn't get truncated and deprive you of the assistance of people
whose newsreaders didn't show the whole thing. There are other good
suggestions on effective use of newsgroups at
http://www.mvps.org/access/netiquette.htm.

Second, if you have not omitted some details for clarity, you can do what
you want simply by using the Query you show as the RecordSource for a Form. Continous forms view is designed for use as a bound Form. There is no need
to open a Recordset.

I'm not certain what you mean by "the textboxes in the continuous form have the following control source" -- if you mean you think you have assigned a
different subscript to each row in the continuous form, then I fear you are mistaken. There is only a single definition in CFV, which is repeated for
each row.

If I have misunderstood your question, please clarify. If I am not able to
follow up on the clarification, I'm sure someone will.

Larry Linson
Microsoft Access MVP

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:K3AQd.28861$wc.17518@trnddc07...

I have the following query in my form's code:

Private Function Get_Data(fieldNum As Integer)
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT [lName] & "", "" & [fname] AS Student,
activities.activityDescription, studentScores.score FROM groups INNER JOIN (students INNER JOIN (activities INNER JOIN studentScores ON
activities.activityID = studentScores.activityID) ON students.studentID = studentScores.studentID) ON groups.groupID = activities.groupID WHERE
(((students.studentID) = " & Forms!frmFindStudent!lstStudentName.Column(0)

&

")) ORDER BY groups.groupOrder, activities.activityOrder,
activities.activityDescription;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Get_Data = rs.Fields(fieldNum)
rs.Close
db.Close
End Function

and the text boxes in the continuous forms have the following Control
Source:
=Get_Data(0), =Get_Data(1), =Get_Data(2), etc.

The problem is that it only shows the first record. However, when I

make a

similar query under queries, I get all the records (though I need to

hard code the studentID). What might I be doing wrong? I DO have the form set to continuous forms.

Thanks,
Rich Hollenbeck

[Lyle Fairfield]

Richard Hollenbeck wrote:

I found a different solution. I have a multipurpose temporary table I use a
lot. It is not for permanently holding data and I empty it out after each
use. So I made the button delete all data from the temp table, then
assemble the neccessary data and insert it into the temp table. Then I open
the form with the continuous forms with the temp table set as its
RecordSourse. All this is in VBA behind the command button of the first
form. I set the OnClose event of the second form to send the updated data
to the appropriate tables then delete the contents of the temp table. This
seems to work nicely, and I don't have to mess with a DAO.RecordSet.

This is such a wonderful solution that I hope you will keep it entirely
to yourself and a secret from everyone!