Solved

Page Totals based on subform data

Posted on 2003-11-13
32
497 Views
Last Modified: 2006-11-17
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.  

http://www.experts-exchange.com/Databases/MS_Access/Q_20773405.html
http://www.experts-exchange.com/Databases/MS_Access/Q_20446904.html
http://www.experts-exchange.com/Databases/MS_Access/Q_20084618.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!!!  

0
Comment
Question by:nissanjon
  • 16
  • 14
  • 2
32 Comments
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9743034
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,
Sven
0
 

Author Comment

by:nissanjon
ID: 9743148
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.

0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9743258
Hi Nissan,

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


Sven
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9743276
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
0
 

Author Comment

by:nissanjon
ID: 9743347
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?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9743450
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.
0
 

Author Comment

by:nissanjon
ID: 9743490
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?
0
 

Author Comment

by:nissanjon
ID: 9749344
Does anyone else have a clue on how to pull subform data onto a report's main page totals?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9749401
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);

0
 

Author Comment

by:nissanjon
ID: 9749628
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...
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9749667
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.
0
 

Author Comment

by:nissanjon
ID: 9749924
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!

0
 

Author Comment

by:nissanjon
ID: 9749971
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?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9750006
Very much like what I meant... I presume you have inserted the PageNum field into one of the lower level tables...
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9750026
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.
0
 

Author Comment

by:nissanjon
ID: 9750066
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?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:nissanjon
ID: 9750084
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.  
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9750200
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.
0
 

Author Comment

by:nissanjon
ID: 9750281
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.
0
 

Author Comment

by:nissanjon
ID: 9765760
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!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9765925
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...

0
 

Author Comment

by:nissanjon
ID: 9765960
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.

0
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 9766123
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
  Else
    zTotalItems = zTotalItems + 1
    If zPageItems = 0 Then
      zPageNumber = 1
      zPageItems = 1
    Else
      zPageItems = zPageItems + 1
      If zPageItems > iItemsPerPage Then
        zPageItems = 1
        zPageNumber = zPageNumber + 1
      End If
    End If
  End If
 
  If Reset Then
    zPageCounter = zTotalPages
  Else
    zPageCounter = zPageNumber
  End If
 
zPageAssign_Exit:
  On Error Resume Next
  Exit Function
 
zPageAssign_Err:
  Select Case Err
  Case Else
    MsgBox Error$
    Resume zPageAssign_Exit:
    Resume
  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)
  rs.MoveLast
  recs = rs.RecordCount
  rs.MoveFirst
  zPageCounter recs, 8, True
  Do Until rs.EOF
    rs.Edit
      rs("PgNum") = zPageCounter(recs, 8, False)
      Debug.Print Nz(rs("ModalityClassCode"), "--") & vbTab & rs("CPTCode") & vbTab & rs("PgNum")
    rs.Update
    rs.Move 1
  Loop
 
End Function
0
 

Author Comment

by:nissanjon
ID: 9766254
wow.  thanks!  I will study this on lunch and give it a shot this afternoon!!!  I'll post my results...
0
 

Author Comment

by:nissanjon
ID: 9767264
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?
0
 

Author Comment

by:nissanjon
ID: 9767298
Nevermind - this single form method just won't work.  I am unable to add/edit data.

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9767613
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...
 
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9767615
PS: I am currently rated as a Wizard, Guru is at 1,000,000 points
0
 

Author Comment

by:nissanjon
ID: 9767938

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!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9771320
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,....

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9771343
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.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9771805
Thanx for the question nissanjon!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now