We help IT Professionals succeed at work.

Resetting recordsource for each record in subreport

newbie46 asked
Last Modified: 2013-11-28
I am using the following code (within a sub report) to set the record source. The problem is that I need to reset it for each new student id (within the main report) and the same course names are appearing for each student.

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String

On Error GoTo Err_Report_Open

 strSQL = "SELECT tbl_Courses.CourseName " & _
          "FROM tbl_Courses " & _
          "WHERE (tbl_Courses.Season='Summer') AND " & _
          "(tbl_Courses.Student=" & Me.Parent!StudentId & ")"
   Me.RecordSource = strSQL

Previously, I used the following code:
Private Sub Detail_Format (.....
dim cnn as new ADODB.Connection
dim rs as new adodb.recordset
dim strsql as string
set cnn = codeproject.connection

strsql = "SELECT tbl_Courses.CourseName " & _
          "FROM tbl_Courses " & _
          "WHERE (tbl_Courses.Season='Summer') AND " & _
          "(tbl_Courses.Student=" & Me.Parent!StudentId & ")"
rs.open source:=strsql, activeconnection:=cnn

do while not rs.eof
  me.CourseTaken = rs!CourseName

The problem with this method was that only the last coursetaken was displayed.

Any suggestions?

All students are retrieved from the record source of the main report. The subreport needs to loop through multiple records associated with a particular student to display their courses taken.
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

The simplest way is to allow Access to handle this for you. After properly building your report/subreport (and binding them to the correct Recordsource), make sure to set the Master/Child link fields for the subreport object. This tells Access which records to display for the "main" report record ... in effect, Access already does your filtering for you.


So are you suggesting to use the  Detail_Format Sub code? or suggesting something else?
Since we use ADPs, having our stored procedures stored in SQL Server 2008, we don't typically use the Master/Child link fields. They don't seem to work correctly with ADPs (so I have been told)
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
This one is on us!
(Get your first solution completely free - no credit card required)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.