473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Dynamic Report using Calculated Values

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
6 2384
MMcCarthy
14,534 Expert Mod 8TB
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
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
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
12,516 Expert Mod 8TB
I believe the On Print event may be too late to change positioning. Try the On Format event instead.
Oct 11 '07 #5
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
12,516 Expert Mod 8TB
Not a problem. Good luck.
Oct 11 '07 #7

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

Similar topics

2
by: yzarc | last post by:
I'm working with a DB design that seems to me to be rather complex. This is a very slimmed down version of what I'm doing, but I believe it is enough to get my question resolved. Here is my...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
3
by: ahaque38 | last post by:
Hello. Using A2K SP3, I am having the following problem with a report using "Sorting and Grouping". I have recently added a grouping in the reports for "Category2<>'CONTRACTS'". I have...
1
by: shobhit_shanker | last post by:
Here are the relevant "givens" to my problem... Form: frmLaunchRpt - Text Box: txtAsOfDate - Check Box: chkLogEval - Command Button: cmdLaunchRpt Report: rptEvaluation - Text Box:...
2
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic...
4
by: Jana | last post by:
Hi! I have an Access 97 report based on tables linked to an SQL back end. The report has 5 subreports in it (unfortunately unavoidable due to the nature of the report) and performance is quite...
7
by: Sunil Korah | last post by:
Hi, I haven't used access reports much. I have a problem in getting the total of a group. I have 3 fields, ProgName (Program name), Pname (Participant's name) and PCategory (Participant...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
14
ollyb303
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which...
5
by: royals | last post by:
Hello Access gurus, I am a self-taught Access “developer” and have a ton to learn so please allow me to sound stupid. I am using 2003 on a XP platform. I manage properties for an...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.