Update not going to 0.00 (1 Viewer)

[rainbows]

Below is a form that changes the stock qty and allocation and stock balance
in the yellow feild which is called update i put the qty i am taking out of the stores in this case 10 so when i have pressed the button " update allocation " this second form below shows that this has happened . this is correct but there was 10 in allocation
the 3rd form shows this time i am going to remove 20 from stock but nothing was allocated this time then in the 4th form you will see the yellow update did not go back to zero and stayed at 20 i have attached the code and have no idea why it will not changed to zero

thanks for your help steve

Private Sub cmdUpdate_Click()
10        On Error GoTo cmdUpdate_Click_Error


30    Me.StockQTY = Me.StockQTY - Me.Update
35   If Me.Allocation < Me.Update Then
      Me.Allocation = 0
36 Else
 
40    Me.Allocation = Me.Allocation - Me.Update
50    Me.Update = 0
60      Refresh
End If



 MsgBox "The selected item in Stocklist has been Updated", vbInformation
          
100       On Error GoTo 0
110       Exit Sub

cmdUpdate_Click_Error:
          
        
      
160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."


End Sub

 

[June7]

Review http://allenbrowne.com/AppInventory.html

Did you step debug? Does the line get executed?

 

[Gasman]

You only set update to zero on the else path???

 

[The_Doc_Man]

The question has to be how these quantities get loaded. Which ones are bound to the underlaying data, which ones are entered from form input, and which ones are computed. Somewhere in there, you are not properly re-initializing something. Also, the order in which you do that math in your routine seems wrong. You should do nothing to stock qty until AFTER you have verified that you CAN do something to it. I wonder if there can be leftovers on the form. Every field on that form should either be bound or should be reset to zero after the update occurs.

One last comment, and it is truly minor - but if you check the link below, you find that the word UPDATE is a reserved word for Access. Oh, it might work OK - but it is ALWAYS a bad idea to use a reserved word as a variable name.

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

EDIT: Looks like Gasman also noted that your order of operations seems questionable.

 

[jdraw]

Might be more efficient to post a copy of the database so readers can see/exercise your code.
Looks like you have 4 decimal places on quantities???
Also, you might want to watch the Northwind2.0 videos that deal with inventory.
Northwind2.0 Starter
Northwind2.0 Developer

 

[Mike Krailo]

I would put some validation code in the forms before_update event to make sure that the amount entered into your [Update] field is not greater than the amount currently in stock.

Alternatively, you can adjust your current code to this instead.

Private Sub cmdUpdate_Click()
   On Error GoTo cmdUpdate_Click_Error
  
   If Me.Update > Me.StockQTY Then
      MsgBox "There are only " & Me.StockQTY & " left in stock!"
      Exit Sub
   Else
      Me.StockQTY = Me.StockQTY - Me.Update
      If Me.Allocation < Me.Update Then
         Me.Allocation = 0
         Me.Update = 0
      Else
         Me.Allocation = Me.Allocation - Me.Update
         Me.Update = 0
         Refresh
      End If
   End If

   MsgBox "The selected item in Stocklist has been Updated", vbInformation
    
   On Error GoTo 0
   Exit Sub

cmdUpdate_Click_Error:
   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."
End Sub

I'm not sure about the Allocation part since I do not know exactly what that is for.

 

[rainbows]

thank you this has got it working ok now

the stock qty gets change when the purchase order gets booked in this is done by a append query
but to book out the parts individually to the shop floor i use that form to select the part which automactically loads the stock quantity and allocation to that form above which i posted yesterday from the table stocklist . then in the yellow update box i enter the amount that i am taking out of stock and the it updates the stock qty, and allocation
if i want to pick all the parts and send them to the shop floor for a certain project i use this form which updates all the parts for that job the 2 columns on the right tell me what the allocation and stockqty will be after i press the "update allocation " button. then cancells that project number so i cannot see this information again for that project so i cannot do it twice

Private Sub cmdUpdate_Click()
10        On Error GoTo cmdUpdate_Click_Error


30    Me.StockQTY = Me.StockQTY - Me.update
35   If Me.Allocation < Me.update Then
      Me.Allocation = 0
36 Me.update = 0
37 Refresh
      
      
39 Else
 
40    Me.Allocation = Me.Allocation - Me.update
50    Me.update = 0
60      Refresh
End If



 MsgBox "The selected item in Stocklist has been Updated", vbInformation
          
100       On Error GoTo 0
110       Exit Sub

cmdUpdate_Click_Error:
          
        
      
160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."


End Sub