Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    Unanswered: Delay/re-trigger calculations on a subform

    Hi,

    I have a form in Access 2010 that consists of two subforms. The first subform is a continuous form based on a query, with some calculated Sum fields at the bottom for several of the columns. The second subform has a calculated field (calculated onLoad for that subform) that relies on the value of one of the Sum fields on the first subform.

    The problem is that in the short moment it takes for the Sum field to calculate when the form is opened, the field on the second subform that relies on it has already calculated, and so it's incorrect. I've tried some code I found online to introduce a time delay before the onLoad event for the second subform runs, but this only works some of the time (and it seems to delay both subforms from loading anyway, not just the second one).

    Is there a way to delay only the second subform's onLoad event, or maybe trigger a requery of the second subform after the Sum fields on the first one have finished calculating?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't use a time delay
    place code behinds events to do your calculation
    ... use the on current event to trigger a recalcualtion when the current row changes (probably in your master form
    ...use after update in a control to fire the recalculation when a (relevant) value changes

    timers are all well and good, but you cannot relay on the inbuilt timer events in Access, they are not guaranteed to fire att eh precise time / time interval you specify, the tmer events are lower prority than other events and may get swamped out by other more important events
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2004
    Posts
    75
    Thanks for the info about the timer...that explains why it only works some of the time

    The timer was a last resort though. I tried doing it using the textbox events first, but it seems that the after Update event of a textbox doesn't fire when the form it is on first loads. To clarify, when the form loads, the Sum field is calculated, but it takes a second or so. In that second, the other field that relies on it has already calculated itself, and it is assuming that the value of the Sum field is 0 (or Null perhaps). So it's the wrong value

    Is there an event that fires when the Sum field has finished calculating? That's the event I need to use to refresh the other textbox, but that event doesn't seem to exist. Failing that, I need to delay the processing of the second subform's onLoad to give the Sum field enough time to calculate

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there are hoards of events.
    each has a specific role.

    what you need to do is push the actual calculation into a sub routine (as id expect it to set the value as opposed to a function which would return a value. then call that function from whatever events you need. one of those events will be the after update event of what ever controls that comprise the values in the sum.
    another will (probably) be on current. if you need to it may also be called in the on load event. you may need it elsewhere as well.
    you may need to use a dsum in the function, or if your VBA skills are up to it use a recordset.

    VBA is an event driven development environment NOT a time driven environment. the event hooks are available to developers to do the sort f things you want to do.... so use 'em.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you dynamically load the subforms into the main form? That way you can control the order and only load the dependent subform when the other one is ready.
    Have a nice day!

  6. #6
    Join Date
    Oct 2004
    Posts
    75
    Thanks for the help guys. Sinndho, I didn't see your reply till I'd already fixed it and come back here to say so, but that sounds like what I was originally trying to do. How would you do that exactly? Something like using VBA in the main form's onLoad to initiate the subforms and then the first one should finish before the second one starts?

    Well, what I ended up doing was using a recordset in the onLoad of the second subform that used the same query that the first subform is based on to calculate the required sum and store it in a variable. Then I used the variable to set the dependant field's value instead of referring to the Sum field on the first subform

    Thanks again!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Bane View Post
    How would you do that exactly? Something like using VBA in the main form's onLoad to initiate the subforms and then the first one should finish before the second one starts?
    Something like that.

    First you have to know that when a form has subforms, these subforms are initialized before the parent (main) form, i.e. the Form_Open, Form_Load and Form_Current events of each subform occurs before the Form_Open, Form_Load and Form_Current events of the parent form. However, you cannot accurately control in which order each subform will be loaded and initialized.

    If you left the SourceObject of the SubForm/SubReport control of the "dependent" subform empty, it will not be loaded automatically when the parent form is open. You can then use the Form_Open event handler of the parent form to manually load the dependent subform as, at that time, you are sure that every other subform is already loaded and ready:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        ' ctlChildDedendentForm is the name of the SubForm/SubReport control of the "dependent" subform.
        '
        Me.ctlChildDedendentForm.SourceObject = "NameOfTheDependentSubForm"
    
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Oct 2004
    Posts
    75
    Ah I see...I didn't realise that the subforms are initialised before the parent form. That's a handy tip to know, thanks mate

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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