By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,257 Members | 2,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,257 IT Pros & Developers. It's quick & easy.

Dynamic Report using Calculated Values

P: 1
Create Dynamic Reports using VBA

This has been a great post and all code works as described. However, I am still struggling with a problem, for which I haven't been able to find an answer anywhere online.

I need to create a report in Access VBA, where I manipulate the data, and not tie it to a query.

The best Access offers you is calculated control, which (again) is calculated from query data. What I need is totally different.

EXAMPLE:
I need to pull a list of transactions with a specific query.
From another table I need to pull transaction details.
The two tables share a common key.
Depending on whether transaction details are debit or credit I have to calculate running balance and obviously deside whether I will display the value - on the left or right-hand side....

Now... as you see there are many decisions to be made about the POSITION and VALUES of different info, so I cannot just tell Access -- here is the query, go play... I need to populate the report with my controls, where I say (left or right) with the value I calculate...

I have not been able to achieve this. Always reports are generated on the basis of a query and that is it.

IDEAS THAT MIGHT WORK?
• Maybe one way is to create a dummy recordset, populate it and use it as a source for the report (however I do not know if that is at all possible).
• Another possibility is to export everything to Excel, but then I am giving up the built-in Reports utility Access has, plus users must have Excel installed.

I am running out of ideas... Any help will be appreciated.
Sep 14 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
When I have to create a financial statement with debits and credits I create a transaction table based on a series of queries and run the report off that.
Sep 14 '07 #2

MGrowneyARSI
P: 90
In the past I've had to use a single table made from many for different resigns, but anyway all I did was run an append query or querys to build the table on open of the form in my case report in yours and then a delete query on close. I don’t know if this helps if not leave more info on what it is exactly you are trying to do here. Good luck
Sep 14 '07 #3

P: 2
Forgive me if I err in posting here... This is my 1st post - ever.

I am trying to programatically set the position of a control on an MS Access (2003) report. I am calling the following from the On Print property of the Detail section of my report. It seems that the following syntax should work, but it does not (nor does anything else I have tried).

Expand|Select|Wrap|Line Numbers
  1.  Me!ReportControl1.Left = 800 
I can successfully change the Visible property using the syntax below, but I cannot change the Left property in similar fashion. What am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1.  Me!ReportControl1.Visible = True 
Oct 11 '07 #4

Rabbit
Expert Mod 10K+
P: 12,366
I believe the On Print event may be too late to change positioning. Try the On Format event instead.
Oct 11 '07 #5

P: 2
I believe the On Print event may be too late to change positioning. Try the On Format event instead.
Thank you! I was getting errors within On Print that I do not get from On Format - and it works!
Oct 11 '07 #6

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem. Good luck.
Oct 11 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.