Advance to next row in report

[jgelpi16]

I am generating a report and having to assign the values to the report programmatically. The report is bound on dates and it has a list of dates from the same table that my recordset is based on. I have included the beginning of my code to give you an idea. However, when the report loads the first value that was assigned to the text box is displayed in all of the text boxes for the "No Response" category. How can I either advance to the next text box within the report? Is this something that is feasible? I have included screen shot of report for reference as well. Thanks in advance for your help.

Private Sub Report_Load()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim varDiv, varDate, varResponse, varCount
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryDivBreakdown", dbOpenDynaset)
    rs.MoveFirst
    Do While Not rs.EOF
    '******************
    'This loop will look up each value in "qryDivBreakdown
    'and assign the value to the appropriate box within rptDivBreakdown
    '******************
        varDiv = rs("DIVISION")
        varDate = rs("BegWk")
        varCount = rs("CountID")
        varResponse = rs("Response")
        If varDiv = 10 Then
            If varResponse = "No Response" Then
                txtDiv1_7.Value = varCount
            End If
        End If
                
    rs.MoveNext
    Loop
End Sub

[NTC]

mmmm don't like the approach. I believe you can (and should) establish all needed values at the core record set; via calculated values. Even if it means subqueries.

I didn't look into it that deep though, are you sure that you can not?

[pkstormy]

I created a 'Timesheet' type of report similar to look with your snapshot. On the Report itself, I had each field/control separately (versus a query). So I had 50 controls on the report. For example, I had controls named "Text1", "Text2", "Text3", etc.. I would then loop in code (in the OnFormat of the report detail section) populating each of these controls using a loop along with the recordset I opened.

ex:
For X = 1 to 50
me.controls("Text" & X).value = rs!Field(X) (where rs is the recordset I opened to loop through)
next X

I'm not sure if this helps but it's one approach. The other approach which I think is best for your report is to design a query (you could use expressions for some fields) which returns the date range and values. Or you could write some code to create a temporary type table and populate it. The report would then just be based on this table or a query.

(nice looking report)