Loop report with values in a table

[blindlemonray]

I am trying to output a report to a printer several times but change the report content based on the value of each row in a table.

I can loop for each value in the table and print a report. But I cannot figure out how to use the value of each row as a criteria in the report.

So I have a report and instead of creating a report for each user and printing, create a table of users and then print the report for each user in the table using the table as the criteria in the report.

Code shown below if it helps

Private Sub Command69_Click()
Dim cnCurrent As ADODB.Connection
Dim rsCallers As ADODB.Recordset

Set cnCurrent = CurrentProject.Connection
Set rsCallers = New ADODB.Recordset

rsCallers.Open "Accmans", cnCurrent, adOpenDynamic, adLockOptimistic

Do Until rsCallers.EOF
    DoCmd.OpenReport "user", acViewNormal
rsCallers.MoveNext
If rsCallers.EOF Then Exit Do
Loop
Exit Sub

End Sub

any help really appreciated.

 

[PWise]

you can use the where Condition

DoCmd.OpenReport "user", acViewNormal,,"user=" Accmans!user

but even better will be to join the Accmans(table/query) to the reports recordsource

 

[Remou]

You can use the Where statement:

DoCmd.OpenForm "user", acViewNormal, , "SomeNumericField=" & rsCallers!SomeNumericField

You will need delimiters for anything other than text fields.

 

[blindlemonray]

thanks peeps, but when I try this it prints the report for all the entries in the accmans table in one report. what I need is for it to print with each name in the table individually. So Accmans 1 then Accmans 2, etc. Can I somehow use a sort of current row filter??

 

[Remou]

Please post the code as modified to suit your application.

 

[blindlemonray]

Code as shown below:-

Dim cnCurrent As ADODB.Connection
Dim rsCallers As ADODB.Recordset

Set cnCurrent = CurrentProject.Connection
Set rsCallers = New ADODB.Recordset

rsCallers.Open "Accmans", cnCurrent, adOpenDynamic, adLockOptimistic

Do Until rsCallers.EOF
    DoCmd.OpenReport "Ashley", acViewPreview, User_name = " & rsCallers!user"
rsCallers.MoveNext
If rsCallers.EOF Then Exit Do
Loop
Exit Sub

 

[PWise]

perhaps if User_name is a text field then

Dim cnCurrent As ADODB.Connection
Dim rsCallers As ADODB.Recordset

Set cnCurrent = CurrentProject.Connection
Set rsCallers = New ADODB.Recordset

rsCallers.Open "Accmans", cnCurrent, adOpenDynamic, adLockOptimistic

Do Until rsCallers.EOF
    [COLOR=red]DoCmd.OpenReport "Ashley", acViewPreview, "User_name = '" & rsCallers!user & "'" [/color]
rsCallers.MoveNext
If rsCallers.EOF Then Exit Do
Loop
Exit Sub

 

[PHV]

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886