Firstly, a bit about my setup.
I have a primitive database of three tables:
A. Table that lists various locations: [location_code], [location_name]
B. Table that describes items: [item_code], [item_name], [item_price]
and finally a table that ties the roo^H^H^H whole database together
C. Table says which items are stored at each locations and in what quantities: [location_code], [item_code], [item_quantity]. This table can have multiple entries with the same [location_code], since different items can be stored at the same location. Basically, this is a schoolbook implementation of one-to-many relationship from locations to items, with a extra attribute (quantity) attached to each location-item link.
Now, all I need to do is to create a form that would list all items stored at certain location. This is a basic use-case for "subform" feature in Access. The main form simply navigates through different locations, while the subform lists the items.
I use
SELECT C.location_code, B.item_name, B.item_code, B.item_price, C.item_quantity
FROM B INNER JOIN C ON B.item_code=C.item_code
as a record source for the subform. The main form is linked to subform by [location_code]. The subform is set up with list layout (not table layout), i.e. each database field is given its own control element.
So far everything works perfectly. I select the location in the main form, and the subform gives me a list of editable [item_name]-[item_price]-[item_quantity] triplets for the current location.
As the next step, I want to add a calculated control to each of the above triplets. I want to know the total price for each kind of item (i.e. simply the [item_price] * [item_quantity] in each line). Easy, I add a calculated control to the "repeatable" area of the subform and specify
= [item_price] * [item_quantity]
as the formula for the value. Again, everything works perfectly. Now instead of the above triplets, my list has four entries per line: [item_name]-[item_price]-[item_quantity]-[total_item_price]. And, what is important, when I edit [item_price] or [item_quantity], the [total_item_price] gets automatically reevaluated once I finish editing.
And as the final step, I want to add another calculated control that would display the total price of all items stored at the current location (let's call it "grand total"). A also place that control into the subform (into the "footnote" area this time) and specify the following formula as its value
= SUM([item_price] * [item_quantity])
Now, this works. The total value is calculated correctly each time I change the location in the main form. Also, when I change [item_price] for any item at the current location, the grand total is also updated automatically. However, when I change [item_quantity], the grand total does not update (???). Hitting F9 will make it update. Changing the current location back and forth will update it. But, unfortunately, a mere change to [item_quantity] does not trigger an automatic update.
Does anyone know, what could be wrong here? Why do changes in [item_price] trigger the automatic update of the grand total, while changes in [item_quantity] do not? Intuitively, these fields seem to have the same status (except that the former comes from table B, while the latter comes from table C). Why is the behavior different? How do I make it update automatically?
