nissanjon
asked on
Page Totals based on subform data
I just purchased VIP service out of total frustration. Please help reduce my stress level!! I'll give as many points as I can to whoever can help.
I have a form with a subform for Bills of Lading reports. The main is bound to the bol_info table. The subform is bound to the bol_transactions table. Both tables are linked by the bol_number field. We have to print these Bills of Lading on an old-school, tractor fed, Dot Matrix printer with pre-printed BOL forms.
I have to limit each page to 8 line items in the subform. After 8 items, the new page starts. I have this working by playing with the size and placement of the subform within the main form. Multi-page orders print beautifully.
I already have the report grand totals printed at the bottom of every page (total units and total weight).
My problem is that I need to print the page totals on each page (units on this page and weight for this page).
For some reason, everything I have tried, whether it be just Access, or even VBA, everything seems to ignore the first page's data. For example...Page1 total will show Page2's first record, and Page2's total will show Page2's 2nd record.
I have tried the following examples, and it turns out they are all different ways to give me the same incorrect results.
https://www.experts-exchange.com/questions/20773405/Totals-Per-Page.html
https://www.experts-exchange.com/questions/20446904/Summing-Group-Data-on-page-footer.html
https://www.experts-exchange.com/questions/20084618/Report-get-total-of-numric-field's-for-each-page-only.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;216311&Product=acc20
I think I know why though. None of the examples mention anything about using a subform.
It does not matter to me if the solution must be done with Forms or Reports. Please help!!!
I have a form with a subform for Bills of Lading reports. The main is bound to the bol_info table. The subform is bound to the bol_transactions table. Both tables are linked by the bol_number field. We have to print these Bills of Lading on an old-school, tractor fed, Dot Matrix printer with pre-printed BOL forms.
I have to limit each page to 8 line items in the subform. After 8 items, the new page starts. I have this working by playing with the size and placement of the subform within the main form. Multi-page orders print beautifully.
I already have the report grand totals printed at the bottom of every page (total units and total weight).
My problem is that I need to print the page totals on each page (units on this page and weight for this page).
For some reason, everything I have tried, whether it be just Access, or even VBA, everything seems to ignore the first page's data. For example...Page1 total will show Page2's first record, and Page2's total will show Page2's 2nd record.
I have tried the following examples, and it turns out they are all different ways to give me the same incorrect results.
https://www.experts-exchange.com/questions/20773405/Totals-Per-Page.html
https://www.experts-exchange.com/questions/20446904/Summing-Group-Data-on-page-footer.html
https://www.experts-exchange.com/questions/20084618/Report-get-total-of-numric-field's-for-each-page-only.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;216311&Product=acc20
I think I know why though. None of the examples mention anything about using a subform.
It does not matter to me if the solution must be done with Forms or Reports. Please help!!!
ASKER
Ouch...Well, if I drop the main/sub model, how can I handle multi page orders cleanly?
here goes:
At the top of each main report is a BOL number, entered by hand. The main report uses several combo boxes which are bound to fields in bol_info. These fields simply link to other tables, so when i select combo box items, other fields are populated. Say I pick Shipper_ID, shipper name/address/etc is then pulled from the shipper table and put on the form. (this basically pulls info from Shipper, Buyer, Carrier tables)
The subform is linked by BOL number and lists the BOL's line items (what they ordered).
At the bottom of the main form is just a couple of grand total fields which print on every page. I'd just like to be able to print page totals there as well.
here goes:
At the top of each main report is a BOL number, entered by hand. The main report uses several combo boxes which are bound to fields in bol_info. These fields simply link to other tables, so when i select combo box items, other fields are populated. Say I pick Shipper_ID, shipper name/address/etc is then pulled from the shipper table and put on the form. (this basically pulls info from Shipper, Buyer, Carrier tables)
The subform is linked by BOL number and lists the BOL's line items (what they ordered).
At the bottom of the main form is just a couple of grand total fields which print on every page. I'd just like to be able to print page totals there as well.
Hi Nissan,
just need to clarify: are you havin problems with forms or reports ?
Sven
just need to clarify: are you havin problems with forms or reports ?
Sven
Hey nissanjon!
The news you didn't want to here is that Access reports don't handle page totals easily.
If the data underlying the report can be made to have a page number assigned to it, then... Houston, we're go for launch
I have seen a function that was built for use in a query that would assign a page number to the records output in a query so that the page number was used as any other group would be on a report format. (I've been searching for that since SK answered up...)
The function required a parameter of how many items would be reported on each page (consistant with your spec on limiting to 8 items) and would assign the pages 1 to the first 8, 2 to the second 8 etc. I didn't like when you tried to get real fancy with the groupings but it did do what the author said it would.
regards
Jack
The news you didn't want to here is that Access reports don't handle page totals easily.
If the data underlying the report can be made to have a page number assigned to it, then... Houston, we're go for launch
I have seen a function that was built for use in a query that would assign a page number to the records output in a query so that the page number was used as any other group would be on a report format. (I've been searching for that since SK answered up...)
The function required a parameter of how many items would be reported on each page (consistant with your spec on limiting to 8 items) and would assign the pages 1 to the first 8, 2 to the second 8 etc. I didn't like when you tried to get real fancy with the groupings but it did do what the author said it would.
regards
Jack
ASKER
To clarify, Sven, The Forms do not seem to have this capability. If at all, I think the Report's Page Footers would be the closest thing I need, but as jadedata says, Access is limited in this regard as well. I think it has to do with the fact that the data I'm trying to page break on is from the subform, and the page breaks when the subform has no more room, it is not broken based on row counts or anything logical/programmatic.
Jadedata, if you find a link, I'd sure like to check it out. It does sound a lot more complex than this needs to be though. However, I could always add a field to the table for page number but that could also get messy...let's just say I did that, what then would I have to do to make this work?
Jadedata, if you find a link, I'd sure like to check it out. It does sound a lot more complex than this needs to be though. However, I could always add a field to the table for page number but that could also get messy...let's just say I did that, what then would I have to do to make this work?
You wouldn't add any new fields, the expression calling the function creates the value for the page numbering on the fly. You would just use that number as a group in your report, which wouldn't even need to be displayed.
ASKER
Jadedata - I think I misunderstood, based on " If the data underlying the report can be made to have a page number assigned to it,..." I thought you meant something else.
Any luck finding that function? I'm at a complete loss.
Would it be easier to just lose the subform, have everything on one form, and then find a way to create additional pages of the same record?
Any luck finding that function? I'm at a complete loss.
Would it be easier to just lose the subform, have everything on one form, and then find a way to create additional pages of the same record?
ASKER
Does anyone else have a clue on how to pull subform data onto a report's main page totals?
I tried to recreate the function from memory without success.
Although I wrote would allow the function could be used to fill a field in a table to be reported from.
It would require that you 'pre-stage' the recordset for the report, and loop thru the records, hard-assigning page numbers to each record.
(and it would require the additon of that field I thought wouldn't be needed...sorry)
The result of this would mean that you could acquire the totals for a page from the underlying recordset by asking for
SELECT Sum([Amount]) as sAmt FROM Table WHERE ([PageNum] = 8);
Although I wrote would allow the function could be used to fill a field in a table to be reported from.
It would require that you 'pre-stage' the recordset for the report, and loop thru the records, hard-assigning page numbers to each record.
(and it would require the additon of that field I thought wouldn't be needed...sorry)
The result of this would mean that you could acquire the totals for a page from the underlying recordset by asking for
SELECT Sum([Amount]) as sAmt FROM Table WHERE ([PageNum] = 8);
ASKER
Thank you jadedata.
I think I will give it a shot as it sounds like it could work. I just have one question. Based on your expertise, do you think it is better keep the subform/mainform layout and try this workaround, or perhaps revert to a single form layout? That's how I had it originally. The reason I changed to the sub/main layout was to have multi page functionality...I just didn't know that page totals would be this difficult.
THanks again! In the mean time i'll be working on your suggestions...
I think I will give it a shot as it sounds like it could work. I just have one question. Based on your expertise, do you think it is better keep the subform/mainform layout and try this workaround, or perhaps revert to a single form layout? That's how I had it originally. The reason I changed to the sub/main layout was to have multi page functionality...I just didn't know that page totals would be this difficult.
THanks again! In the mean time i'll be working on your suggestions...
You only issue (i think) is that Access doesn't like the page totals thing from data in a sub-data set. You can put total controls in the page footers of reports, but the page footer is not the same kind of object as your average data-driven group section footer, and is subject to "slippage" due to the capacity of reports to "rewind and reformat" when they discover something doesn't fit on a page.
If single form would work without workarounds, use it.
let me know which way you decide.
If single form would work without workarounds, use it.
let me know which way you decide.
ASKER
Ok I have the subform's table/query edited so that there is a PageNumber field. This will have to be manually entered by the user and if the order requires more than 8 line items, they will just have to know to enter 2 for the 2nd page, and if >16 items, 3 for the 3rd page, etc. (Not sure how my boss will like this...)
Anyway I'll keep playing around with this...any other tips welcome!
Anyway I'll keep playing around with this...any other tips welcome!
ASKER
SELECT Sum([Amount]) as sAmt FROM Table WHERE ([PageNum] = 8);
Getting tricky - I'll just set this up as another query and have the page footer control refer to it. Is that what you meant?
Getting tricky - I'll just set this up as another query and have the page footer control refer to it. Is that what you meant?
Very much like what I meant... I presume you have inserted the PageNum field into one of the lower level tables...
How is the number of items on a page currently being controlled???
Normally whatever details belong in a group are placed there and the report format dynamically Shrinks/Grows groups to meet the needs of the underlying data.
We DO NOT want to be creating any additional decision requirements for users. This whole thing should be transparent to the user.
Normally whatever details belong in a group are placed there and the report format dynamically Shrinks/Grows groups to meet the needs of the underlying data.
We DO NOT want to be creating any additional decision requirements for users. This whole thing should be transparent to the user.
ASKER
ok i'm lost...i have the existing query with a pagecount field that sums the number of units field....but I don't think that's what I"m looking for.
Now that each item has a page number, wouldn't I use the Report's Print events to determine which items print on which page?
Now that each item has a page number, wouldn't I use the Report's Print events to determine which items print on which page?
ASKER
OK so we do not want the user entering each item's page number? I agree, I'm just not envisioning this at the code level.
Right now, I have the page header and footer aligned so that the main form's info prints perfectly on the BOL forms we have. The subform is sized so that 8 line items will fit between the head/foot before forcing a new page.
Right now, I have the page header and footer aligned so that the main form's info prints perfectly on the BOL forms we have. The subform is sized so that 8 line items will fit between the head/foot before forcing a new page.
Just to be clear here, you are saying that you have FORM items configure in such and such manner,... you should be using a REPORT for this output, not a form. Forms do not have the same properties and methods used for controlling printed output.
ASKER
Right now I'm working with both...whichever one works first, I'll go with ;)
I use a form for the users to enter data. I have a print button on it that takes it to the report to print out. I am open to either printing via form or report.
I use a form for the users to enter data. I have a print button on it that takes it to the report to print out. I am open to either printing via form or report.
ASKER
I just wanted to bump this back into view...I'm still looking for help. Right now I am toying around with vba code, trying to figure out a way to determine which subform records are on which page, and after 8 records, reset value to 0, etc/etc/etc. I have code and examples of code, and a lot of keywords that do not give errors, but I have no results in terms of getting any page totals.
Please help!
Please help!
I told you this was gonna be a bugger...
If you have an invoice that has 12 items on it, does it matter which 8 items go on the first page? My thought is that you could "Assign" the page number in advance and use that assignment as a basis for the form display or report grouping...
If you have an invoice that has 12 items on it, does it matter which 8 items go on the first page? My thought is that you could "Assign" the page number in advance and use that assignment as a basis for the form display or report grouping...
ASKER
It doesn't matter which items are on which page, as long as the page sum is correct.
I have a backup copy fully prepared to handle assigning page numbers in advance, but I still wouldn't really know where to go from there.
I have a backup copy fully prepared to handle assigning page numbers in advance, but I still wouldn't really know where to go from there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow. thanks! I will study this on lunch and give it a shot this afternoon!!! I'll post my results...
ASKER
Jadedata - I think you are an absolute Access Guru. However your code is only confusing me. Don't get me wrong, I really do appreciate it. I just don't know how to use it.
I also had a thought - I'm playing around with 1 single form. Instead of having 1 form with 1 table source and a subform with a linking table source, why not just have 1 main form based on a query? Right now I am working on getting all fields into 1 form. So far all the data is there, but only 1 record from the details shows at a time, when I should have 8.
I will worry about getting the Forms functional, but how can I use Sorting/Grouping in the Report so that I can have a single main form entry up top and multiple line item entries in the Detail, where main form record id = details record id?
I also had a thought - I'm playing around with 1 single form. Instead of having 1 form with 1 table source and a subform with a linking table source, why not just have 1 main form based on a query? Right now I am working on getting all fields into 1 form. So far all the data is there, but only 1 record from the details shows at a time, when I should have 8.
I will worry about getting the Forms functional, but how can I use Sorting/Grouping in the Report so that I can have a single main form entry up top and multiple line item entries in the Detail, where main form record id = details record id?
ASKER
Nevermind - this single form method just won't work. I am unable to add/edit data.
Drop the two functions above into a separate Module name bas_HandlePgNums.
Compile your project
Work out the errors you can, I'll help you thru the rest.
Add a field called PgNum to the lowest level table used in the report (Invoice Item level)
Try the Testrs function AFTER you set it up to act on a different table than currently in the code.
Look at the results, I'd almost guarantee the light will come on...
Compile your project
Work out the errors you can, I'll help you thru the rest.
Add a field called PgNum to the lowest level table used in the report (Invoice Item level)
Try the Testrs function AFTER you set it up to act on a different table than currently in the code.
Look at the results, I'd almost guarantee the light will come on...
PS: I am currently rated as a Wizard, Guru is at 1,000,000 points
ASKER
After compilation, I got an error with the DAO code, so I changed the DIM statements to ADO. It's making things even worse. I'll just put it back the way it was, and I'm going to study it and play with it for a while. I'll post again when I can (hopefully) make some sense of it.
Thanks Jadedata!
Assure that the MS DAO 3.6 is referenced in VBA Editor/Tools/References
The basic premise is that:
You give build a recordset with Testrs function
That recordset (in that order) is iterated through, one record at a time.
Each record gets assigned a page number based on its ordinal position in the set and the limit on number of records per page so be sure to sort your data by Invoice Num,....
The basic premise is that:
You give build a recordset with Testrs function
That recordset (in that order) is iterated through, one record at a time.
Each record gets assigned a page number based on its ordinal position in the set and the limit on number of records per page so be sure to sort your data by Invoice Num,....
the zPageAssign has two modes,... Reset and Run. Reset will reset the internal counters. Run will assign the pages to records.
Note: You don't have to assign page numbers to ALL the records, ALL the time... Use a filter (WHERE condition) in the recordset to apply page numbers to only certain groups of records.
Note: You don't have to assign page numbers to ALL the records, ALL the time... Use a filter (WHERE condition) in the recordset to apply page numbers to only certain groups of records.
Thanx for the question nissanjon!
if I've understood you correctly, I believe I have had you problem, and found no immediate solution, but did a work-around by dropping the main/sub-report model.
-- Your report design: Main report prints some report summary details for each main item, and then sub-report prints all the details?
Actually you should use the sections to group in this way, - it's more precise. Could you specify your task a bit more detailed so I may better help you?
Kind regards,
Sven