How do I set up a RunningSum in a sub form please?

D

Deekayw

I've found the RunningSum facility for Reports but can't find a way to do
it with a sub-form - can anyone advise please ?

Thanks
DK
 
A

Allen Browne

There is no RunningSum property for controls in a form, because the form is
dynamic, i.e. you modify, filter and sort them at runtime.

If you can guarantee that your form is sorted by (say) the ID field, and
that no records are filtered out, you could display a running Sum for the
Amount field with this in the Control Source of a text box:
=DSum("Amount", "Table1", "ID <= " & [ID])

You could do that more efficiently by creating a query with a subquery to
get the total. However, the form would then be read-only. If you want to
pursue that idea, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
D

Deekayw

Thanks for the very speedy response Allen, much appreciated.

I was a bit brief and vague on what I wanted to acheive -

Doing a DB for coach tour bookings

Tour Table - contains spec / costs / venue etc
linked to
Booking - Main - contains lead customer details, booking
date etc etc
this links to
Bookings - extra - contains individual passenger names, seat
number
pickup, room types etc

My main form has a sub form which is for the data that I type into Bookings
- extra, and as I need to keep peoples names in the order I type them into
the Form for rooming requirements. Up to now I have an initial field I type
from 1 to ...
so that when I do a query or report based on the booking ref lines of entra
in Bookings - extra it brings them up in the same order as input (which isn't
alphabetical or anything)

Hope that explains it a bit better - would that need a different way of
numbering >?

Thanks again

DK
 
A

Allen Browne

So your [Bookings - extra] table has a number field in it, and you want to
assign the next number (1 more than the highest assigned so far) when you
add a new record in the subform?

You could do that in the BeforeInsert event of the subform.
Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Choose the entry in the main form first."
Else
strWhere = "ID = " & Me.Parent![ID]
Me.Num = Nz(DMax("Num", "[Bookings - extra]", strWhere), 0) + 1
End If
End Sub
 
Top