Field Calculations

[MikeKohler]

I have a question about calculations, In DAO can you take a field subtract a different field to it and store the results in a third field. The 2 fields are quantity and on hand, I want to calculate the new on hand value by subtracting the quantity use. Hope this makes sense.
Then using on change event, I want to do this using vba code.
Thanks,
Michael Kohler
mkohler@telusplanet.net

 

[jgoodman00]

dim db as dao.database, rst as dao.recordset
set db = currentdb
set rst = db.openrecordset("SQL String Here&quot
rst.edit
rst!Onhand = (rst!Onhand - rst!QuantityUsed)
rst.update
rst.close


That code should carry out the calculation. However, you need to locate the correct record (I have always done this by passing in the Unique ID for the recordset). You then need to update the data displayed in your form.

However, I would have thought it easier just to carry out the calculation directly within the forms class module, & not used DAO at all (assuming you are not manipulating multiple records, at which point DAO/ADO becomes much more efficient...

HTH
James Goodman

 

[MikeKohler]

Thanks for the help, this worked great, Is there a different way of doing it in VBA with class module, is this a better way?
Michael Kohler