Calculated Field on subform

[nicole.skeeters]

Hi, I'm back!

I have searched through the forum trying to find the answer, but no one seems to be trying to get an answer from a calculated field like I am...On an inventory transactions list form, I want to do the "total cost" of the line item. In my text box control source, I have tried doing: =[Inventory List].[Quantity]*[Inventory Transactions].[Item Cost] only to receive a "#name?" on my form view. I also tried putting this expression in the query, thus making my control source: =[Inventory Transactions Extended]![Expr1] but with the same result. Is it not possible to pull from two different tables to make a calculated field? Or am I just doing this all wrong?

Thanks,
Nicole

[Bob Fitz]

Try:
=[Quantity]*[Item Cost]
as the Control Source

[nicole.skeeters]

Well, while it did remove the "#name?" error, it just left a null value...I'm thinking because the item cost is stored in another table, "Inventory List," and not in the "Inventory Transactions List" where I'm trying to get the total cost to show.

[TG_W]

Are you querying two different tables to produce the form? Without seeing the db structure, it sounds like you might want to add a field for the cost in the "Inventory Transactions List" so you can calculate. If you do not need to edit the data, you should be able to query the two tables together and get your cost.

[nicole.skeeters]

Hmm. I added the field item cost to "Inventory Transaction List" and updated the control source - again no "#name?" error but no calculation either. I would not need to edit the data, no. Perhaps a better way to go about doing this is to just add total cost to reports instead of trying to make it populate on the order entry form?

[TG_W]

Try checking this little deal I threw together. Probably does more than what you're looking for, but I can get the calculation you're looking for in VBA. I was just doing something similar.

[TG_W]

Sorry, in .mdb format. It has code performing the calculations, and there is a relationship set in the database between the two tables.

[nicole.skeeters]

Hey there, thank you for that! I'm getting an error: "Compile error: Method or data member not found." It is referencing the [Quantity] in the Private Sub. Any suggestions?

[June7]

I tested TG's database and it works without error for me.

However, why save the calculated extended price to table? This can be calculated whenever needed.

The value you might want to save to transactions is the item cost if this cost could be changed in the future and don't want existing records to reflect the new cost. The alternative is a new item record in inventory and a field for setting records as active/inactive and excluding inactive records from combobox list.

Options for retrieving the item cost from inventory list.

1. join inventory table to transaction table, join type "Show all records from Transactions ...", bind textbox to field from inventory table, set it as Locked Yes, TabStop No

2. include the price as another column in item combobox (which TG has done) and reference the column in a textbox

3. DLookup() in textbox expression