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.

response to your questions:
- This is an individual report run on a customer (1 of 1000's) Unfortunately, I can't retrieve the total sum of credit sales for the business unit this way. I need to run a separate query to retrieve this amount. I recently wrote some code to (a function that runs the query ... the function is called from a text box and it returns the result for display.

- End CurrRecv is calculated by subtracting end receivables with a balance from the total receivables (TOTAL - FUTURE)

- Beg Recv is used as part of one of the formulas (CEI - collection effectiveness index).

Instead of creating multiple functions that are called form the report to display the appropriate values - ( ie. one to retrieve Credit sales, another for End ReceivablesEndCurr Receivables - Note: due to the nature of what is being retrieve, multiple queries need to be executed ... the data cannot be retrieve in one query),

Is there a way to get all the data we need in one function and assign it as the recordsource of the subreport?
I'm just looking for the best and most efficient way to do this.....

Let me know if I can clarify anything or any other questions u have...

Can someone please close question and refund the points. I no longer need assistance on this one. To address this, I call a function that I created residing in a module within access. Within that function, several queries are called & recordsets are opened. I populate controls (text boxes) by getting the retrieving the data needed in the vba code and then assigning the values retrieved to the controls. I was able to reference the controls through the code and assign the values needed to them...thanks

Featured Post

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…

Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…