MS Access Report design - subreport, onopen run vba code, set recordsource
Posted on 2006-10-24
Hello .... I am in need of some assistance / guidance with a report. Please see description of what has been done so far and a proposal for the next steps.
Report Displays measurements, (ie. credit sales, end receivables, beg recv, end curr recv ... and 4 performance measurements) for A select time range.
Report sample below (Recordsource is a query, and report is dyamically filtered based on selection from form)
REPORT BY CUSTOMER
Business Unit: XXXXXX
Column Names: fiscal month, CreditSales, EndRecv, BegRecv, EndCurrRecv, DSO, BPDSO, ADD, CEI
5/27 XXX xXX XXX XXX XXX XXX XXX xxx
7/1 XXX XXX XXX XXX XXX XXX XXX XXX
7/29 XXX xxx XXX
8/26 xxx xxx XXX
9/30 xxx xxx XXX
The part of the report above works fine ... now I need to display additional data. The only way I can think of to do this is to create a subreport and create code on the open event to execute it .... (please see what I am proposing to do ...NEED SOME GUIDANCE HERE):
vba code will do the following:
- Run a query to retrieve total credit sales for the BUSINESS UNIT
- Run another query to retrieve EndRecv and logic in vb code to calculate EndCurrRecv
- Run another query to retrieve BegRecv.
The above vba code will use values from 3 recordsets created from the queries just mentioned...
How can I set the recordsource of this subreport with the data retrieved using the vba? Is it possible to take these values retrieved from several queries and populate the subreport text boxes that will be contained in the subreport? Does this approach look feasible?
Thanks for any insight or guidance you can provide.... I am happy to provide additional clarification and details as needed. Thanks.