Where did I go wrong HELP

[Dadeo]

The below code I thought should work but doesn't, can someone tell me if there is a problem with it? My Inventory is not updating when I add the quantity and listed it as Received.



Thank you all in Advance

The Inventory_Qty and ItemCode is in the Inventory table. InOutQty and LineStatus is in the PurchaseDetails table. Both tables are joined by ItemCode which is in both tabels. Inventory table has it list as an Autonumber and the PurchaseDetails table listed as a Number. Both are listed as Primary Keys. Also I am not sure if the 'On Error GoTo InOutQty_error is correct

Private Sub LineStatus_AfterUpdate()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb


'On Error GoTo InOutQty_error


Set rst = db.OpenRecordset("Select Inventory_Qty from Inventory where ItemCode =" & Me.ItemCode)


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)
    .Update
    End If
End With
End Sub

[Welshgasman]

Well the on error is definitely not correct, as there other mention of it in the sub? However it is commented out?
Have you walked through the code with F8 to see what the code is actually doing?

[Gicu]

You are updating only the returns, please try this (note the added indentation that should make it easier to follow the code):

Private Sub LineStatus_AfterUpdate()


Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb




'On Error GoTo InOutQty_error




Set rst = db.OpenRecordset("Select Inventory_Qty from Inventory where ItemCode =" & Me.ItemCode)




With rst
    .Edit
    	If Me.LineStatus.Value = "Received" Then    
    		!Inventory_Qty = !Inventory_Qty + Nz(Me.InOutQty, 0)
    	ElseIF Me.LineStatus.Value = "Returned" Then    
    		!Inventory_Qty = !Inventory_Qty - Nz(Me.InOutQty, 0
	End IF
    .Update    
End With


End Sub

Cheers,

[Micron]

Can LineStatus be neither Received or Returned when that control is updated?

[Dadeo]

Returned
Received
Confirmed

[Dadeo]

The ElseIF fixed it Thank you very much. One more questions how do I get the On Error working my I was told to add the label to the following "On Error GoTo edit_error

On the form itself I have the following code.

General Declarations

Option Compare Database
Option Explicit

and under Form BeforeInsert I have

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Handler:


Me.OrderNumber = Format(Now(), "yyyymmddhhnnss")
Exit_Here:
 Exit Sub


Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
        Resume Exit_Here
End Sub

[Micron]

Not what I was thinking (null) but still relevant. In that case FWIW,
- if Confirmed means do nothing, I wouldn't create objects (rs - which neither yours or the suggested code reclaims that memory space) unless it passed a test for not being "Confirmed"
- I would always use an error handler when dealing when creating recordset objects.

how do I get the On Error working

Don't use colon in the directive, only on the line you want to go to if there is an error
On Error GoTo Err_Handler, not On Error GoTo Err_Handler:

and

Err_Hanlder:
code here

The colon makes the line a line label.

[Dadeo]

This is the new code the revieced works but the returns don't

Private Sub LineStatus_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb


'On Error GoTo InOutQty_error


Set rst = db.OpenRecordset("Select Inventory_Qty from Inventory where ItemCode =" & Me.ItemCode)


With rst
    .Edit
        If Me.LineStatus.Value = "Received" Then
            !Inventory_Qty = !Inventory_Qty + Nz(Me.InOutQty, 0)
        ElseIf Me.LineStatus.Value = "Returned" Then
            !Inventory_Qty = !Inventory_Qty - Nz(Me.InOutQty, 0)
        End If
    .Update
End With


End Sub

[Micron]

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.

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

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":

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"

[Dadeo]

Sorry there Gicu, the returned was labeled return and that is why it didn't work. Thank you for you help you guys are great.

[Dadeo]

This works also just had to change the Returned to Return. I thought being I had the error handling on top it would work on the bottom. You count that towards a NEWBI Error. Thanks so much for you help. I wish all forums were as good as the group here.

[Micron]

Thanks so much for you help. I wish all forums were as good as the group here.

Well that's a whole lot nicer than some of the thanks we get.

If by

I thought being I had the error handling on top it would work on the bottom.

you are referring to Returned vs Return, I think it's safe to say that code does not raise errors that arise from logic errors - only incorrect syntax and other invalid statements.