Results 1 to 3 of 3
  1. #1
    alikona is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019

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

    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:

    • 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?

  2. #2
    Join Date
    May 2018
    Living in Scotland UK

    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.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Kelowna, BC, Canada
    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.

    Vlad Cucinschi
    MS Access Developer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-05-2020, 05:50 AM
  2. Replies: 39
    Last Post: 07-28-2018, 12:27 PM
  3. Replies: 2
    Last Post: 12-09-2015, 04:50 PM
  4. Replies: 8
    Last Post: 05-30-2013, 05:06 PM
  5. Replies: 6
    Last Post: 08-24-2012, 12:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums