Access - calculating totals from 2 subforms on a form!

[Annie]

Hi

I have designed a simple database to collect sales information about
manufacturing Kitchens and wardrobes. On my main form (created from my
main sales table) I have 2 subforms as follows:

Type Qty Price Total
1 2 2000 4000
2 2 4000 8000
3 2 3500 7000

That is the 1st subform I created and the total comes from Qty x Price.

The 2nd subform is the same with wardrobe prices - total is the same
setup.

On my main form I want to total the totals for the first subform and
then total the totals from the second subform and then add the two
together - is this possible ??? I keep getting errors.

Please can somebody help me before I throw in the towel and admit
defeat?????

Many thanks and kind regards

Ann from Dublin Ireland (I have no VBA experience)!

 

[Bob Greenblatt]

Hi

I have designed a simple database to collect sales information about
manufacturing Kitchens and wardrobes. On my main form (created from my
main sales table) I have 2 subforms as follows:

Type Qty Price Total
1 2 2000 4000
2 2 4000 8000
3 2 3500 7000

That is the 1st subform I created and the total comes from Qty x Price.

The 2nd subform is the same with wardrobe prices - total is the same
setup.

On my main form I want to total the totals for the first subform and
then total the totals from the second subform and then add the two
together - is this possible ??? I keep getting errors.

Please can somebody help me before I throw in the towel and admit
defeat?????

Many thanks and kind regards

Ann from Dublin Ireland (I have no VBA experience)!

It certainly sounds like it is possible, but this is a Macintosh forum. You
should post the question to an access forum.

 

[Jim Gordon]

Hi

I have designed a simple database to collect sales information about
manufacturing Kitchens and wardrobes. On my main form (created from my
main sales table) I have 2 subforms as follows:

Type Qty Price Total
1 2 2000 4000
2 2 4000 8000
3 2 3500 7000

That is the 1st subform I created and the total comes from Qty x Price.

The 2nd subform is the same with wardrobe prices - total is the same
setup.

On my main form I want to total the totals for the first subform and
then total the totals from the second subform and then add the two
together - is this possible ??? I keep getting errors.

Please can somebody help me before I throw in the towel and admit
defeat?????

Many thanks and kind regards

Ann from Dublin Ireland (I have no VBA experience)!

Hi Annie,

Welcome to the Macintosh Office forum.

This particular newsgroup is for general Office topics. There is a
newsgroup that is specifically for Microsoft Access forms. It is
Microsoft.public.access.forms.

Generally it is discourage to cross-post, but to help you get a faster
reply and so that you are likely to find the response quickly I am also
posting this message to the forms group, where I am sure someone will be
able to help you.

Please enjoy the newsgroups. I hope you find that they are a good place
to get help.

-Jim Gordon
Mac MVP

 

[Wayne-in-Manchester]

Hi Jim

If you are getting "error" I take it you are trying to Sum the total of your
items in the subform in a text box on the main form

There are a number of way to bring a total from a subform into the main form.

1
This way create a total on the subform and refers to it on the main form.
Open the subform in design view and ensure the headers and footers are in
view (if not this can be done from the View Menue)
Next in the footer section of the subform add an unbound textbox and call it
"SubFormTotal" then insert this into the control data section

=Sum([Total])

- if your box is not called "total" then insert the real name.
Save the subform and close.
Next
Open the main form in design view and create an unbound text box (call it
whatever makes sense to you. Insert this into it

=[InsertNameOfSubform].Form![SubFormTotal].

Save the main form and close. When you open it again you will see the total
in the main form.

2
Another method would be to create a macro to SetValue of a text box on the
main form. This would give you the total of particular order (using the
OderID as a reference). You could use something like this.

Item: Forms![MainFormName]![TextBoxName]
Expression:
DSum("[Total]","[SubFormName]","[OrderID]=Forms![MainFormName]![Order ID]")

You could (if you want) set this macro to run AfterUpdate on th subform or
where ever you want.


Wayne

If you feel this answer has been useful please check the "was this post
helpful" so I get some feedback. Thanks.

 

[Wayne-in-Manchester]

No answer - I'm off out now so you may want to to 1st see this link
http://support.microsoft.com/kb/838911/en-us

and then (if you're not using the right jet pack) you can download the
latest from microsoft at
http://support.microsoft.com/kb/239114/

Hope this helps


Wayne

Hi Jim

If you are getting "error" I take it you are trying to Sum the total of your
items in the subform in a text box on the main form

There are a number of way to bring a total from a subform into the main form.

1
This way create a total on the subform and refers to it on the main form.
Open the subform in design view and ensure the headers and footers are in
view (if not this can be done from the View Menue)
Next in the footer section of the subform add an unbound textbox and call it
"SubFormTotal" then insert this into the control data section

=Sum([Total])

- if your box is not called "total" then insert the real name.
Save the subform and close.
Next
Open the main form in design view and create an unbound text box (call it
whatever makes sense to you. Insert this into it

=[InsertNameOfSubform].Form![SubFormTotal].

Save the main form and close. When you open it again you will see the total
in the main form.

2
Another method would be to create a macro to SetValue of a text box on the
main form. This would give you the total of particular order (using the
OderID as a reference). You could use something like this.

Item: Forms![MainFormName]![TextBoxName]
Expression:
DSum("[Total]","[SubFormName]","[OrderID]=Forms![MainFormName]![Order ID]")

You could (if you want) set this macro to run AfterUpdate on th subform or
where ever you want.


Wayne

If you feel this answer has been useful please check the "was this post
helpful" so I get some feedback. Thanks.

Hi Annie,

Welcome to the Macintosh Office forum.

This particular newsgroup is for general Office topics. There is a
newsgroup that is specifically for Microsoft Access forms. It is
Microsoft.public.access.forms.

Generally it is discourage to cross-post, but to help you get a faster
reply and so that you are likely to find the response quickly I am also
posting this message to the forms group, where I am sure someone will be
able to help you.

Please enjoy the newsgroups. I hope you find that they are a good place
to get help.

-Jim Gordon
Mac MVP