Adding totals to a form from a subform

[Niki]

I have a total line in my subform. I would like the data from the total line to be shown in a field of the same name in the main form. I have used the expression builder, but, as the total column is not part of the design I can't figure out what to call it in my expression.



Must I put a total field in the footer so it's a visible part of the subform or can I use the simple total column somehow?

Thanks!

[June7]

Please clarify, how is it the total column is not part of the design - what is the design - it is part of the RecordSource? Showing the value in a box of the subform and then referring to the box in another on the main form would be simplest. The box on subform could be invisble.

[Niki]

Thank you for your response. I have a subform with combo boxes that allow me to select entries from tables. In the main form, I would like to show totals of some of the columns of the selected entries from the subform.

For instance, selected in the subform is "Red, 1, 2, 3, 4, 5" and then "Blue, 5, 4, 3, 2, 1". The Totals line underneath should read "nothing, 6, 6, 6, 6, 6".

I would like the 6's to then pop up in the main form where I tell them to.

When I go to design view and attempt to use the expression builder for Control Source, there is no total line to choose from. So, what do I put after =[My Subform].[Form]! ?

Thank you!

[June7]

This sounds like something I have never done on a form. What I am not clear about is how you get the Totals calculated. They are not showing in the subform? The subform is in Datasheet View?

Sometimes seeing is understanding. If you want to make project available I will look at. If you can't attach here then could upload to a fileshare site such as Box.net and post link to the file. Please Compact and Repair the project first. Zipping not required but good idea for large files.

[Niki]

If you look at the recipes form and flip to recipe 7 you will see I worked out how to show the totals of the columns, but I used invisible controls in the subform. I think there has to be an easier way, but that worked. However, now I'm having trouble making the cost and nutrient information keep up when I change the ingredient from the drop down window on the form. If you change the ingredient, the cost and nutrient info says the same! Thanks for any help you can give. Zipped file attached.

[June7]

Recipe Ingredients table should not have all the nutrition fields. This is repeating data from the Ingredients table. What is presently happening in the Recipe Ingredients subform is that selecting an ingredient in the combobox is just changing the ID value in the Recipe Ingredients table. What you need to do is make the RecordSource for the subform a join between Recipe Ingredients and Ingredients (join type - show all records from Recipe Ingredients and only those from Ingredients ...), bind the nutrition textboxes to the fields of Ingredients, make these textboxes Locked and no TabStop.

When I opened the Recipe Ingredients table a parameter prompt popped up. I had to remove from the table's Order By property: [Lookup_Ingredient].[Ingredient]

I could not figure out how you get the column totals in the subform. I have never done this before. Could you explain?

[Niki]

If you go to the recipes form in design view and scroll down on the subform you'll see a bunch of nonvisable boxes. They sum the calories, cost and nutrients. Then on the main form I told those boxes to

=[Recipes Subform].[Form]![Total Calories]

Etc.

I've decided to start from scratch though - I downloaded the recipe db from microsoft and tried to add to it to get what I needed, but I think it'd be easier if I started over so I knew where everything went.

Thanks for you help and I'm sure I'll post again in the future!

[June7]

I did see those textboxes. I even tested that idea by deleting them but the totals still show. That's why I was confused enough to ask how these totals are generated.

Starting from scratch is a bit extreme. The bones of this project are fairly good, just need some cleanup. Building project that is totally your own is a good learning exercise but you can also learn a lot by examining what has been done in this project. So before you totally discard, just examine all the objects and controls and note what works and what doesn't. Were the Recipe Ingredients table and Recipes form/subform in the template like that?

[Niki]

That's the big question - I can pull in the info the first time I select an item from the combo box, and even generate the totals, but when the info from the drop down box changes, it doesn't change everything else in the datasheet. That's where I'm stuck now.

[June7]

I offered you correction to that problem in the earlier post.

I will have to look at the project again tonight. I don't know how the values are brought into the Recipe Ingredients table the first time. My point is that this should not be happening anyway. This duplication of data should not take place. Defeats the concept of relational database.

And still not understanding how the column totals are generated.

[Niki]

Hold the phone-I think I got it! I'll have to mess with it some more, but don't worry about it tonight and I'll post future questions as needed.

Thanks!

[June7]

Glad your understanding has progressed. However, I am still confused by the column totals and intrigued enough to want to understand how this takes place.

[nicknameoscar]

Hi,

I have been following this thread and I am confused with the Total as well. It has been a day or so since I looked at the DB but, if I remember correctly, I couldn't even find the Total text box on any of the forms. I am thinking I missed something obvious but would like to understand what is going on if possible. Thanks!

[June7]

Eureka! Found it. Form in Datasheet view, click the Totals (Sigma) button.