Add delivered quantities and assign to an order (inventory issue)

[mseo]

hi,
I have a Form for add delivery Receipts and assign each product quantity for specific OrderdetailID
:
1- Form Add receipt: when I select the customer from Customer_ID combobox, I get Form1 opened with no orders
2- when I select product in the subform, Form1's Listbox will be filtered by unlocked/unfilled orders for that product
I have two problems with this Form:
1- if I select a product and entered the quantity without orderdetailID, then select orderdetailID, this problem causes the quantitydelivered field to be updated by minus value
assume I entered quantity=100 and there is no orderdetailID, then I selected an orderdetailID with quantityrequired= 1000, the result is: Quantity Delivered=0 (not updated) and if I removed or adjusted the quantity the Quantity Delivered= -100

2- if I select a product, select the orderdettailID and entered the quantity successively, if I change the product : the updated record of the order is the wrong record ( because the product changed from the combobox)(should undo the quantity delivered because the product changed)
I don't need to force the user to select the orderdetailID first before inserting the quantity because may be there are some receipts are entered without any orders just as retail
may be the problem with the code and I really don't know how to handle it

Public hold_qty As Long
Private Sub Quantity_Enter()
hold_qty = Me.Quantity
End Sub

Private Sub Quantity_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
If Me.OrderDetail_ID > 0 Then
On Error GoTo edit_error
Set rst = db.OpenRecordset("select QityDelivered from tbl_orderdetail where orderdetailID=" & Me.OrderDetail_ID)
With rst
    .Edit
    !QityDelivered = !QityDelivered - nnz(hold_qty)
    !QityDelivered = !QityDelivered + nnz(Me.Quantity)
    .Update
End With
Me.QD.Requery
Exit Sub
edit_error:
If Err.Number = 3188 Then
    MsgBox "This record is locked by another user.  Please try this edit again later."
    Me.Quantity = hold_qty
Else
    Me.Quantity = hold_qty
 
Resume exit_out
exit_out:
End If
End If
End Sub
 

I need to handle two issues
2.Handling Edits
If the user changed either the quantity or the product, you have work to do. In the form's BeforeUpdate event, examine the OldValue of these controls. If either changed
3.Handling Deletes
In the continuous subform, multiple records can be selected for deletion. The Delete event fires for each record, so if the deleted record had affect on the quantity delivered, how can I undo this affect?
thank you very much

I really appreciate your help

[mseo]

hi,

I need help with this, please