Update Available Inventory Field

[Lnwolf3232]

I am writing code in Access that will allow me to update the Available Inventory for a product. This is all based on a table called tblOrders.

I have Initial Inventory, Used, Reorder, Received, and Available.

In the command button I have written the following code:
Private Sub cmdCalculate_Click()
Dim TempInitial As Double
Dim TempReorder As Double
Dim TempUsed As Double
Dim TempReceived As Double
Dim TempCurrent As Double
Dim TempAvailable As Double
Dim TempDiscontinued As Boolean

TempInitial = Initial_Inventory
TempReorder = ReOrder_Level
TempUsed = Used
TempReceived = Received
TempDiscontinued = Discontinued


If TempDiscontinued = False Then
TempCurrent = TempInitial - TempUsed
TempAvailable = TempCurrent + TempReceived
Available_Inventory = TempAvailable
Else
MsgBox ("Discontinued Cartridge Checked")
Available_Inventory = TempCurrent
End If

End Sub

When I initiate a quick watch all fields update appropriately, except not updating the Available_Inventory field on the form or in the table. Can someone please tell me what I am doing wrong..... It has been about 20 years since I have done Access, so I am very rusty.

 

[Micron]

First, please use code tags and good indentation for more than a few lines of code. Second, you should think about whether or not you even should do this. It's generally accepted that storing calculations is not good except for certain cases. This doesn't seem to be one of them.

As to your issue, do you have Option Explicit at the top of every module? If not, it should be there by default (it is an option setting in vb editor -"Require Variable Declaration). I ask this because while there's more that one possibility for your issue, your use of the variables and the assignments to them - TempCurrent = TempInitial - TempUsed provides no clue as to where the values are coming from. If the values come from form controls, the syntax is incorrect (should be Me.TempUsed if that's the form control name).

Your issue could also be caused by
- the form or even just the control you're trying to update not being bound to anything
- not updating the record by requerying the control or form record
- the value is updating, but you're going back to the table view and failing to refresh the table view
- and maybe others that don't come to mind at the moment
More importantly is the issue as to whether or not you should be storing the calculation. It typically should be more of a matter of sum of all ins minus sum of all outs and be displayed only in a query or form control.

 

[Lnwolf3232]

thanks. the me.Temp worked!!!