Trying to setup filters for subreports

Greetings all,

I'm not sure if this is the best place for this post but here it goes anyways. I have a report that lists an account's budgetline status (how much money has been used in each, etc.) and an account's recent transactions. The information is coming from two tables into two subreports and I want to write a function that will take a parameter (an account name, for instance) and a period (fall 2004, for instance) and will setup (or filter, link) the report with all that account's budget lines and transactions. Thus far I've tried using filters to accomplish this without much luck (see code below). I've also tried linking the subreports .RecordSource to an SQL recordset, which didn't work either. Anybody have any ideas or suggestions on how I could go about setting this up? Eventually I want to have a function that prepares a report, e-mails it and prepares another and e-mails it in a loop, so this function should ideally be pretty generic.

In summary, I want to write something in VBA that will prepare a report given parameters and I want feedback/code that would help me approach it in the best way.


''my attempt to setup a filter

DoCmd.OpenReport AccountReportTemplate, acViewPreview

Filterstr = "((([" & BudgetDatabase & "].Account) = """ & Account & """) And (([" & BudgetDatabase & "].Period) = """ & Period & """)))"

[Reports]!ACCOUNT_REPORT.BUDGETsub_report.Form.Filter = Filterstr 'I get the error "the setting you entered isn't valid for this property"

[Reports]!ACCOUNT_REPORT.BUDGETsub_report.Form.Requery

Filterstr = " ((([" & TransactionDatabase & "].[Account])=""" & Account & """))"

[Reports]!ACCOUNT_REPORT.Form.Filter = Filterstr

[Reports]!ACCOUNT_REPORT.Form.Requery


thanks all,

- Justin

I'm also trying to work along these lines, but I'm not making much headway.

DoCmd.OpenReport AccountReportTemplate, acViewPreview

[ACCOUNT_REPORT].BUDGETsub_report.Form.Recordset = GetAccountTransactions("Central")

where GetAccountTransactions returns a recordset.

- Justin