Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check Value of Subform (97 SR2)

    I have a PO-type form with a subform containing the details. In the footer of the subform, I have a hidden text box with a total of the price ie =sum(qty*price). On the main form, I have a visible text box that references the hidden total box on the subform. SOP so far, right? The visible total on the main form updates and displays perfectly. The problem is that I have a function that checks the total value and if it is over $1000, it prompts for a spending request form number (necessary for any purchase over $1000). This works fine if I add a record, move off the "price" field of the subform back on it and back off again. The function is in the "LostFocus" property. I want it to trigger the check immediately after the most recent line item is entered so that it catches it as soon as the new record is added and the total initially reaches 1000. I think I see it check the "total" but it happens so fast that the status bar still says "Calculating" and it misses the change. Any Ideas here?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Check Value of Subform (97 SR2)

    Could you put your check in the subform's AfterInsert event?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Value of Subform (97 SR2)

    I tried it in the AfterInsert with the same results. The check is performed correctly, from what I can see, but the problem is that the check is made before the total is calculated. Would it be possible/wise to put in a 1 or 2 second delay between entering the record and performing the check in order to allow the calulated field to, um well, calculate?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Check Value of Subform (97 SR2)

    You could try using "DoEvents" in your code just prior to performing the check. YOu could also try "Me.Recalc". Although, since the problem may be that the subform hasn't yet completed its calculation, you might want to directly force that control on the subform to requery.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Value of Subform (97 SR2)

    Thanks for all your help, Mark. I tried DoEvents to no avail. I also forced the total box on the subform to requery, followed by the total box on the main form, then ran the check. Still no dice. I can see the focus go briefly to the total box to perform the check then continue with the code as if the check was false. It's just that the calculation isn't working fast enough (and it's not that complicated). The next record entered picks up on the check being true, but what happens if the user has only one entry? I would like for it to be instantanious (or at least seemingly)

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Value of Subform (97 SR2)

    Did it change anything if you do the check on the invisible textbox on the subform ?
    This should be faster as there is no need to reference to the main form.
    My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Francois

  7. #7
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Value of Subform (97 SR2)

    Nope, no change.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Check Value of Subform (97 SR2)

    At this point, I'm somewhat at a loss for anything that would either speed-up or wait for the calculations to be done. The only thing I can suggest is to ignore that textbox on the subform that hold the calcuation. IN your checking routine, open a recordset based on a totals query (or even use DSum) to get your information, using the same WHERE criteria as used for subform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Value of Subform (97 SR2)

    Ok, problem solved. I reread the posts and tried the recalc idea (once I figured out what it did). So I stuck "Me.Recalc" in the subform's AfterInsert right before the check and bingo...works like a charm. Thanks again for all your suggestions.

Posting Permissions

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