Access 2007 - Subform only calculates last field.

[bakkouz]

Hi,
I have a main form and a subform.
The main form is based on an "items" table, and the subform is a continuous form based on a "subitems" table, they are lined via "item_id".


in the subform I have the following controls: Item, Used, Available.


The user chooses the "item" from a combo box, it automatically shows him the quantity of the item available in the "Available" field, then he enters the amount used in the "Used" field. I have set up an Invisible text box which stores the actual available amount with a formula to subtract the Used from the Actual, to prevent the Actual amount from being changed until the user presses the "Save" Button on the main form.


This all works fine and well if the subform contains only one item. but if the user selects more than one item in the continuous subform, Access only calculates the last item entered. and doesn't apply the formula to all items in the subform.


How do I solve this?
Thanks.

 

[vbaInet]

In what section did you place this invisible textbox?

What do you mean by "if the user SELECTS more than one item"?

 

[bakkouz]

ok, the subform is a data entry continuous form. you select an item, enered the data, the select another item and enter the data and so on. you know.

and the invisible textbox is placed in the subform.

 

[vbaInet]

Is the textbox placed in the Detail section of the subform?

Is it bound to a field?

 

[bakkouz]

Yes, it is placed in the details section and is bound to a field.
I've attached a copy of the accdb file.
you must first select Dept, then SEction in the main form,
then in the subform you must select: printer make then printer model then ink model> then enter a value in "Used"
after update of "used" updates the value of the items in the inventory but only temporarily.
the actual value of the items on the list should only be updated after clicking save on the main form.
then in the subform try using the HP Color LaserJet 1600 as an example.
hope it makes sense .

 

[vbaInet]

Ok bakkouz, I'm working blindly here. You didn't mention what the name of the form is? The name of the textbox you're dealing with?