0
votes

So I have a stored procedure in my database which I run from MS access using pass through query which returns a table. I access the table using a recordset "rs". I want to loop through recordset and for each row I want to create an instance of the report and populate textboxes.

For example I want a report for each student that will have name, age, sex, etc. So it should be done by looping through recordset and creating a report for each student and filling in textboxes. I wrote this code to test for one textbox but have a strong feeling that I'm missing a lot. Any help would be very much appreciated

Dim i As Integer
i = 0

Do While Not rs.EOF
    Dim rtp(i) As Report_DecemberProject
    Set rpt(i) = New Report_DecemberProject
    rpt(i).Text55.Value = rs.Fields(3)
    rpt(i).Visible = True
    i = i + 1
Loop
2
Have you tried doing this without code? You should be able to feed this to a report with a page break and generate a multipage reportNick.McDermaid
the reports have identical labels but ofcourse the values shuld be different, and ofcourse the number of reports is not fixed since there can be different number of students depending on conditions in the stored procedure. I have done a bit of searching on what you mentioned, but don't think it solves what I am asking for. of you're sure it does please elaborate because im kinda clueless and new to this.Amro El Gherianii ッ
If you have five records, you should be able to create one report that uses the dataset and generates one report with five pages, each page containing different information from the dataset. Please do some experimentation with MS Access reports. You shouldn't need to use any VBA for this.Nick.McDermaid
Use the table or query you used to create the recordset rs as data source of your report. Then you can set the control source of textboxes to the required fields. You can insert a page break or group by some field and insert group headers and footers. In the group header set "Force New Page" to "Before Section".Olivier Jacot-Descombes

2 Answers

0
votes

Cannot programmatically set Value property of controls on report. Use BOUND report and controls and ForceNewPage property of Detail section to force page break after each record. Use Page Header section for information that must repeat for each page, such as label with report title and textbox with =Date(), etc.

0
votes

To expand slightly on June 7's answer, it is the "Grouping" property of the report that lets you make a multi-page report with a new page started for each student. In the report properties, group on the student's (unique) ID. Then, as suggested above, use the ForceNewPage property in the Group header or footer to put each new student on a new page.