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]

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.

[ZKAHADI]

this code not working.

i need to print the page like ms word with header footer 400 students 30 records per page with totals

[NeoPa]

ZKahadi:
this code not working.

Not helpful to refer to "this code" when there are multiple posts with code. Which are you talking about.

ZKahadi:
i need to print the page like ms word with header footer 400 students 30 records per page with totals

Instead of just repeating what you need like a project specification (Which behaviour is unacceptable.), why don't you try out some of the code and report where you are having difficulty.

You sound like someone who simply wants your work done for you and who isn't prepared to put any effort into their own project. If that continues I'll be obliged to close this thread and generally keep a close eye on you in future.

[ADezii]

@NeoPa:
Good to see an old friend!

[NeoPa]

Hi ADezii my friend. Always a pleasure :-)

[ZKAHADI]

here just old friends meeting . no expert here to solve my problem. they all say do it your self we not here for your work. i think here expert people live

[NeoPa]

Hi ZKahadi.

I think there may be some confusion here still as to what Bytes.com is here for and how best to use the amazing free resources we offer to everyone in the world who has internet and a technical question.

We provide answers to your technical questions. We expect you to work with those answers to produce or fix your work.

We do NOT :

1. Do your work for you.
2. Generally speaking, though we do slip up sometimes, answer questions that aren't questions at all but a simple specification for your work.

If you are finding the help here to be limited then that's because your question should not have any answers at all. At no point have you even indicated what you've done for yourself in trying to apply the very full answer that's already been provided for you.

If you come just a very short way then you'll find that people here will bend over backwards to come towards you too. So far though, all I've seen is complaints that we haven't done everything for you. Even if we wanted to, and one here loves nothing more than to do just that (Tip: Not me.), then our rules forbid it. No-one wants to encourage members who think we're here as a free resource to do their work for them. That's disrespectful and those of us who manage the site are very careful of our wonderful experts who deserve so much better treatment than that.

[twinnyfo]

I have responded to his duplicate post.

My approach would be to execute this report programmatically through VBA. I determine the total size of the batch using the record source of the main report. then using that record source, or a similar recordset, I would execute the report, one page at a time, filtering for the record in question. Just find the "break points" between records that begin the page and end the page. This would allow each page to automatically total per page.

This would not, however, give a grand total.

As mentioned in his other post, I am not sure it makes sense to have page totals. If I have 400 students, I don't CARE about "this particular set of 30 students"--I only care about the total, because it all works together.

Obviously there must be a reason, so this is my recommended solution and should produce the desired result.

Hope this hepps!

[NeoPa]

I've deleted the other thread. It is against the rules to repost the same question. In this case it appears that, having failed to progress with this one, in spite of more than one complete & full answer to their question, they've posted it again elsewhere.

I've had to spend time & effort now to PM them a warning. I have much better things to be wasting my time on :-(

NB. You may want to look below where I've already given them a full and complete solution they simply had to adjust the names of for their own use. ADezii has also posted a solution that covers a single column total that can be extended to cover multiple columns too. The answers are all there. They're simply too lazy to do even the little work involved to change some names to match their own project.