VBA - updatting main form + subform based on entry in 2nd subform

[alikona]

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:

=[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.

    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?

[mike60smart]

Hi

I like to see the actual Forms and table structure to try and help.

If you can upload a zipped copy with no Confidential data we can take a look for you.

[Gicu]

You do not want that (Filled) as a field in the table as it can easily become a problem if you sometimes need to delete or modify a shipment. Simply add it as a calculated field in the LineItems record source query and bind the textbox to that. As for to calculate it you can either use DSum or Dlookup in a totals query.

Cheers,