Solved Sum of Textbox over Continuous Form (1 Viewer)

[widemonk]

Hi all. I would like to have a way to build recipes and see the nutritional content for both each ingredient and the overall recipe, live, as the numbers are being entered.

The small figures in pink are bound to the MasterFoodList table that has its values stored at the 'per 100g' rate and will be Visible = No.
The blue figures are unbound, but are a simple calculation of that 100g rate to show actual usage...eg =txtCals100 / 100 * txtGrams.

All works great so far. However now I want to show the totals for each value at the bottom.
(Lets say the whole thing has too many calories, I can see which ingredient is the biggest culprit so I know which one to either reduce or substitute as necessary).

The figure Im looking for in the first #Error is 947.13... a sum of all the Calorie figures in blue.

None of this is a subform, its a continuous form and the individual numbers in blue dont need saving (to reserve db space) because they're just a calculation All powered from a simple query that pulls a few tables together.

I look forward to your points of view.

 

[theDBguy]

Hi. Can you show us the Control Source for the Textboxes with the #Error?

 

[widemonk]

The control for the blue (unbound) text is txtCals
Control for the pink 100g rate bound to the food table is txt100Cal
The one called txtSize is under Grams.. i should probably change the source to 'Grams' so the control can be called txtGrams

 

[theDBguy]

The control for the blue (unbound) text is txtCals
Control for the pink 100g rate bound to the food table is txt100Cal
The one called txtSize is under Grams.. i should probably change the source to 'Grams' so the control can be called txtGrams

View attachment 90768

Hi. Thanks for the additional information. In the formula =Sum([txtCals]*[txtSize]), is txtCals the name of a field in your table? Actually, the same question applies to all the names with "txt" in front of them? I could be wrong, but I thought you could/should only Sum fields in your Record Source.

 

[widemonk]

Hi and thanks for looking.
All the txt prefixes are for Text Box controls (eg txtThis, lblThat, cmdTheOther etc - my own personal 'rule' so can see at a glance and never confuse a control from a source.)

That appears to be the case, but this table doesnt have those actual numbers stored, in fact doing so would be a detriment.
(Lets say I change the shop I use and the cals changes, I need all my recipes to be auto-updated simply with the new 100g rate).

So if it cant be done on the form, can it be done on the query for the form to just pull the like a Record Source?
Im guessing something like this, Im sure I can research the proper layout/syntax and go for some trial & error.

 

[theDBguy]

Hi and thanks for looking.
All the txt prefixes are for Text Box controls (eg txtThis, lblThat, cmdTheOther etc - my own personal 'rule' so can see at a glance and never confuse a control from a source.)

That appears to be the case, but this table doesnt have those actual numbers stored, in fact doing so would be a detriment.
(Lets say I change the shop I use and the cals changes, I need all my recipes to be auto-updated simply with the new 100g rate).

So if it cant be done on the form, can it be done on the query for the form to just pull the like a Record Source?
Im guessing something like this, Im sure I can research the proper layout/syntax and go for some trial & error.

View attachment 90769

Hi. My understand, and again I could be wrong on this, is that the Sum() function is used to add up the records in your data source. So, yes, if you can move your calculated data into the query, I expect you will be able to use the Sum() function on your Form to add them up.

 

[arnelgp]

you use Sum() on the Fieldname (the Control Source) of the textbox and not the textbox itself:

=Sum([the controlsource of txt100cals textbox] / 100 * [the controlsource of txtSize textbox])

 

[widemonk]

Hi and thanks again.
Have tried both, both still return the #Error

move your calculated data into the query, I expect you will be able to use the Sum() function on your Form to add them up

The Expr in the Query is called 'QryCalc'. This is referred to on the form in the yellow box.
If I remove the Sum part of that and just say =[QryCalc] then I dont get an error, but it returned the value of the first record only, not a sum of them all.

=Sum([the controlsource of txt100cals textbox] / 100 * [the controlsource of txtSize textbox])

This is represented in the green box. It refers directly to the Record Source name (shown in pink)


I really really need a workaround for being able sum all on a form

 

[theDBguy]

I really really need a workaround for being able sum all on a form

Are you able to share a sample copy of your db with test data?

 

[widemonk]

OK I've stripped it down to a bare bones with minimal info.
Its the footer of frmRecipeBuilder.
Total calories should be 859cal
(100g @ 142cal/100g + 133g @ 261cal/100g + 200g @ 185cal/100g = 859cal)

The key is that this figure in the footer should instantly change if the 100g/133g/200g values are edited, or as new ingredients are added.

 

[arnelgp]

check and test.

 

[widemonk]

Fantastic, thank you arnelgp.
Now I just need to fiddle with them to make the recordset updateable when the number of grams is changed, or a different ingredient is selected.

 

[arnelgp]

you can make it updateable by using DSum() and not Total Query.

 

[widemonk]

Sorry, I have to admit Im struggling with that. I'm not sure which query to put it in, and it appears to need extra syntax; both an expression and a domain (or from what I understand, the source query).

Because I dont know where to put the DSum, at the same time I dont know which source query to use and my trial & error returns something about an aggregate function.

Either Im putting it in totally the wrong place, or its the wrong syntax

Expr1: DSum([calories]/100*[grams],[AnotherQueryName])

 

[widemonk]

Hi - I just want to update and close the thread.


Workaround was like this...

RecipeList revised to include fields for actual values for each nutrient for each ingredient - not ideal because its storing data that is just a simple calculation of data found in other tables.

The form includes bound (but locked/not directly editable) textboxes to the actual values stored, and hidden textboxes to the 100g rates. A simple VBA calculation on the AfterUpdate event of the Grams field to fill in the actual values. Then for the total at the bottom, used a DSum of those stored actual values in the Textbox Control Source.

A couple of other tweaks like moving to a subform and an occasional .requery command, its now well on its way.

Thank you all very much for your input. Although it wasnt a direct solution, there were plenty of tips to help me research and find a successful workaround.