Results 1 to 5 of 5
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Wait for subform to fill (Access 2003 (2000 format

    I have a situation where a customer is entitled to a discount if the total of their purchases exceeds a threshold value.

    I want to use the Oncurrent event of a customer form, with an item sales subform, to calculate whether they qualify for the discount.

    But when I put : currTotalSales = Nz(Me!frmVendorItemssoldsub!txtTotalSales, 0) in the OnCurrent event, currTotalSales returns 0,
    because the subform is still filling with data, and calculating its total.

    So to slow it down, I put this loop in

    <pre> currTotalSales = 0
    Do While (currTotalSales = 0)
    DoEvents
    currTotalSales = Nz(Me!frmVendorItemssoldsub!txtTotalSales, 0)
    Loop
    </pre>


    Generally this works, but I am danger of infinite loops, because occasionally TotalSales are 0.

    I can fix this by putting in an arbitrary exit point;

    <pre> currTotalSales = 0
    intCount = 1
    Do While (currTotalSales = 0) And (intCount < 5000)
    ' to avoid infinite loops
    DoEvents
    currTotalSales = Nz(Me!frmVendorItemssoldsub!txtTotalSales, 0)
    intCount = intCount + 1
    Loop
    </pre>


    There must be a better way to do this. The choice of 5000 is pretty arbitrary. Initially I tried 50, and that seemed to work, then it didn't, so I don't know whether 5000 will remain sufficient.

    I am pu zzled by all this, as I don't remember having to wait for a subform to fill as being a problem in the past.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wait for subform to fill (Access 2003 (2000 format

    Have you tried putting a text box on the main form with control source

    =Nz([frmVendorItemssoldsub]![txtTotalSales],0)

    and referring to that to decide if the customer is entitled to a discount? You wouldn't need code.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wait for subform to fill (Access 2003 (2000 fo

    Thanks Hans.

    Yes I have tried putting a text box on the Main form as you suggest, but didn't solve my problem.

    I still need code in the OnCurrent event to compare this textbox with the threshold, and calculate the amount of the discount and put it into discounts given control. Unless I put in the waiting loops, this always returns a zero discount.

    Discounts can also be given for other reasons, so I can't just set the control source of the discount control to a calculation.
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wait for subform to fill (Access 2003 (2000 fo

    That does the trick.

    Thanks Again.
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wait for subform to fill (Access 2003 (2000 fo

    Try this then:

    Private Sub Form_Current()
    ' Force subform to recalculate NOW
    Me!frmVendorItemssoldsub.Form.Recalc
    currTotalSales = Nz(Me!frmVendorItemssoldsub!txtTotalSales, 0)
    ' Other code
    ...
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •