Solved

Carry forward value in Access Report

Posted on 2010-09-03
13
511 Views
Last Modified: 2012-06-27
HI all

I have an Access 2003 report I need some advice with

This report has a group section of fiscal years.
There is an unbound control in this section called Base

Initially the first Base value is a value from an underlying query which I use code to fill in ...no problem
Following years is other data for the same fiscal year coming from a subreport with a total called PermTotal..again no problem

Heres where the problem is.
For Subsequent years the next Base should be the previous Base with the PermTotal for this current year.
I have code which has a variable to hold the previous base amount.
and then add in the subreport values for this current year.

I think part of my problem is that my code is in the group section for the fiscal years. It fires when I advance the pages.
Though the stored previous base is available, the subreport values dont appear to be ready yet...so they are 0 resulting in the wrong total

I appreciate the help

Thanks
0
Comment
Question by:HenryV1955
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 33598214
I think you need to supply a sample database demonstrating the problem and a snapshot of the result required.
0
 
LVL 13

Expert Comment

by:lucas911
ID: 33599158
Make that variable a global variable in your report.

Then in the event, determine your logic and set the value of the global variable.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33599219
I, like hnasr, would like to see a sample of this DB.

My feeling is that there may be a less complicated way to do this...

Sample database notes:
Back up your database(s).
Combine the front and back ends into one database file.
Remove any startup options, unless they are relevant to the issue.
Delete any objects that do not relate directly to the issue.
Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
Compile the code. (From the database window, click: Debug-->Compile)
Run the compact/Repair utility.
Remove any Passwords and/or security.
Post explicit steps to replicate the issue.
Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.


;-)

JeffCoachman
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33601655
As boag2000 suggested, and on many occasions you may find it easier to create a a new database, and import the relevant objects that recreate the issue.
0
 

Author Comment

by:HenryV1955
ID: 33621512
HI guys

Thanks for responses so far. Maybe I can simplify the problems.

I have a main report with 2 subreports.

If in one control on my main report I use a control source the following:
= [H51] + [subrptLevel_FY_NoAG_PivotStdAcctNonPerm].Report![51NonPermTotal] + [subrptLevel_FY_NoAG_PivotStdAcctPerm].Report![51PermTotal]

This works fine, I have the correct values until one of subreports has no data. Even putting code in the subreport for
 'No data' wont trap this problem

If instead for this control I use code to populate the value, and I use a variable to capture the subreports totals, the variables
come up with zero even though I know the value isnt 0. I put the code in the GroupHeader2 On Print section.
Yes I am group levels on my main report. I suspect its because I'm too soon ahead of the actual subreport value????
 Thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33621929
There are many ways to deal with this situation.

I usually reach for an aggregate function on the Subforms recordsource to insert Zero if no corresponding records are found for the Main form record.

Something "roughly" like this:

IIF(Dcount("SomeField", "YourSubformsSourceTable/Query","SubformForiegnKey=" & MainForm primaryKey)=0, 0, [YourSubFormControl/Fieldvalue])
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33622093
...But there are slicker alternatives to using this method.

I'll try to post a sample later this evening.

Perhaps hnasr will chime back in with an alternate solution.

;-)

Jeff
0
 

Author Comment

by:HenryV1955
ID: 33623113
Hi Jeff, thanks.....interesting code you provided.

Its interesting I've created dozens of reports, having some issues this one though. I'm sure the answer will come.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33625709
boag2000 is leading you to the solution.

"having some issues this one though"
Attach a sample database showing him the issues.
0
 

Author Comment

by:HenryV1955
ID: 33627174
Hi all
As I was saying, there were 2 issues with this report.
One was a control with a controlsource pointing to subreport controls worked fine as long as there was data.
There other was using an unbound control and filling it using code. I would have rather preferred this way, but for some reason, as I advance record by record, the value from the subreport control, was always a record value behind.
At this point I went with a control source like this
=[H51]+IIf(subrptLevel_FY_NoAG_PivotStdAcctPerm.Report.HasData,subrptLevel_FY_NoAG_PivotStdAcctPerm.Report![51PermTotal],0)+IIf(subrptLevel_FY_NoAG_PivotStdAcctNonPerm.Report.HasData,subrptLevel_FY_NoAG_PivotStdAcctNonPerm.Report![51NonPermTotal],0)
This works ok.
If someone could explain the 'record value behind issue' that would be fine

Thanks
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 33628102
Here is the sample.

As you can see there is a Dcount in the Payment sub form footer.
This forces a Zero if there are not matching Main form records.
Then the payment amount can be deducted form the Invoice amount, and the Balance can be displayed on the Main Form
;-)


To explain a bit more:
The whole issue is that when a subform has no related records, you can't use NZ(), because it is not null, ...there are no records.


Again, I have seen at least two other techniques to deal with this situation.
I use the Aggregate function because I understand it and I can read the formula(s) and see what is doing and why it is there.

;-)

JeffCoachman
Access-EEQ26450344-BasicSimpleSa.mdb
0
 

Author Closing Comment

by:HenryV1955
ID: 33977591
Solution provided gave part of the answer, I provided the rest...thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33978795
ok
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now