Looping through reports

[graeme34 via AccessMonster.com]

Hi I am attempting to loop through a query recordset printing a statement for
each record.

I have the following code..

Private Sub cmdPrintMonthlyStatements_Click()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qryAccountLoop")

Do Until rs.EOF = True

DoCmd.OpenReport "repMonthlyStatement", acViewPreview

DoCmd.Close acReport, acSaveNo
rs.MoveNext

Loop

Which works fine I think, its just the report is not what I expected...

On opening the report the report is showing details of each account record,
does the report need to be filtered at all ??? To show only the details of
the current record in the reocrdset......the control source query forthe
report header is the same as the recordset:

SELECT DISTINCT tblAccount.AccountIndex, tblAccount.AccountName, tblAccount.
[Account Address 1], tblAccount.[Account Address 2], tblAccount.[Account
Address 3], tblAccount.[Account Town / City], tblAccount.[Post Code],
tblAccount.CurrentBalance
FROM tblAccount INNER JOIN qryStatementLine ON tblAccount.AccountIndex =
qryStatementLine.AccountIndex
ORDER BY tblAccount.AccountIndex;

any suggestions??

 

[Marshall Barton]

Hi I am attempting to loop through a query recordset printing a statement for
each record.

I have the following code..

Private Sub cmdPrintMonthlyStatements_Click()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qryAccountLoop")

Do Until rs.EOF = True

DoCmd.OpenReport "repMonthlyStatement", acViewPreview

DoCmd.Close acReport, acSaveNo
rs.MoveNext

Loop

Which works fine I think, its just the report is not what I expected...

On opening the report the report is showing details of each account record,
does the report need to be filtered at all ??? To show only the details of
the current record in the reocrdset......the control source query forthe
report header is the same as the recordset:

SELECT DISTINCT tblAccount.AccountIndex, tblAccount.AccountName, tblAccount.
[Account Address 1], tblAccount.[Account Address 2], tblAccount.[Account
Address 3], tblAccount.[Account Town / City], tblAccount.[Post Code],
tblAccount.CurrentBalance
FROM tblAccount INNER JOIN qryStatementLine ON tblAccount.AccountIndex =
qryStatementLine.AccountIndex
ORDER BY tblAccount.AccountIndex;

If that would work, you need to use the OPenReport method's
WhereCondition argument to specify the account number
(AccountIndex?)

DoCmd.OpenReport "repMonthlyStatement", acViewPreview, _
WhereCondition:= "AccountIndex = " & rs.AccountIndex

But, I don't think this approach will work reliably because
the report may not have completed pronting one account
before you tell it to print another account. This is
because if the report is already open, the OpenReport can
not open it again. You would have to add more code in your
loop to test if the report is still open and wait until it's
no longer open before going on to the next account.

Why can't you print a single report with all the accounts in
it? By grouping on the AccountIndex field, you can force a
new page before each account and the stack of paper will be
the same as you are trying to achieve with your problematic
approcah.

 

[graeme34 via AccessMonster.com]

Thank you Marshall (again )

Hi I am attempting to loop through a query recordset printing a statement for
each record.

[quoted text clipped - 30 lines]

qryStatementLine.AccountIndex
ORDER BY tblAccount.AccountIndex;

If that would work, you need to use the OPenReport method's
WhereCondition argument to specify the account number
(AccountIndex?)

DoCmd.OpenReport "repMonthlyStatement", acViewPreview, _
WhereCondition:= "AccountIndex = " & rs.AccountIndex

But, I don't think this approach will work reliably because
the report may not have completed pronting one account
before you tell it to print another account. This is
because if the report is already open, the OpenReport can
not open it again. You would have to add more code in your
loop to test if the report is still open and wait until it's
no longer open before going on to the next account.

Why can't you print a single report with all the accounts in
it? By grouping on the AccountIndex field, you can force a
new page before each account and the stack of paper will be
the same as you are trying to achieve with your problematic
approcah.