Diplaying Specific Totals from a Subform/table on the Parent Form (1 Viewer)

Hi all, I'm building a form. It's based upon a table "Orders", within this is an "Invoices" subform (Datasheet).

Each entry in the subform has a type (text) and a value (currency).

My aim is to display to the totals at the bottom of the parent form in a text box. The catch is I need 3 totals:
Total for "Type1"
Total for "Type2"
Total for All others

I also want to be able to sum these 3 text boxes to 2 records in the parent, so another subform would not help.

I created a few queries that create the results, but have no idea whether this is useful at all as I haven't been able to make them work with a text box

I've been searching for a while now but can't find anything that seems to tackle this.

I have almost no experience with form building and would be extremely grateful for any suggestions on this.
Hope I've explained this well, Thanks Richard

NB I am hoping for a quick resolution so have posted to 2 forums at the same time. I respect your time and will update my issue is resolved. I hope this does not put you off helping. ht tp://forums.aspfree.com/microsoft-access-help-18/diplaying-specific-totals-from-a-subform-table-on-the-parent-498442.html

I created a few queries that create the results, but have no idea whether this is useful at all as I haven't been able to make them work with a text box.

You seem to have the answer at your fingertips.

All you need is to have a Text Box on your Form with the result as the Control Source.

I assume you are using a Bound Form.

Other than that I can't see where you have a problem.

The Form itself is bound to the orders table. The queries take information from invoices table (with OrderNumber being the relationship between the two tables)
I can find no way of displaying the correct total on the form with a text box.
It has struck me that dsum and potentially dlookup could be useful in getting the results from a query. I have several questions though

• Whenever I use the criteria option in dsum, the text box displays #error (the same is true with dlookup). Is there common issue that might cause this?
• I haven't worked/found out how to create a bound type link to the form, so if you're looking at order 1 then the total is for order 1 also and when you move to 2 it shows the totals for 2 etc.
• If the value is based upon a query, will there need to be vba or macro code to have it requery when the subform/table is modified?

Any help on these question is much appreciated.

Alternatively, if you have a different/better method I can try I would be grateful for it.

Thanks,
Richard

You have me confused.

Did you not say that you were successful in creating a result in your query?

If this is correct then all you need to do is to add a text Box with its source as the result in the query.

No calculations are required on the form as you already have the result calculated in the query.

I assume you are using this query as the Record Source for the Form.

If on the other hand you are wanting a Running Sum (Total Increases as you progress through the Records) then you need something completly different. If this is the case I can post a sample DB but may take me till tomorrow to find it.

The form is based upon the "orders" table. This is the initial point of entry for all the information being entered into the database.

I'm aiming to replicate the function of an excel spreadsheet that is currently used. It shows the total for each cost type at the bottom of the form.

The queries are something I did when exploring options. Currently I'm exploring the use of DSum.
I have found/been shown the following resources that are very useful

htt p://ww w.techonthenet.com/access/functions/index.php

htt p://office.microsoft.com/en-us/access-help/dsum-function-HA001228829.aspx?CTT=1

This one is particularly useful for syntax
htt p://msdn.microsoft.com/en-us/library/ff193998.aspx

Apologies for the knaff links, you'll have to remove the spaces

I'm sorry but I don't know how to help. You are not replying to my questions.

RainLover said:

I'm sorry but I don't know how to help. You are not replying to my questions.

Click to expand...

RainLover said:

Did you not say that you were successful in creating a result in your query?
...
I assume you are using this query as the Record Source for the Form

Click to expand...

RJGoodhead said:

The form is based upon the "orders" table. This is the initial point of entry for all the information being entered into the database.

Click to expand...

I am trying to answer your questions but also to post on what I have been attempting in case this helps convey my issue.

I appreciate your help as I'm new to Forms in Access, I'm still trying to piece together an understanding of how forms can (and cannot) tie elements together

I have now fully solved the issue of displaying total costs, based upon a subform.
I used the dsum method as suggested by alansidman on 'forums.aspfree'.

In order to refresh these values when the subform is updated I created an event procedure attached to the "On Exit" option for the subform. the command used was Code:
me.requery
this refreshes the calculation in the same way as pressing F9.

I hope this is of use to anyone who encounters this issue in the future.