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.

[June7]

Calculated values are not automatically saved to table because those controls are not bound. The Actual value will not be altered by the calculation alone. Code is required to save calculated value.

Is Used a bound control?

[bakkouz]

No, They are bound.
As I said, everything works fine, but only for the last item selected in the subform.
it does not update all items. that's the problem.

[June7]

Don't know what you mean by 'update'. Make the calculation control visible while you are testing. What is the formula? Are the calculations occurring? Can you provide project for analysis?

Try this. Name the controls different from the fields they are bound to, like tbxUsed. Then refer to the control names in calculations.

[bakkouz]

Yes, I'll try and post it. just let me clean it up a bit

[bakkouz]

June,
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

[June7]

This project opens to a form with choices Enter Items and View Items. I selected Enter Items. When I select Department (the only one offered is Administration) I get error message "The expression After Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control." I am not familiar with this error. Before I can select Section an input prompt pops up. I OK through it and try the Section combo, there are no choices. I tried to identify the control with the calculation but didn't see any controls with a calculation. Sorry, can't debug your project.