Form, Subform, Totals

[mimminito]

Hello, i am new here, but have been doing access for a couple of months or so now. However i have got a problem, which seems like a very easy task when reading around and looking at samples, but i cant get it done.
Ok, basically I have got a Main Form and a Subform. The main form display details on Customers, and the Subform displays details on their orders. I would like to be able to do two things:

1. Be able to add a new row below on the Subform, below all of thier orders an choices, that will total the cost of the choices.(i can get a total at the end of the Subform as a new field, but it totals in every row)

2. I would like to be able to total the cost of the order from the Subform, and add it onto the Main form, in an unbound text box maybe?

I would like to be able to do it either way, so i have a choice

Thankyou for your time, Adam

 

[Ecron]

You could potentially build a query that has totalling enabled. If you did that, you could do a Group By selection on the primary key (the unique id of your table) and select Sum for each of the fields that you want totalled. This would give you the totalled amount for specific fields that are associated with each PK.

Then, in your recordsource on your two forms you could add the query to both and link each of them to the same PK.

 

[mimminito]

ok thankyou. could you do me a favour and try to explain something for me. i have had a look on the Northwind 2007 sample database. On the Order Details form they have got a subform, much like mine. Except they have got a totals section at the bottom. how would i go about doing this? thankyou, i have attached an image of the form i mean, and the totals section is at the botom of the subform

 

[Ecron]

I do not have the Northwind 2007 database, but from looking at it, it appears they are using a continuous subform with a header and footer.

You could create a footer and then create a textbox in that footer with the recordsource: =Sum([Qty]), that would give you the sum of the quantity for the selected order#.

 

[mimminito]

yes i tried to create a footer, but it will not display when i load up the form. its hidden for some reason. i will give it another go and post up my results with some screens. cheers

 

[mimminito]

ok done it again and still not worked. i have attached screenshots of the design view and the form view, so you can see what i ahve done to the subform

 

[Ecron]

As idiotic as this sounds, if you right-click the form and choose FormFooter, and under Format please be sure that Visible is set to Yes.

 

[boblarson]

As far as I know you can't put anything in a form footer on a form that is displayed as DATASHEET. Headers and footers won't show up in Datasheet view.

 

[mimminito]

they are all set to visible yes in the properties

 

[boblarson]

Again, you won't be able to get it to display unless the form is either in Single or Continuous and not datasheet (like it currently is displayed).

 

[mimminito]

ok, but i am not able to get it into single view when i am looking at it in the main form with subform. when i open it up all i can see is datasheet view. how do i select form view/single view?

 

[Rich]

Reference the subForm summed textbox on the Main form, it doesn't then matter if the subForm footer isn't visible

 

[boblarson]

You have to open the subform itself (not in the main form) and in it's properties set the Default View (to either "Single Forms" or "Continuous Forms") and make sure Views allowed says both. Save it, close it, and then reopen the main form.
EDIT
And Rich is correct.