Quantities in Products Table not updating properly.

[dragongunner0351]

I have posted this problem twice and unfortunately I still need assistance.

I have a Products table which holds quantity levels of parts. BeginningBalance and Available Units.

The Products table is linked to the Order Details Subform via OrderID -> Product ID (Relationship)

This is the code I am using that brings in the data from the Products table to the Order Detail Subform when a part is selected from the Product Combo Control on the Order Details Subform.

Private Sub ProductID_AfterUpdate()
Me![ListPrice] = Me![ProductID].Column(4)
Me![SalePrice] = Me![ProductID].Column(7)
Me![Available Units] = Me![ProductID].Column(14)

End Sub

I can get this to work beautifully if in our world someone only ordered a product once.

In the Order Details Subform (datasheet view) The Available Units data autofills and I have another control in the subform called NewBalance, the control source for NewBalance is =[Available Units]-[Qty Shipped], like I said works great but only once.

Once the order is placed I can go to my Products form and see the order and the adjustment to the Available Units. I have since been working on using a requery in the after update event but it is not working. I have also tried using requery in before Update, On load, On Activate, but to no avail. Am I on the right track with the requery or is there a problem with my structure?

Thanks in advance for any assistance.

 

[kjv1611]

This may sound silly, but have you tried this:
After one update is made on the form, in code, close the form, and reopen it?

Private Sub cmdCompleteOrder_Click()
  DoCmd.Close
  DoCmd.OpenForm "frmOrders"
End Sub

 

[dragongunner0351]

Thanks for the reply I give it a go.