Sub Form Formula

  • Thread starter Thread starter Lou
  • Start date Start date
L

Lou

Good Day;
This is a followup to a previously asked question.

I have a combo box in a sub for that identifies a [product]
on my subform which feeds of the product table. Then I
have a [cost] combo box that brings in the cost from the
same product table, then I have a [quantity] cell that I
enter the quantity in. I then created a [Extended Cost]
cell with the control source being [Quantity]*[Cost], my
problem is that the [cost]is a combo box and it is
multiplying by the cost record number instead, how do I
change it to go of the [cost] amount and number the [cost]
record number? Any help would be great.

Thank you.
Lou
 
Lou,

Not sure why you'd use a combo box for the price field. Do you have multiple
prices for the same listed product/ ingredient? If not, then there's no need
for the combo box for the cost of the product. Either use the DMax (if you
keep track of price history) or DLookUp function to pull the price, or add
the price to the rowsource of your Product combo box and use its afterupdate
event and column property to insert the price into a text control and set
the control source of your Extended Cost to
[Quantity]*[NameOfTextboxHavingCost].

Does that answer your question?
Jeff

ps. you really should have stayed in the original thread. Wayne has posted a
follow-up question.
 
Jeff thank you,

I have changed my box to an unbound field with the
following control source formula,

=DLookUp("[Cost]","Ingredients","[IngredientID]=
[IngredientID]").

It brings back a 2 for each row in the subform cost field.
The cell properties are set for currency in the tables and
forms. Any ideas.

Thank you.
Lou

Also, unfortunatelly I did not see waynes response when I
opened this new one up or yes I would have continued this
thread as I thought that one went into thread limbo.
-----Original Message-----
Lou,

Not sure why you'd use a combo box for the price field. Do you have multiple
prices for the same listed product/ ingredient? If not, then there's no need
for the combo box for the cost of the product. Either use the DMax (if you
keep track of price history) or DLookUp function to pull the price, or add
the price to the rowsource of your Product combo box and use its afterupdate
event and column property to insert the price into a text control and set
the control source of your Extended Cost to
[Quantity]*[NameOfTextboxHavingCost].

Does that answer your question?
Jeff

ps. you really should have stayed in the original thread. Wayne has posted a
follow-up question.

Good Day;
This is a followup to a previously asked question.

I have a combo box in a sub for that identifies a [product]
on my subform which feeds of the product table. Then I
have a [cost] combo box that brings in the cost from the
same product table, then I have a [quantity] cell that I
enter the quantity in. I then created a [Extended Cost]
cell with the control source being [Quantity]*[Cost], my
problem is that the [cost]is a combo box and it is
multiplying by the cost record number instead, how do I
change it to go of the [cost] amount and number the [cost]
record number? Any help would be great.

Thank you.
Lou
Click to expand...


.
Click to expand...
 
Hi Lou,

Yes, I actually have a better idea. I wasn't paying attention to the fact
you're doing this in a subform - either continuous or datasheet.

For the record source of your subform, use a query based on your
RecipeIngredients table. Drag all of your fields into the grid. Then add
your Ingredients table to the query, and join the two tables on the
IngredientID. Next you'd drag your [Cost] field into the grid. And then in
the next empty field in the grid, type Ext_Cost:[Cost]*[Quantity]. Now on
your form, delete the DLookUp from the control source of [Cost], and instead
click the down-arrow and select your cost field. Do the same for your
extended cost control source. You might also want to either lock the Cost
and Ext_Cost controls on the form, or at least set their tab stop properties
to "No" so you don't inadvertently overtype them. No harm on Ext_Cost since
it's just a calculated value, but because [Cost] is a bound control,
overtyping will change the value in the underlying table.

Hope that provides you with a more workable solution.
Jeff
 
The value of the combobox is its bound column. To use the "human data"
column, specify the column in the equation.

=[Quantity] * [Cost].[Column](1)

The column number is zero based, so the first column is 0, the second is 1,
etc.
 
ok, thank you guys for the help, I have accomplished
getting the extended cost correct. Now I am tring to sum
the [ExtCost] field in the regular form portion not in the
subform, ideas on formula, I have tried

=sum([Recipes Ingredients subform].[ExtCost])
and
=sum([Recipes Ingredients subform]![ExtCost])

with no success.

Thank you.
Lou

-----Original Message-----
The value of the combobox is its bound column. To use the "human data"
column, specify the column in the equation.

=[Quantity] * [Cost].[Column](1)

The column number is zero based, so the first column is 0, the second is 1,
etc.

--
Wayne Morgan
MS Access MVP


Good Day;
This is a followup to a previously asked question.

I have a combo box in a sub for that identifies a [product]
on my subform which feeds of the product table. Then I
have a [cost] combo box that brings in the cost from the
same product table, then I have a [quantity] cell that I
enter the quantity in. I then created a [Extended Cost]
cell with the control source being [Quantity]*[Cost], my
problem is that the [cost]is a combo box and it is
multiplying by the cost record number instead, how do I
change it to go of the [cost] amount and number the [cost]
record number? Any help would be great.

Thank you.
Lou
Click to expand...


.
Click to expand...
 
The problem with doing this is that ExtCost isn't a field. It is calculated
one record at a time, so there isn't something for the Sum to go back and
get. This would be easy in a report using the Running Sum option of the
textbox, but that isn't available on a form.

While we made the calculation work in the subform one record at a time using
the Column property of the combobox, we now need to know what table and
field that value comes from so that we can write an equation that will give
you your sum. Also, the calculation won't work for record changes that
haven't been saved to the table yet. So, if you just changes Quantity, until
that record is saved the new total won't show.

--
Wayne Morgan
MS Access MVP


Lou said:
ok, thank you guys for the help, I have accomplished
getting the extended cost correct. Now I am tring to sum
the [ExtCost] field in the regular form portion not in the
subform, ideas on formula, I have tried

=sum([Recipes Ingredients subform].[ExtCost])
and
=sum([Recipes Ingredients subform]![ExtCost])

with no success.

Thank you.
Lou

-----Original Message-----
The value of the combobox is its bound column. To use the "human data"
column, specify the column in the equation.

=[Quantity] * [Cost].[Column](1)

The column number is zero based, so the first column is 0, the second is 1,
etc.

--
Wayne Morgan
MS Access MVP


Good Day;
This is a followup to a previously asked question.

I have a combo box in a sub for that identifies a [product]
on my subform which feeds of the product table. Then I
have a [cost] combo box that brings in the cost from the
same product table, then I have a [quantity] cell that I
enter the quantity in. I then created a [Extended Cost]
cell with the control source being [Quantity]*[Cost], my
problem is that the [cost]is a combo box and it is
multiplying by the cost record number instead, how do I
change it to go of the [cost] amount and number the [cost]
record number? Any help would be great.

Thank you.
Lou
Click to expand...


.
Click to expand...
Click to expand...
 
There is a way around the limitation mentioned in my previous post. You can
make the "field" you are wanting to sum into a field by doing the
calculation in the query feeding the form. This then provides a "field" in
the form's recordset that can be summed.
 
Wayne and Jeff thank you, I incorporated the information
into the subform query and it is working just fine, now I
am trying to get the percentage to work right, heh, fun
fun fun..

Thank you again you guys have been very helpful.
Lou
 
Back
Top