MDM,
Erk! Sorry, too much rush... I meant
=Sum([ProductID].Column(2)*[UnitsSold])
Not sure that this will work - never tried it!
Ok, at the moment, the Record Source of the subform is the Inventory
Transactions table, right?
Well, make a query, including the Inventory Transactions table and the
Products table, joined on the ProductID field from both. Add all fields
from the Inventory Transactions table, and also the UnitPrice field from
the Products table, in the query. And then make this query the Record
Source of the subform instead.
But now here's another thing I've noticed... now you mentioned that "the
UnitPrice is being stored in the Inventory Transactions file correctly".
Sorry, I didn't pick this up before. Do you have a UnitPrice field in
the Inventory Transaction table? So by what mechanism is the data being
entered in there? Your UnitPrice control on the subform is the one with
the [ProductID].Column(2) expression in it, right? So, you have a
control on the form named UnitPrice, and a field in the form's Record
Source, also named UnitPrice, but the UnitPrice control is not bound to
the UnitPrice field, right? Well, this won't work, and is probably
contributing to the problem. But I can't see how any data at all is
going into the UnitPrice field.
Under normal circumstances, it would not be valid to have a UnitPrice
field in the Inventory Transactions table anyway. However, it's not as
simple as that (in PICK or in Access!). If the UnitPrice in the
Products table for a given ProductID may change over time, and if the
historical records in the Inventory Transactions table need to show a
UnitPrice as at the time of the transaction, regardless of subsequent
price fluctuations, then some people would put a UnitPrice field in the
Inventory Transactions table to store this value. If so, you need to
change the design of the form, as all you want is the current Product
UnitPrice for the product being offered as the default value for the
Inventory Transaction UnitPrice for the product. In any case, I would
probably not do it like that. Instead, probably a more "correct"
approach would be to have a ProductPrices table, that records the price
changes for each product, with date of price update, and then use this
to determine the UnitPrice for any given product on any given
transaction date. Sorry, Mike, this has nothing to do with Access, it
has to do with the real-world data relationships that exist in trading
scenarios. So, hopefully you will come back and say that the Products'
UnitPrice are fixed, and we don't have to worry about price changes. If
that's the case, remove the UnitPrice field from the Inventory
Transactiosn table, and many of your worries will evaporate.
--
Steve Schapel, Microsoft Access MVP
Steve
You wrote:
"I would include the Products table directly in the query that the form is
based on,"
I tried the other route and get #Error. How do I include the Products table
directly in the query?
Boy, I may have to go back tot he PICK RDBMS! MDM