Hi all,
So I have a database for order management, this form in particular has several subforms and I'm having some issues getting them to talk to each other the way I would like them to.
General info about my form:
- Main form = frm_OrderDetails
- Sub forms = LineItems (subf_LineItems), Shipments (subf_Shipments)
- Each Order can have multiple Line Items and each Line Item can have multiple Shipments.
- On my Shipments subform, I have an unbound textbox [ShipTotal] that sums the [QtyShipped] field.
- On my main form, I have an unbound text box [RemainShip] that takes [ShipTotal] from the Shipments subform and subtracts that from [QtyOrdered] from the LineItems subform. Originally I had the following as the control source but I've removed that to try and have this calculated in VBA after [QtyShipped] has been updated:
Code:
=[LineItems].[Form]![QtyOrdered]-Nz([subf_Shipments].[Form]![ShipTotal],0)
- On my LineItems subform, I have a bound yes/no field called [Filled]. I would like this field to be somewhat of a visual indicator that a line item has been completed without having to click on each line item to see the shipment history.
What I would like it to do:
- When a user inputs data into [QtyShipped], the [RemainShip] field will be updated to show the remaining shipments needed to fill this line item. If [RemainShip] = 0 or alternatively if [ShipTotal] = [QtyOrdered] the [Filled] value will be changed to True (Yes).
What I've tried so far:
I've tried placing the following code in the AfterUpdate and OnChange event for [QtyShipped] field. The issue I've ran in with this one is that the [RemainShip] field does not seem to be updating in time, which does not trigger the [Filled] box to get updated. I've also tried placing the code in the OnCurrent event for the Shipments subform and that does seem to work how I want it to but it causes the subform to freak out (flickering like its constantly looping through the code - which I guess is what its supposed to do here, not ideal) and forcing the cursor to the first field on the subform.
Code:
Forms![frm_OrderDetails]![RemainShip] = Forms![frm_OrderDetails]![LineItems].Form![QtyOrdered] - Nz([ShipTotal], 0)
If Forms![frm_OrderDetails]![LineItems].Form![QtyOrdered] > 0 And Forms![frm_OrderDetails]![LineItems].Form![QtyOrdered] = [ShipTotal] Then
Forms![frm_OrderDetails]![LineItems].Form![Filled] = True
End If
Forms![frm_OrderDetails]![Line Items].Form.Dirty = False
Does anyone have any suggestions on how I can get this to work how I want it to?