30 records per Report MS access

[ZKAHADI]

hi buddies....

need your help kindly see the image.

i made a report of all students fee i want to print this report on one legal paper with 30 records with total sum of their fee/page.

Explain: for example i have 400 students and i print the whole database and in last i get the total sum of the fee. so i want to print only 30 record of students on page and i need total sum of the students fee in every report of 30 students per page. not in one last paper of 400 students. mean total 13 pages will be printed of 400 students and i need the sum totals on every page.

is it possible please help!

[NeoPa]

It sounds like you want Page totals as well as, separately, Report totals - yes?

You can have Group totals and Report totals easily enough with a simple design but Report & Page totals would require some VBA code I believe. It's also pretty complicated because you have to handle the Report being shown in the normal direction - as well as handling situations where the user decides to go backwards to previous pages they've already been over.

To handle this properly you'd need to write code into the Report's OnRetreat Event.

[ZKAHADI]

first of all i want to explain what i want to do. this report belongs to the students fee structure i will show in below image. every separate fee column shown in report total in below textbox which shows how much fee collected for that , what ever you can call faculty or department and in last showing the totals of totals. from up to down showing total of total and from left to right showing total of above separate columns of each department. as you can see i just tried to put some bogus data inside the table and i see the totals at last page. i want to print every 30 students single report with footer with totals on legal page. i dont want see the 400 students all totals at last page.
second how to code as you say on event please tell.

[NeoPa]

Hi.

Annoyingly I just lost my reply because the time had expired on my page - invisibly (Grrrr).

This is less straightforward than I'd expected because aggregation (Summing, Averages, etc) functions only work on Group & Report Headers/Footers. Not on Page Headers/Footers.

It can still be done, using invisible Controls and the .RunningSum property of the TextBox, but that will take time for me to investigate & test. I'll post back when I have something that will help you. It may take a short while but somewhen over the weekend I expect.

[NeoPa]

First of all let's post the example database as a proof of concept. My supper is calling me so I'll have to write out the explanation later, but in the meantime you can examine the attached as it's all very basic really.

I will add an explanation later though obviously.

[ZKAHADI]

See below attached files

i have attached my database report and attach an image. i want to print out the 400 students report like this. 30 students data per legal page in landscape and in every page i need the total sum of all columns on every page. do experiment on my database and re upload

[NeoPa]

I'll make allowances for your use of the language, but let me be clear :
This is not somewhere where you simply order up work from a cheap resource. You ask technical questions and we answer as we can. The responsibility for the actual work lies with you, and you alone.

So, no. Feel free to pay someone for work if you don't feel able to do it for yourself. My job will end when I've posted, as I promised, an explanation of how this works. After that it's up to you. I will happily answer questions on my explanation if it turns out not to be clear enough however.

[ZKAHADI]

brother you made same database as i made. but the main thing i need is still pending. and this is not just for fun its my database for my self not for selling. and i need to make a hard printed file from this obtained access reports. and i need 30 students data on legal 18x14 page. which can paste in hard folder in print form. so i need to get the 400 students print on 13 separate pages with totals end on every page

[ZKAHADI]

i tried apply break page option but it only show one record per page.

[Rabbit]

One way to accomplish this is to use a ranking query to number the rows and integer division on the rank to split the rows into groups.

[ZKAHADI]

how? to do that? i have uploaded my database file try on it

[Rabbit]

Sorry but I'm not here to do your work for you. And I don't download files from people I don't know.

The link in my previous post contains the info you need to set up a ranking query yourself. I'm happy to answer any questions you might have about it, but you'll have to do the work yourself.

[NeoPa]

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 :

1. They include a Running Sum of the value (See the RunningSum property of the TextBox).
2. 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.

[ADezii]

The bad news is, as previously mentioned, Access does not inherently support Totals in a Page Footer. The good news is, that you can implement this functionality in only four lines of Code. For this illustration, I created a simple, in-line Report, no Grouping Levels, based on the Order Details Table of the Northwind 2007 Sample Database. The idea is to display the Totals for the [Unit Price] Field on a Page-by-Page basis. Here are the steps necessary to accomplish this.

1. Create a Private Variable in the General Declarations Section of your Report to hold the Running Totals of each [Unit Price] on each Page.
   

   Private curTotalPricePerPage As Currency

2. In the Format() Event of the Page Header, Reset this Variable to 0.
   

   Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
     curTotalPricePerPage = 0
   End Sub

3. In the Print() Event of the Detail Section, place the following Code.
   

   Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
     If PrintCount = 1 Then curTotalPricePerPage = curTotalPricePerPage + Me![Unit Price]
   End Sub

4. Place an 'Unbound' Control in the Page Footer Section, for this illustration, we'll name it
   

   [txtPriceTotalPerPage]

5. In the Format() Event of the Page Footer, place the following Code.
   

   Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
     Me![txtPriceTotalPerPage] = curTotalPricePerPage
   End Sub

6. The Text Box in the Page Footer will not contain the Per-Page Totals for [Unit Price] when Printed.

[ADezii]

I figured that I would keep this Topic a separate Post. In addition to generating Page Totals, if you also want to limit each Page to 30 Records, then you would need to also integrate the following Code into the above.

1. Add a Textbox to the Detail Section of your Report, let's call it
   

   txtRecsPerPage

2. Set the Control Source Property of this Textbox t =1.
3. Set the Running Sum Property to Over All.
4. Set it's Visible Property to no.
5. In the Format() Event of the Detail Section, Copy-N-Paste the following Code to limit the number of Records per Page to 30:
   

   Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Const conNONE = 0
   Const con_AFTER_SECTION = 2
   
   If Me![txtRecsPerPage] Mod 30 = 0 Then
     Me.Detail.ForceNewPage = con_AFTER_SECTION
   Else
     Me.Detail.ForceNewPage = conNONE
   End If
   End Sub

6. Now, you will be displaying 30 Records per Page as well as Page Totals.