The lack of ElseIf would not be your problem. Doing the update in one or the other IF blocks meant that the update only occurs for the IF that is True. Moving the update should be the fix as Gicu noted. This is why proper indentation of code is extremely important. You probably would have spotted your error if it looked more like first example below. Also, as Welshgasman indicated, stepping through your code is TroubleShooting 101. Surely that would have shown you where the problem was if indentation did not reveal it.
Code:
With rst
.Edit
If Me.LineStatus.Value = "Received" Then
!Inventory_Qty = !Inventory_Qty + Nz(Me.InOutQty, 0)
End If
If Me.LineStatus.Value = "Returned" Then
!Inventory_Qty = !Inventory_Qty - Nz(Me.InOutQty, 0)
End If
.Update
End With
Code:
If Me.LineStatus.Value = "Returned" Then
!Inventory_Qty = !Inventory_Qty - Nz(Me.InOutQty, 0)
.Update << will only update if LineStatus is "Returned"
End If
Still not working anyway? Well, what does that mean, error message, no result, wrong result, that part of the code is bypassed?
This could be another approach but I don't expect it would perform any differently if your current code does not perform as expected. Mostly, it could be used to demonstrate a way to deal with "Confirmed":
Code:
Private Sub LineStatus_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
On Error GoTo errHandler
If Not Me.LineStatus = "Confirmed" Then
Set db = CurrentDb
Set rst = db.OpenRecordset("Select Inventory_Qty from Inventory where ItemCode =" & Me.ItemCode)
With rst
.Edit
If Me.LineStatus = "Received" Then !Inventory_Qty = !Inventory_Qty + Nz(Me.InOutQty, 0)
If Me.LineStatus = "Returned" Then !Inventory_Qty = !Inventory_Qty - Nz(Me.InOutQty, 0)
.Update
End With
End If
exitHere:
Set db = Nothing
Set rs = Nothing
Exit Sub
errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub
That presumes LineStatus can only be 1 of 3 possibilities and nothing is to be done if the update is "Confirmed"