Totalling nested subforms

[binny]

Okay I've finished my training course on Visual Basic, so I now have basic
training in Visual Basic, so to speak!
My question is this. I have 2 nested subforms and I need some way to get
the totals from the last sub form back to the main form.
I'm using the database to record nominations at a Gymkhana,

One competitor is allowed up to two horses.
Each horse can be nominated in many events.
A competitor can ride twice in and event, but a horse can only run once.

My main form has the competitor details name address etc,
embedded into that is the horse details subform to record the horses name ect.
Embedded into the horse details are sub form is the event details subform.
And it in records the actual details of the events nominated for i.e. flag
race and the nomination fee.

This system is working well, allowing me to produce reports showing which
events a competitor it competing in on what horses they will be riding in
those events.

My problem is this. I would like to be able to keep a running total of the
total nomination costs for both horses on the main form. So that as soon as
someone comes to the Secretary's table. I can tell immediately, how much
they owe and have them pay without the need to print off reports and suchlike.
I can calculate a total nomination cost for the first horse from the events
detail subform. However, when I clicked next record to record the details of
the second horse. The details of the first horse are lost.

Is there some way to insert a calculated control on the main form that will
hold its value after the subform has moved on to the next record.

By the way, if you are nesting more than one subform. YOU HAVE TO MANUALLY
set the parent-child relationship. The wizard will not do it for you
automatically.
That little tidbit is not in the help file, nor in the training course, and
took me six hours to work it out.

binny

 

[strive4peace]

Hi binny,

firstly, I do not know what a "Gymkhana" is but will try to help anyway...

"keep a running total of the total nomination costs for both horses on
the main form"

seems that if you have multiple horses, they should be in a subform ...
even though your limit is 2, it is much better to have the Horse PK
(primary key) in a related table as opposed to 2 fields in another table

read this and see if you change your mind on the way that your data is
structured:

Access Basics on Allen Browne's site
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

Warm Regards,
Crystal

*
have an awesome day
*

 

[binny]

Gymkhana is an ancient Hindu word of two parts. Gym , meaning sport or
activity and Khana meaning field or area the littoral translation is playing
field.
In the Western world. It has come to mean a horse based sporting event.
The events themselves are based on old cavalry training techniques, racing
horses in and out of and around poles catching flags off the top of poles
and putting in a bin all at full gallop it requires perfect ordination
between horse and rider. Also racing horses around three drums set up in a
triangular pattern call a barrel race, which is very popular as a woman's
event at rodeos
Thanks for the link there is no such thing as too much information, but
perhaps you should have read my question more thoroughly.
binny

 

[jacksonmacd]

Place an unbound textbox in the header or footer of the first subform.
Enter a formula to sum the costs for that horse. Place an unbound
textbox on the main form, and set its ControlSource to the unbound
textbox on the subform.

 

[Evi]

Not nice, Binny. The writer did not ask for the Etymology lesson, he just
(as he ought to do) stated his lack of knowledge of the word's meaning in
case it had any bearing on your database design - terms like flag race and
Nomination could have different meanings to other users. The writer may not
have English as his first language or he may just not be one of the 'horsey'
set but he did attempt to help you.
OK leg slap over.
If you are referring to a control in your subform in order to get your
result in the main form then as you saw, you will see a filtered result.
You may need to create a query (QryEvents) to give you results you require
and then use DSum filtering by the information in the Main/and Or subforms
to get the answer.

So assuming your db is designed correctly and you have a primary key number
field for Rider (RiderID) and your subform 1 (Sub1) contains the EventID
(Primary key number field from the Events table) then

Your Dsum would look *something* like this

=DSum("[Fees]","QryEvents","[RiderID]=" & [RiderID] & " AND [EventID]=" &
[Sub1].Form.[EventID])

The last part of the DSum is the conditions for summing the Fees field in
the query. Without this, the DSum would sum all the fields in the query

This will give you the total of both horses for that rider in that event.
I'm sure you can see how to adapt this to add more AND filters. If not, ask.

Keep the word Form as it is but replace the field and query names with the
real fields and queries. If any of your ID fields are text instead of number
then the syntax will be different.

I well remember the fun I had trying to force the Wizard into action if it
thought that I didn't need its help. I guess it encourages you to find the
Property button!

Evi

 

[binny]

Wasn't trying to be 'not nice' the origins of words and the way the English
language adopts and adapts them fascinates me.
You're right, this is a computer site I should stick to computery stuff

Thank you very much,Dsum is what I need. I guess I will have to study up on
functions a bit more
The more, you know, the more you know you don't know

Budgerigar (small parrot native Australia) 2 aboriginal words
Budgeri = good; Gar= eating or to eat
Sorry!! couldn't resist
binny

Not nice, Binny. The writer did not ask for the Etymology lesson, he just
(as he ought to do) stated his lack of knowledge of the word's meaning in
case it had any bearing on your database design - terms like flag race and
Nomination could have different meanings to other users. The writer may not
have English as his first language or he may just not be one of the 'horsey'
set but he did attempt to help you.
OK leg slap over.
If you are referring to a control in your subform in order to get your
result in the main form then as you saw, you will see a filtered result.
You may need to create a query (QryEvents) to give you results you require
and then use DSum filtering by the information in the Main/and Or subforms
to get the answer.

So assuming your db is designed correctly and you have a primary key number
field for Rider (RiderID) and your subform 1 (Sub1) contains the EventID
(Primary key number field from the Events table) then

Your Dsum would look *something* like this

=DSum("[Fees]","QryEvents","[RiderID]=" & [RiderID] & " AND [EventID]=" &
[Sub1].Form.[EventID])

The last part of the DSum is the conditions for summing the Fees field in
the query. Without this, the DSum would sum all the fields in the query

This will give you the total of both horses for that rider in that event.
I'm sure you can see how to adapt this to add more AND filters. If not, ask.

Keep the word Form as it is but replace the field and query names with the
real fields and queries. If any of your ID fields are text instead of number
then the syntax will be different.

I well remember the fun I had trying to force the Wizard into action if it
thought that I didn't need its help. I guess it encourages you to find the
Property button!

Evi

Gymkhana is an ancient Hindu word of two parts. Gym , meaning sport or

perhaps you should have read my question more thoroughly.
binny

 

[strive4peace]

Hi Binny,

thank you for explaining Gymkhana, I found that very interesting!

"However, when I clicked next record to record the details of the second
horse. The details of the first horse are lost."

you are right, I mis-interpreted what you said, my apologies to you ...

if each competitor can have 2 horses and you do indeeed have the horse
information specified on a subform and need to see TWO horse subforms at
one time, why not use the same subform twice? This way, you can show
both records at once.

how would this work?

I assume a structure similar to this:

Competitors
- CompetID, autonumber
- Lastname, text
- Firstname, text
- etc

Horses
- HorseID, autonumber
- etc

Gymkhanas
- GymkID, autonumber
- GymkDate, date
- etc

EventTypes
- EvTypID, autonumber
- EvType, text
- NomFee, currency (default value for this event type -- not sure where
this information belongs...)

Events
- EventID, autonumber
- EvTypID, long integer, FK to EventTypes
(on the form, make sure that NomFee is a column in the combo so its
value can be assigned in EventDetail for NomCost)
- etc

EventDetail
- EvDetID, autonumber
- EventID, long integer, FK to Events
- CompetID, long integer, FK to Competitors
- HorseID, long integer, FK to Horses
- HorseNum, integer -- 1 or 2; horse number for the competitor
- NomCost, currency (initial value from EventTypes.NomFee)
- etc

Unique index on the combination of: EventID and HorseID

~~~
FK is Foreign Key
~~~

your main form will be based on the Competitors table

in the RecordSource for the main form, define these 2 calculated fields:

field --> HorseNum1: cInt(1)
field --> HorseNum2: cInt(2)

the first column of the grid would probably be -->
field --> Competitors.*
(show all the fields from Competitors)

for the first subform (based on EventDetail):

LinkMasterFields --> CompetID, HorseNum1
LinkChildFields --> CompetID, HorseNum

for the second subform (same form in the subform control as used for the
first one):

LinkMasterFields --> CompetID, HorseNum2
LinkChildFields --> CompetID, HorseNum

make sure all the link fields are controls on each respective form/subform

to show the sum of the costs, you can make a calculated control on the
main form:

=IIF(subform_controlname1.form.recordset.recordcount > 0,
nz(subform_controlname1.form.NomCost,0), 0) +
IIF(subform_controlname2.form.recordset.recordcount > 0,
nz(subform_controlname2.form.NomCost,0), 0)

.... unless all you need can be shown with a dSum in a calculated control
as suggested by Evi

~~~~~~~~~~~~
"YOU HAVE TO MANUALLY set the parent-child relationship"

do you have relationships defined using the relationship diagram for
your database?

are the linking fields ON the main and subform?

(if both of these are true, then when you assign the SourceObject to a
subform control, the link fields should be automatically filled out for you)

Warm Regards,
Crystal

*
have an awesome day
*

 

[Evi]

If you get stuck, write back giving your field and query names, telling us
if the fields contain text, numbers or dates.

And get those budgies off the barbi!

Evi

Wasn't trying to be 'not nice' the origins of words and the way the English
language adopts and adapts them fascinates me.
You're right, this is a computer site I should stick to computery stuff

Thank you very much,Dsum is what I need. I guess I will have to study up on
functions a bit more
The more, you know, the more you know you don't know

Budgerigar (small parrot native Australia) 2 aboriginal words
Budgeri = good; Gar= eating or to eat
Sorry!! couldn't resist
binny

Not nice, Binny. The writer did not ask for the Etymology lesson, he just
(as he ought to do) stated his lack of knowledge of the word's meaning in
case it had any bearing on your database design - terms like flag race and
Nomination could have different meanings to other users. The writer may not
have English as his first language or he may just not be one of the 'horsey'
set but he did attempt to help you.
OK leg slap over.
If you are referring to a control in your subform in order to get your
result in the main form then as you saw, you will see a filtered result.
You may need to create a query (QryEvents) to give you results you require
and then use DSum filtering by the information in the Main/and Or subforms
to get the answer.

So assuming your db is designed correctly and you have a primary key number
field for Rider (RiderID) and your subform 1 (Sub1) contains the EventID
(Primary key number field from the Events table) then

Your Dsum would look *something* like this

=DSum("[Fees]","QryEvents","[RiderID]=" & [RiderID] & " AND [EventID]=" &
[Sub1].Form.[EventID])

The last part of the DSum is the conditions for summing the Fees field in
the query. Without this, the DSum would sum all the fields in the query

This will give you the total of both horses for that rider in that event.
I'm sure you can see how to adapt this to add more AND filters. If not, ask.

Keep the word Form as it is but replace the field and query names with the
real fields and queries. If any of your ID fields are text instead of number
then the syntax will be different.

I well remember the fun I had trying to force the Wizard into action if it
thought that I didn't need its help. I guess it encourages you to find the
Property button!

Evi

Gymkhana is an ancient Hindu word of two parts. Gym , meaning sport

or

perhaps you should have read my question more thoroughly.
binny


:

Hi binny,

firstly, I do not know what a "Gymkhana" is but will try to help anyway...

"keep a running total of the total nomination costs for both horses on
the main form"

seems that if you have multiple horses, they should be in a subform ....
even though your limit is 2, it is much better to have the Horse PK
(primary key) in a related table as opposed to 2 fields in another table

read this and see if you change your mind on the way that your data is
structured:

Access Basics on Allen Browne's site
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

Warm Regards,
Crystal

*
have an awesome day
*


binny wrote:
Okay I've finished my training course on Visual Basic, so I now

have
basic

training in Visual Basic, so to speak!
My question is this. I have 2 nested subforms and I need some

way to
get

the totals from the last sub form back to the main form.
I'm using the database to record nominations at a Gymkhana,

One competitor is allowed up to two horses.
Each horse can be nominated in many events.
A competitor can ride twice in and event, but a horse can only run once.

My main form has the competitor details name address etc,
embedded into that is the horse details subform to record the

horses
name ect.

Embedded into the horse details are sub form is the event details subform.
And it in records the actual details of the events nominated for

i.e.
flag

race and the nomination fee.

This system is working well, allowing me to produce reports

showing
which

events a competitor it competing in on what horses they will be

riding
in

those events.

My problem is this. I would like to be able to keep a running

total
of the

total nomination costs for both horses on the main form. So that

as
soon as

someone comes to the Secretary's table. I can tell immediately,

how
much

they owe and have them pay without the need to print off reports

and
suchlike.

I can calculate a total nomination cost for the first horse from

the
events

detail subform. However, when I clicked next record to record the details of
the second horse. The details of the first horse are lost.

Is there some way to insert a calculated control on the main form

that
will

hold its value after the subform has moved on to the next record.

By the way, if you are nesting more than one subform. YOU HAVE TO MANUALLY
set the parent-child relationship. The wizard will not do it for you
automatically.
That little tidbit is not in the help file, nor in the training course, and
took me six hours to work it out.

binny