Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access Report design - subreport, onopen run vba code, set recordsource

Posted on 2006-10-24
5
Medium Priority
?
552 Views
Last Modified: 2013-11-28
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
CustomerID: XXXXXX

Column Names: fiscal month, CreditSales, EndRecv, BegRecv, EndCurrRecv, DSO, BPDSO, ADD, CEI
Rows:

                        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.
0
Comment
Question by:lorincha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17801649
- Run a query to retrieve total credit sales for the BUSINESS UNIT
Put a footer on the group holding the business unit.  Put a textbox in the footer and set control source to
=Sum([CreditSales])

- Run another query to retrieve EndRecv and logic in vb code to calculate EndCurrRecv
what is the calculation as this can be entered into the control source for [EndCurrRecv]

- Run another query to retrieve BegRecv.
To do what with it?
0
 

Author Comment

by:lorincha
ID: 17805840
HI Mpmccarthy,


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...

Thanks
0
 

Author Comment

by:lorincha
ID: 17853509
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
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 18193298
Closed, 500 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question