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.
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.
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.
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.
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 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
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.
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
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.
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.
Expand|Select|Wrap|Line Numbers
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.
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.
Create a Private Variable in the General Declarations Section of your Report to hold the Running Totals of each [Unit Price] on each Page.
Expand|Select|Wrap|Line Numbers
Private curTotalPricePerPage As Currency
In the Format() Event of the Page Header, Reset this Variable to 0.
Expand|Select|Wrap|Line Numbers
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
curTotalPricePerPage = 0
End Sub
In the Print() Event of the Detail Section, place the following Code.
Expand|Select|Wrap|Line Numbers
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then curTotalPricePerPage = curTotalPricePerPage + Me![Unit Price]
End Sub
Place an 'Unbound' Control in the Page Footer Section, for this illustration, we'll name it
Expand|Select|Wrap|Line Numbers
[txtPriceTotalPerPage]
In the Format() Event of the Page Footer, place the following Code.
Expand|Select|Wrap|Line Numbers
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
Me![txtPriceTotalPerPage] = curTotalPricePerPage
End Sub
The Text Box in the Page Footer will not contain the Per-Page Totals for [Unit Price] when Printed.
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.
Add a Textbox to the Detail Section of your Report, let's call it
Expand|Select|Wrap|Line Numbers
txtRecsPerPage
Set the Control Source Property of this Textbox t =1.
Set the Running Sum Property to Over All.
Set it's Visible Property to no.
In the Format() Event of the Detail Section, Copy-N-Paste the following Code to limit the number of Records per Page to 30:
Expand|Select|Wrap|Line Numbers
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
Now, you will be displaying 30 Records per Page as well as Page Totals.
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.
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
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 :
Do your work for you.
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.
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.
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.
Hello;
Simple question for most I'm sure. I'm curious how many records MS
Access will store in a single database? What happens when you reach
that number of records? Do you just lose the ability...
Thanks for taking the time to read my question.
I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own...
hi
I am new to this forums but it looks like some knoweable people and some good discussions!!!
I have 2 excel files that contain certain HR information that i import into 2 tables in access...
Hey Gang!
I am at work, will attempt to make it brief:-)
Looks like I am unable to print subform data results. When the subform loads with the results I want, say from 9,000 records, I get 100...
Hi All..
I want to access records from access database and edit them and save the changes using visual basic.. How can i do that ???
Can anyone please help me???
Dear All,
I joined newly to Bytes :)
Am stuck in the following issue like a week, and couldent find proper fix :(
I ll try to breif:
I have a recordset, of record count over 4000 records....
Hi,
I am having problem with duplicate data appearing in the report. I have selected all fields and in the property sheet I have selected "Yes" for the Hide Duplicate fields.
In the query...
I have a table with 10 records in it, and I have created a report and added existing fields.
The report shows one record on each page (There are therefore 10 pages)
I need all 10 records on one...
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...