Running page count tied to volume number

[bmj121]

I have a form with a "Volume" field and a "Page" field and "Total" field. "Volume" is a book volume, so a volume contains several items that have "page" lengths. As each item is entered, the "Volume" field and "page" fields are entered. What I need is a set of code that will allow me to enter the total pages for the volume as each item is added.



Example:

Item# Volume# Pages Total
Item #1: 1 3 3
Item #2: 1 2 5
Item #3: 2 2 2
Item #4: 1 4 9
Item #5: 2 2 4


Hopefully you get the point. I know I'm coming to this with nothing to start with, and if you can only give me a piece of the puzzle, any help would be appreciated.

[June7]

That looks like a running sum over a group. This is an aggregate calculation. Saving aggregate data to table is not usually a good idea nor necessary. This calc is best done during output not data entry and can easily be done on reports. Textboxes on reports have a RunningSum property.

Showing this calc on a form and saving to table with your data entry structure would involve VBA code.

[bmj121]

That looks like a running sum over a group. This is an aggregate calculation. Saving aggregate data to table is not usually a good idea nor necessary. This calc is best done during output not data entry and can easily be done on reports. Textboxes on reports have a RunningSum property.

Showing this calc on a form and saving to table with your data entry structure would involve VBA code.

So ultimately, the page count is needed for a report, so running it in a report will work for me. The problem that I see is that I need a page start count for each item. And the page count for each item needs to appear in line with said item. I've done count totals on reports before, but I've only ever done them for the entire report or for each section. I've never had any luck getting counts for each item.

I knew that getting the page count into the form would require VBA, and I'm working in that direction, hoping to use DLookup to write the information to a table and then retrieve the information.

[June7]

The example shows a running total for each Volume#. To produce this in report the records would have to be grouped by Volume#. If you want to maintain order by Item#, that is a complication and a VBA procedure is probably the only solution.

[bmj121]

The example shows a running total for each Volume#. To produce this in report the records would have to be grouped by Volume#. If you want to maintain order by Item#, that is a complication and a VBA procedure is probably the only solution.

Here'e the code that retrieves the information from a table I set up. Now I need to figure out how to get "NewPage" into the corresponding field "PageTotal" in the table StatPages, where "Volume" = "Volume" from the form. So, almost there.
Private Sub Pages_AfterUpdate()
MyVar = DLookup("PageTotal", "StatPages", "Volume = " & Nz([Volume], 0))
Me![Start Page] = MyVar
NewPage = MyVar + Me!Pages
MsgBox NewPage
End Sub

[June7]

If PageTotal field is included in the form's RecordSource, could be:

Me!PageTotal = Me![Start Page] + Nz(DLookup("PageTotal", "StatPages", "Volume = " & [Volume]),0)