NB. Please feel free to download the ZIP file attachment from
Post #5 and review how I've implemented these points as you go through the explanation here.
Ah. So the two totals you're referring to are the ones in the far-right column and the ones on each page. No Report Header is required - at least not including totals. Good. That starts to become clear. Always a good idea.
As such, the Right-Hand-Side (RHS) total is simply handled by using a formula in the Control on the Report that adds up the values from all the other Controls that hold currency values. Simple enough. I imagine that's enough explanation on that point.
The Page totals is where life starts to get more complicated and VBA becomes necessary. My earlier advice, it turns out, was utterly wrong and unhelpful so please ignore that :-( From
I want to show a total at the bottom of each page you can see that Page totals are not supported in Access. This is because a Page is not any part of the data construct but a property of a Report instead. Thus it would be illogical to provide such a feature. However, there is a way round that of course. Read on.
What proved necessary was to create extra TextBox Controls in the Detail Section that had two attributes :
- They include a Running Sum of the value (See the
RunningSum property of the TextBox). - They are generally invisible (Set the
Visible property to No).
NB. In the example they are not invisible as it's there, first & foremost, to illustrate & prove the concept.
Running Totals obviously show values that include the current record as well as all previous - but not subsequent - records. When it comes time to format a Page Footer the value in this Control is the value of the latest record printed up to that point. That includes not just the current page but all previous ones too. Not exactly what you require - but what it is that Access will give you. So - how do we convert these to totals specific to a particular page?
Well, we will need to maintain, within the module of the Report, totals for each column, and each page, of the Report.
Right, assuming we have a bunch of TextBox Controls in the Detail Section of the Report (In here I'll refer to them as
txtA,
txtB,
txtC, etc.) then we would also need ones to match each of these with the
RunningSum property set. These I'll name
txtRunA,
txtRunB,
txtRunC, etc. These are what we need to use in our Page Total Controls.
Fine so far, however a simple reference to each of these would show the total accumulated for each of the Controls across the Report
so far - not
per page. To handle the
per page bit we'll need to make use of VBA. We're going to use a special Function Procedure in our Report called
GetPageTot() and it will handle converting the
total so far into the
total for the current page. It will need the name of the column passed as well as the
total so far and, importantly, the current Page number. The name of the column can be whatever you want but I will use 'A', 'B' & 'C'. To use this we will now need a different formula in our Page total Controls such as
=GetPageTot('A',[txtRunA],[Page]),
=GetPageTot('B',[txtRunB],[Page]) &
=GetPageTot('C',[txtRunC],[Page]).
The VBA code for this Function Procedure (
GetPageTot()) is shown below with the extra Function Procedure it uses called
ValidKey(). I've included the whole of the module to help with perspective. It goes as the module for the Report itself so the
HasModule property of the Report must first be set to
Yes before pasting in this code.
- Option Compare Database
-
Option Explicit
-
-
Private colVals As New Collection
-
-
Private Function GetPageTot(ByVal strColumn As String _
-
, ByVal curVal As Currency _
-
, ByVal lngPage As Long) As Currency
-
Dim strKey As String
-
-
strKey = strColumn & lngPage
-
If Not ValidKey(colVar:=colVals, strKey:=strKey) Then _
-
Call colVals.Add(Item:=curVal, Key:=strKey)
-
GetPageTot = curVal
-
If lngPage > 1 Then
-
strKey = strColumn & lngPage - 1
-
GetPageTot = curVal - colVals(Index:=strKey)
-
End If
-
End Function
-
-
Private Function ValidKey(colVar As Collection, strKey As String) As Boolean
-
Dim varVal As Variant
-
-
On Error Resume Next
-
varVal = colVar(Index:=strKey)
-
ValidKey = (Err.Number = 0)
-
Call Err.Clear
-
End Function
I'll let you work from this example and convert this to work within your own project. What I show here is an illustration of the concept. It should be more than enough though.
