The only way I know how to have a text box perform a calculation is to put this in the "Control Source".

If you want this value to be stored in a table somewhere, "Control Source" is where I normally select that location.

How can you perform both: a calculation that is saved to a table field?

Thanks.

Hi,
as a general rule we do NOT want to store calculated values in table fields. They can be calculated at runtime on forms/reports or in queries.
HTH
Good luck

Good point. Thank you.

So for my calucation, a text box in the Main form.

=Sum(Forms![Request Order Subform]!TotalLineCost)

...as the control source.

This is giving me an #ERROR.

Three things
1: I assume that your calculation textbox is placed on a main form and the values you want summed are on a subform on that form. Create a textbox txtSum on the form footer on the subform with the value =Sum([TotalLineCost]).

2: Set the control source of your textbox on the main form to: =[Request order subform].[txtSum]. You can make the form footer of the subform invisible, once you know things work.

3: Remember that this value depends on the recordset of the subform. I.e. if a user filters it, the value changes, and it should, as freakazeud points out, NOT be stored, but used purely for information purposes.

Hth

Jock

Thank you. I feel like I am on the right path, but something is wrong.

How do I make my footer visible. I can only see it in design view.

Also, the =[Request order subform].[txtSum]. on the main form comes back with "#Name"

I'm a clutz! it's supposed to be: =[Request order subform]![txtSum] (an exclamtion mark in stead of a decimal point)

I is important that [Request order subform] is the name of the subform object on your main form, and not the name of the subform in the forms collection. Often these two are given the same name for claritys sake, but check it out just in case.

Even if you can't see the footer (I suspect you are working in datasheet view) the value still computes.

Hth

Jock

The have the same name.

Also, I tried
=[Request order subform]![SumTotalLineCost]
and it didnt work. I saw somewhere the format below, but that didnt work in this case either.
=[Request Order Subform].[Form]![SumTotalLineCost].

Thank you,

Clueless.

Here are my current details:

"TotalLineCost" (IN SUBFORM, as is "UnitPrice", and "UnitsSold"
=[UnitPrice]*[UnitsSold]

"SumTotalLineCost" (IN FOOTER OF SUBFORM)
=Sum([TotalLineCost])

"TotalCost" (ON MAIN FORM)
=[Request Order Subform]!SumTotalLineCost

Thank you.

Try changing your subform to a continuous form, which will let you see the "SumTotalLineCost" control at runtime. Check if this computes correctly.

Jock

The "total line cost" computes correctly for each item, but the "SUmTotalLineCost" gives me an "#Error".

Ah! It's probably because you are trying to use the Sum function on an unbound control. For the Sum function to work, I believe that you will have to sum a table/view field name, and not the name of a control.

Do the calculation of the TotalLineCost in the query/view that is the recordset of the form (call it TotalLineCost). Then place this value in a BOUND textbox on your subform.

In the form footer your Sum funtion should be performed on a field-name of the datasource rather than the control name.
The formula is till the same: =Sum([TotalLineCost]). But now it refers to a field rather than a control.

Now you should be flying.

Hth

Jock

I dont completely understand the following part of your directions:

"Do the calculation of the TotalLineCost in the query/view that is the recordset of the form (call it TotalLineCost). Then place this value in a BOUND textbox on your subform."

I attached my database if it will aid you at all in your troubleshooting.

My continued thanks.

It works now (notice that I put some test data in the ItemsSold field of the Inventory Transactions).

I have chosen to create a query for the forms datasource (qInventoryTranscations) where I link the product table to the Inventory transactions through a product ID, so that the price per unit is comming directly from the product table (that seemed to be the price that your "absolutely-no-no" Dlookup unit price control was fetching ;-)). You will see how the calculation of the TotalLineCost is done in the query, rather than on the form.

Btw, what's the unitprice field doing in the InventoryTransactions table?

Hth


Jock



Edited by: prebez on Thu Mar 8 4:10:40 EST 2007.

Thats great. Thank you. I repeat: Thank you.
I had tried following your suggestion on my own, but in the qInventoryTransactions I had TotalLineCost: [UnitPrice]*[UnitsSold], not knowing to type products.unitprice.

Why was that UnitPrice dookup a "absolute no-no"?

I have no idea why unitprice field was in the Inventory Transaction table.
I want it coming from the unitprice field in "Products" table.

On my version UnitPrice used Dlookup to get it from the products table, on yours its just using the field in the Inventory transaction table (Which I think shouldnt be there).

How can I make UnitPrice come from the products table w/o using dlookup, when the subform is based on Inventory Transactions?

Edited by: birdikid3 on Thu Mar 8 9:43:05 EST 2007.

You are welcome.

1: lookups in form is a no-no because it slows them down, and because it makes the code less transparant.

2: In my version it IS the UnitPrice from the products table that's being used. The two tables are joined in the query, that's why the unitprice is prefixed with the table name products.

"How can I make UnitPrice come from the products table w/o using dlookup, when the subform is based on Inventory Transactions?"

You can't. Which is why I redefined the record source by making a query that combines the two tables.

Hth

Jock

Makes sense. So I believe I can just delete the "UnitPrice" field in the "Inventory Transactions" table.

I was confused because "UnitPrice" showed up on the Control Source drop down on the subform properties, and I thought that because UnitPrice was for some reason on the Inventory Transaction table, and that THAT was what was being used.

But I had forgotten that you changed the control source from "InventoryTransactions" to "qInventoryTransactions". Hense the "UnitPrice" from "Products" is actually used.

Yea!! Im understanding. Thanks again.

"Makes sense. So I believe I can just delete the "UnitPrice" field in the "Inventory Transactions" table."

I would say that you should delete it. If it turns out that other forms use thid field, change their record source to the new query.


You are most welcome Birdikid3. It's great that you feel you also learned something.

Jock