473,387 Members | 1,283 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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

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
Nov 13 '05 #1
3 6619
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

Nov 13 '05 #2
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


Nov 13 '05 #3
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!
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: David | last post by:
Hi, I have an order form which has a field 'ProductID'. This form has a button on each record to open a new form linked by ProductID. This new form is a continuous form and obviously, only...
1
by: huela | last post by:
Somehow i have to display the recordset in unbound form When i use the following display the data in recordset to unbound form. I have a question: i can only display the last record data, all...
2
by: Rob | last post by:
Is there a faster/cheaper way to determine the number of records being displayed in a continuous form? I always did: dim rs as dao.recordset set rs = me.recordsetclone if rs.recordcount = 0...
1
by: tclarke | last post by:
Hi, I'm using Access 2003 and VB 6.3 to set exams and track progress. I've created a command button on a form (continuous forms) which runs the code below in it's On_Click event. It adds 8 new...
1
by: David | last post by:
Hi, I have a continuous form. For each record I have a field 'HeldDate' (Text Field from a table) Against each record I have a button which sets the visibility of this text box to 'True' and...
3
by: ApexData | last post by:
I am using a continuous form for display purposes. Above this form, a single record is displayed so that when the user presses my NewButton they can enter a NewRecord which gets added to the...
10
by: webgirl | last post by:
Hi there, I've been searching the net & the forums here over the last few days for help with my problem & am getting myself really confused.. hoping someone may be able to help me here. I've...
36
by: beebelbrox | last post by:
Hi, I am new VB programming in Access and I am requesting help with the following code. WIndows OS MSaccess 2003 This code is attached to an unbound form that will display a specific recordset...
3
by: Clint Stowers | last post by:
Access 2003 I have a Continuous Form. On Open I force it to go to the Last Record. When the form opens only the last record is shown at the top. To view any of the previous records you must...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.