Subtotal in Form died with Combo box

[OnTheJobLearning]

I have a calculated subtotal in a subform that is displayed in the main form.
Worked well. Until, I created a combo box to select the product and set up
the Unit Price field to auto fill from the product choice. The line total
calculation in the subform after the unit price works yet. I noticed,
however, that my calculated subtotal in the main form stopped working.

Is this because the unit price involved in the calculation of the line total
is tied to the product choice in the combo box?

thanks!

 

[Ken Snell \(MVP\)]

Assuming that you're using the Sum function to calculate the subtotal, you
must use only field names in the Sum expression -- you cannot use the value
from a control.

Post the expression that you're using to calculate the subtotal.

 

[OnTheJobLearning]

The Subtotal field in the main form is:
=[Approvals Subform]![Total Approved]

The Total Approved field in the subform is:
=Sum([Quantity]*[Unit Cost])

The Unit Cost field is: (Based off Wafer TypeA combo box choice)
=[Wafer TypeA].Column(1)

 

[Ken Snell \(MVP\)]

You no longer have a Unit Cost field in the form if you have set the Control
Source of a Unit Cost control to this expression:
=[Wafer TypeA].Column(1)

So this expression is not valid any more because Sum cannot see a control
named Unit Cost, it can only see a field named Unit Cost:
=Sum([Quantity]*[Unit Cost])

--

Ken Snell
<MS ACCESS MVP>

The Subtotal field in the main form is:
=[Approvals Subform]![Total Approved]

The Total Approved field in the subform is:
=Sum([Quantity]*[Unit Cost])

The Unit Cost field is: (Based off Wafer TypeA combo box choice)
=[Wafer TypeA].Column(1)

Assuming that you're using the Sum function to calculate the subtotal,
you
must use only field names in the Sum expression -- you cannot use the
value
from a control.

Post the expression that you're using to calculate the subtotal.

 

[OnTheJobLearning]

So, I use the expression for the control name instead of Unit Cost?

Sorry... I've reread this over and over...

You no longer have a Unit Cost field in the form if you have set the Control
Source of a Unit Cost control to this expression:
=[Wafer TypeA].Column(1)

So this expression is not valid any more because Sum cannot see a control
named Unit Cost, it can only see a field named Unit Cost:
=Sum([Quantity]*[Unit Cost])

--

Ken Snell
<MS ACCESS MVP>

The Subtotal field in the main form is:
=[Approvals Subform]![Total Approved]

The Total Approved field in the subform is:
=Sum([Quantity]*[Unit Cost])

The Unit Cost field is: (Based off Wafer TypeA combo box choice)
=[Wafer TypeA].Column(1)

Assuming that you're using the Sum function to calculate the subtotal,
you
must use only field names in the Sum expression -- you cannot use the
value
from a control.

Post the expression that you're using to calculate the subtotal.
--

Ken Snell
<MS ACCESS MVP>



message I have a calculated subtotal in a subform that is displayed in the main
form.
Worked well. Until, I created a combo box to select the product and
set
up
the Unit Price field to auto fill from the product choice. The line
total
calculation in the subform after the unit price works yet. I noticed,
however, that my calculated subtotal in the main form stopped working.

Is this because the unit price involved in the calculation of the line
total
is tied to the product choice in the combo box?

thanks!

 

[Ken Snell \(MVP\)]

No, Sum does not "see" a control on a form or report. You must create an
expression inside the Sum function that includes references ONLY fields that
are in the RecordSource of the form or report, along with any valid
operator(s).

Therefore, if you're getting the value of the UnitCost from a column in the
combo box, and there is no UnitCost field in the form's or report's
RecordSource query, you will be unable to do a Sum calculation in the
subform. I suggest that you will need to add the UnitCost field to the
RecordSource query if you want to use it in a Sum function.

--

Ken Snell
<MS ACCESS MVP>

So, I use the expression for the control name instead of Unit Cost?

Sorry... I've reread this over and over...

You no longer have a Unit Cost field in the form if you have set the
Control
Source of a Unit Cost control to this expression:
=[Wafer TypeA].Column(1)

So this expression is not valid any more because Sum cannot see a control
named Unit Cost, it can only see a field named Unit Cost:
=Sum([Quantity]*[Unit Cost])

--

Ken Snell
<MS ACCESS MVP>

The Subtotal field in the main form is:
=[Approvals Subform]![Total Approved]

The Total Approved field in the subform is:
=Sum([Quantity]*[Unit Cost])

The Unit Cost field is: (Based off Wafer TypeA combo box choice)
=[Wafer TypeA].Column(1)





:

Assuming that you're using the Sum function to calculate the subtotal,
you
must use only field names in the Sum expression -- you cannot use the
value
from a control.

Post the expression that you're using to calculate the subtotal.
--

Ken Snell
<MS ACCESS MVP>



in
message I have a calculated subtotal in a subform that is displayed in the
main
form.
Worked well. Until, I created a combo box to select the product and
set
up
the Unit Price field to auto fill from the product choice. The line
total
calculation in the subform after the unit price works yet. I
noticed,
however, that my calculated subtotal in the main form stopped
working.

Is this because the unit price involved in the calculation of the
line
total
is tied to the product choice in the combo box?

thanks!