VBA loop

[Hek]

Hi all,

I'm having a bit of trouble with the VBA side of access.
I have a subform that i'm using VBA to perform a specific calculation (inventory), i can do that no problem but when i do this it's only being applied to one row in the subform when i want to to apply to all of the rows. So the idea is that when "Process Order" is clicked it will subtract the required quantity from the current inventory.

my code is:
[Kit Supporting Subform].Form![Current Inventory] = [Kit Supporting Subform].Form![Current Inventory] - [Kit Supporting Subform].Form![Quantity]

Any help would be greatly appreciated.

Regards,

Hek

Form for reference:

 

[June7]

Has anyone mentioned that you should not do this? Inventory on hand should be calculated from data when need to know, not saved into table. Review http://allenbrowne.com/AppInventory.html

You do not show any looping code. If you really must do this then open a RecordsetClone recordset of the subform dataset and loop through those records.

 

[Uncle Gizmo]

That code will only ever Sum the the values in the selected row in the sub-form. You are going to have to access the actual data in the table that the subform is based on. However, there are a few questions that need to be answered to work out if this will be possible.

Are the records in the subform linked to the main form by a child parent link in the Subform/Subreport Control that houses the subform? - [Kit Supporting Subform] ... I ask because I don't see a field which identifies the link, although it would be normal to hide this field, I thought if you're developing, it might be an idea to have it visible.

 

[Pat Hartman]

Put the calculation in the Form's RecordSource query and bind the calculated field to your form. The result will be not updateable but if you change either of two fields, then Access will automatically change the result immedately.

 

[Deleted member 147267]

Has anyone mentioned that you should not do this?

I'm with you on that.