Sum field in query won't show on form

[Guest]

I must use two queries on a form because having just one query won't allow me
to be able to calculate the Total for a field and also allow me to change a
combo box.

So I have one query on my form that allows me to change the ingredients
being used in another table. I have another query that calculates the cost
for all of the ingredients used and totals their amounts. The query works
fine in totaling the amounts for each menu item, but in the Detail section on
my form, I put in an unbound text box and set the control source equal to the
expression in the second query.

In the query I set the criteria as the combobox used in the detail section
of my form. In the textbox on my form, however, it says Name? instead of the
total for that ingredient item.

I have read many posts for this error, but I do not know how to get around
calculating this text box in the manner that I am doing.

Any help would be great. Thank you for your time.

 

[Penguin]

First, a form can have only one record source (Table, Query or SQL
statement) If you have a form with a subform then each has its own
record source. Now if you are trying to calculate say the number of
records on the subform. Then you textbox would look like
=Sum([Products]). This value would not be stored in the table. Its
merely a calculation. If you are trying to do something else, try to
explain.

 

[Guest]

Thanks for your response. I'll try to explain a bit better.

My program allows you to enter a bunch of ingredients on one form with their
case costs. On the next form you build add a menu item on the form and then
on the subform you use a combo box to select ingredients for the menu item.
Next to the combo box you enter the quantity and then next to that it does a
calculation to figure the cost of that particular ingredient in the item. In
the footer of the this subform it sums the individual costs of each of the
ingredients to give a total food cost for that menu item.

Now the next form allows you to create combo meals from the menu items that
were created on the previous form. You begin by creating a new combo meal
and then on the subform you select a menu item from a combo box and enter a
quantity. I am not sure how to go about calculating the cost for each
individual menu on this subform. I have tried several ways.

I began by recalculating the menu item from the base level by multiplying
the ingredient cost by quantity in a menu item by quantity in a combo meal.
When doing this, my subform shows a detail for every ingredient item in the
entire menu item. This locks me out where I can't add another menu item.

Another way I tried is by linking the combo meal table to the query I used
on the first form to calculate the menu item costs and then sum that field.
That locked me out and would not allow me to make changes either.

I am now wondering if I should make the cost of the menu items an entered
amount or a stored number instead of calculating it in the detail section.
After I get it to show up in the detail section for each menu item, I will be
able to sum them in the footer fine.

Thank you for your help. I hope this made things a bit clearer.

First, a form can have only one record source (Table, Query or SQL
statement) If you have a form with a subform then each has its own
record source. Now if you are trying to calculate say the number of
records on the subform. Then you textbox would look like
=Sum([Products]). This value would not be stored in the table. Its
merely a calculation. If you are trying to do something else, try to
explain.

I must use two queries on a form because having just one query won't allow me
to be able to calculate the Total for a field and also allow me to change a
combo box.

So I have one query on my form that allows me to change the ingredients
being used in another table. I have another query that calculates the cost
for all of the ingredients used and totals their amounts. The query works
fine in totaling the amounts for each menu item, but in the Detail section on
my form, I put in an unbound text box and set the control source equal to the
expression in the second query.

In the query I set the criteria as the combobox used in the detail section
of my form. In the textbox on my form, however, it says Name? instead of the
total for that ingredient item.

I have read many posts for this error, but I do not know how to get around
calculating this text box in the manner that I am doing.

Any help would be great. Thank you for your time.

 

[Penguin]

I'm not sure, but if you are trying to calculate values based on
previous selected items you will need to do this in a query and then
sum the values in another query which the third form will gets its
data.

Thanks for your response. I'll try to explain a bit better.

My program allows you to enter a bunch of ingredients on one form with their
case costs. On the next form you build add a menu item on the form and then
on the subform you use a combo box to select ingredients for the menu item.
Next to the combo box you enter the quantity and then next to that it does a
calculation to figure the cost of that particular ingredient in the item. In
the footer of the this subform it sums the individual costs of each of the
ingredients to give a total food cost for that menu item.

Now the next form allows you to create combo meals from the menu items that
were created on the previous form. You begin by creating a new combo meal
and then on the subform you select a menu item from a combo box and enter a
quantity. I am not sure how to go about calculating the cost for each
individual menu on this subform. I have tried several ways.

I began by recalculating the menu item from the base level by multiplying
the ingredient cost by quantity in a menu item by quantity in a combo meal.
When doing this, my subform shows a detail for every ingredient item in the
entire menu item. This locks me out where I can't add another menu item.

Another way I tried is by linking the combo meal table to the query I used
on the first form to calculate the menu item costs and then sum that field.
That locked me out and would not allow me to make changes either.

I am now wondering if I should make the cost of the menu items an entered
amount or a stored number instead of calculating it in the detail section.
After I get it to show up in the detail section for each menu item, I will be
able to sum them in the footer fine.

Thank you for your help. I hope this made things a bit clearer.

First, a form can have only one record source (Table, Query or SQL
statement) If you have a form with a subform then each has its own
record source. Now if you are trying to calculate say the number of
records on the subform. Then you textbox would look like
=Sum([Products]). This value would not be stored in the table. Its
merely a calculation. If you are trying to do something else, try to
explain.

I must use two queries on a form because having just one query won't allow me
to be able to calculate the Total for a field and also allow me to change a
combo box.

So I have one query on my form that allows me to change the ingredients
being used in another table. I have another query that calculates the cost
for all of the ingredients used and totals their amounts. The query works
fine in totaling the amounts for each menu item, but in the Detail section on
my form, I put in an unbound text box and set the control source equal to the
expression in the second query.

In the query I set the criteria as the combobox used in the detail section
of my form. In the textbox on my form, however, it says Name? instead of the
total for that ingredient item.

I have read many posts for this error, but I do not know how to get around
calculating this text box in the manner that I am doing.

Any help would be great. Thank you for your time.