Store calculated field in the table

[beti]

Please show me how to store/add calculate field in the table. Thank you very much.
I attached the database for review. In the database, I have 3 fields, QtyReceived, QtyRemain, QtyUsed.
The calculation is QtyRemain = QtyReceived - QtyUsed (where QtyUsed is in the subform and it will add total of QtyUsed in subform and put in the mainform)
As I did calculate in this database, the QtyRemain doesn't store in the QtyRemain in the tbl_Item.
Is there another way to calculate QtyRemain and store in QtyRemain field in the table.

 

[Minty]

Generally, it's not considered the best practice to store this type of data in this way, you should really use a transactions table : items in or out in one column and do a sum across the transactions. Review Allen Brownes resource here http://allenbrowne.com/AppInventory.html

For the better way of handling this.

 

[vba_php]

welcome to ur new thread beti! I remember ur last one. Minty's link is fine, but inventory control, which is the subject of Allen's page in his link, can be done more than one way. I cannot open ur db cuz i get that stupid "unrecognized database format" error. That's one thing I have never solved. what access version r u using? I would assume 2013 and 2016? cuz i think ur db has features in it that 2007 (my version) cannot read and understand. can you upload like a 2007 db or .mdb extension? i mite be able to read those types of files. but as far as storing calcs in a table, a lot of times this is done in a query object and then the query is used as the data source for the display item, like in ur case ur form. calc'd fields CAN be stored in tables of course, but developers generally don't do it I think, although personally i don't see anything wrong with it cuz it's not like they cause the same unforseen problems that lookup fields do. but i would also suggest making a change so u don't have to do that.

 

[ypma]

Beti, as stated it is not common practice to store calculated field in tables , however you have asked the question.

One way would be to set your control source to the field in the table and place your formula in the click event of the form field '
#Private Sub txtAmountRemaining_Click()
Me.txtAmountRemaining = [txtQuantityReceived] - [txtQuantityUsed]
End Sub#

Hope this is of use to you there may well be other ways to achieve your aim.

Regards Ypma

 

[beti]

welcome to ur new thread beti! I remember ur last one. Minty's link is fine, but inventory control, which is the subject of Allen's page in his link, can be done more than one way. I cannot open ur db cuz i get that stupid "unrecognized database format" error. That's one thing I have never solved. what access version r u using? I would assume 2013 and 2016? cuz i think ur db has features in it that 2007 (my version) cannot read and understand. can you upload like a 2007 db or .mdb extension? i mite be able to read those types of files. but as far as storing calcs in a table, a lot of times this is done in a query object and then the query is used as the data source for the display item, like in ur case ur form. calc'd fields CAN be stored in tables of course, but developers generally don't do it I think, although personally i don't see anything wrong with it cuz it's not like they cause the same unforseen problems that lookup fields do. but i would also suggest making a change so u don't have to do that.

Thank you for advising, I'm using 2013 and attached is the db with .mdb

 

[vba_php]

beti, I made the following changes:

> deleted the field "qtyRemain" from table "tbl_item", cuz it has to be calculated.
> added the following to the control source of "QtyRemain":

=forms!frm_Item!QtyReceived - DSum("QuantityUsed","tbl_Item_Checkout","[ItemID] = " & [Forms]![frm_Item]![ItemID])

> added the following to the control source of "QtyUsed":

=DSum("QuantityUsed","tbl_Item_Checkout","[ItemID] = " & [Forms]![frm_Item]![ItemID])

this is just one of many ways to do this sort of thing. take a look at the northwind sample DB from microsoft. personally I think their example sucks, but it's another way this can be done. but regardless, in terms of what you want, this will work fine.

by the way beti, I just bought a new windows 10 machine so I've now got Access 2016 for the time being. but as soon as that 365 trial ends I will be back to 2007. I had to hack my way into installing office 2007 on a machine that has no CD drive (my 2007 version is on cd discs) and a scenario where MS office 2007 support ended long ago, but I've got an external drive at my disposal, so problem solved there!

 

[beti]

beti, I made the following changes:

> deleted the field "qtyRemain" from table "tbl_item", cuz it has to be calculated.
> added the following to the control source of "QtyRemain":

=forms!frm_Item!QtyReceived - DSum("QuantityUsed","tbl_Item_Checkout","[ItemID] = " & [Forms]![frm_Item]![ItemID])

> added the following to the control source of "QtyUsed":

=DSum("QuantityUsed","tbl_Item_Checkout","[ItemID] = " & [Forms]![frm_Item]![ItemID])

this is just one of many ways to do this sort of thing. take a look at the northwind sample DB from microsoft. personally I think their example sucks, but it's another way this can be done. but regardless, in terms of what you want, this will work fine.

by the way beti, I just bought a new windows 10 machine so I've now got Access 2016 for the time being. but as soon as that 365 trial ends I will be back to 2007. I had to hack my way into installing office 2007 on a machine that has no CD drive (my 2007 version is on cd discs) and a scenario where MS office 2007 support ended long ago, but I've got an external drive at my disposal, so problem solved there!

vba_php,
Thank you very much for take a look and work on this db. I did download and test but somehow it doesn't work right. When I created new record, it doesn't work for the calculation. Also, if I make a change on existing record, it doesn't work right away unless I go to next record or close form then open form again and it works. Is there a way if we can do a calculation in vba?