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.;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!!!  

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi 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,
nissanjonAuthor Commented:
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.

Hi Nissan,

just need to clarify: are you havin problems with forms or reports ?

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jadedataMS Access Systems CreatorCommented:
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.
nissanjonAuthor Commented:
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?
jadedataMS Access Systems CreatorCommented:
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.
nissanjonAuthor Commented:
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?
nissanjonAuthor Commented:
Does anyone else have a clue on how to pull subform data onto a report's main page totals?
jadedataMS Access Systems CreatorCommented:
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);

nissanjonAuthor Commented:
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...
jadedataMS Access Systems CreatorCommented:
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.
nissanjonAuthor Commented:
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!

nissanjonAuthor Commented:
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?
jadedataMS Access Systems CreatorCommented:
Very much like what I meant... I presume you have inserted the PageNum field into one of the lower level tables...
jadedataMS Access Systems CreatorCommented:
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.
nissanjonAuthor Commented:
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?
nissanjonAuthor Commented:
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.  
jadedataMS Access Systems CreatorCommented:
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.
nissanjonAuthor Commented:
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.
nissanjonAuthor Commented:
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!
jadedataMS Access Systems CreatorCommented:
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...

nissanjonAuthor Commented:
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.

jadedataMS Access Systems CreatorCommented:
The two below function are
  zPageAssign = Calculates what page an item should be on based on number of records in a set and how many per page requested, assigns that page number to a field name PgNum in any table/query specified by the rs in the function Testrs.  I recommend that before running the Testrs function you conduct a clear
  Testrs = Test the workability of zPageAssign

Note:  No error checking is included in these functions.
They are provided free of charge for evaluation and must be refitted into the database in which they are used.

After running the functions, your report should include a group footer based on the PgNum field of the data.  The normal method of paginating a report MAY CONFLICT with this assigned page number.
  Public Function zPageAssign(iRowCount As Long, iItemsPerPage As Integer, Reset As Boolean) As Long

  Static zPageNumber
  Static zPageItems
  Static zTotalPages
  Static zTotalItems
  On Error GoTo zPageAssign_Err:
  If Reset Then
    zPageNumber = 0
    zPageItems = 0
    zTotalItems = 0
    zTotalPages = iRowCount / iItemsPerPage
    If zTotalPages > Int(zTotalPages) Then
      zTotalPages = Int(zTotalPages)
      zTotalPages = zTotalPages + 1
    End If
    zTotalItems = zTotalItems + 1
    If zPageItems = 0 Then
      zPageNumber = 1
      zPageItems = 1
      zPageItems = zPageItems + 1
      If zPageItems > iItemsPerPage Then
        zPageItems = 1
        zPageNumber = zPageNumber + 1
      End If
    End If
  End If
  If Reset Then
    zPageCounter = zTotalPages
    zPageCounter = zPageNumber
  End If
  On Error Resume Next
  Exit Function
  Select Case Err
  Case Else
    MsgBox Error$
    Resume zPageAssign_Exit:
  End Select
End Function

Public Function Testrs()
  Dim ssql As String, db as DAO.database, rs As DAO.Recordset
  Dim recs As Long
  'clear the rs field
  set db = currentdb
   db.execute "UPDATE trefCPTCodes SET PgNum=Null;", dbfailonerror

  ssql = "SELECT * FROM trefCPTCodes ORDER BY ModalityClassCode, CPTCode;"
  Set rs = CurrentDb.OpenRecordset(ssql, dbOpenDynaset)
  recs = rs.RecordCount
  zPageCounter recs, 8, True
  Do Until rs.EOF
      rs("PgNum") = zPageCounter(recs, 8, False)
      Debug.Print Nz(rs("ModalityClassCode"), "--") & vbTab & rs("CPTCode") & vbTab & rs("PgNum")
    rs.Move 1
End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nissanjonAuthor Commented:
wow.  thanks!  I will study this on lunch and give it a shot this afternoon!!!  I'll post my results...
nissanjonAuthor Commented:
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?
nissanjonAuthor Commented:
Nevermind - this single form method just won't work.  I am unable to add/edit data.

jadedataMS Access Systems CreatorCommented:
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...
jadedataMS Access Systems CreatorCommented:
PS: I am currently rated as a Wizard, Guru is at 1,000,000 points
nissanjonAuthor Commented:

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!
jadedataMS Access Systems CreatorCommented:
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,....

jadedataMS Access Systems CreatorCommented:
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.
jadedataMS Access Systems CreatorCommented:
Thanx for the question nissanjon!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.