It's the wait that's killing me...

[tylersontag]

Alright, take a shot at this one.

I have a form; it opens up another form, and let’s calls it the Report form.

The report form has 3 embedded sub-forms. I'm trying to read a summation value in the footer section of each one of these subforms. The problem is that it takes about half a second to read the backend SQL database and compute this value.

This wait is killing me. These values are used in the closing events and it is possible for a very quick user to hit close before these values are computed, thus causing zero to be passed into the method referencing these fields.

Furthermore, I have a process that automates this process by opening the form then calling a handler to close the form. This ALWAYS results in the values not being computed properly prior to them being needed.

Now, sofar I’ve tried both a while loop and even a reference to kernel32 (Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)) to cause the btnExit_Click event to pause if these calculated values aren't filled out yet.

The problem with both is they take precedence over the computation I’m waiting on. I can SLEEP or loop for a solid ten seconds and still see a zero value in that field.

Is there any multithreaded approach I can take here?

 

[MarkK]

Forms are presentation layer elements; data consumers not data producers. I would not rely on a form to produce data for a mission-critical process. I think in your case I would calculate the required values independently of the subform. This will be much faster and then you can run that process independently of the form.

 

[tylersontag]

Yes, that is the way I would have done it if I would have writen it in the first place... sadly this is not the case.

I had considered the rewrite, but only as a last result. It would require 3 extra queries not to mention the multistep transformation of the results into its final value.

So still accepting any advice, if there's any to give

 

[MarkK]

Your form and subforms will load faster if you load the subforms manually after the main form opens. In design view delete the name of the subform from the SourceObject property of the subform control. Then in the Form_Open() event of the main form, explicitly load the subforms by setting the SourceObject properties of the subform controls...

Me.MySubformControl.SourceObject = "frmMySubform"

Another trick I've used is open the form hidden, set the TimerInterval in the Form_Open() event handler to a duration that allows the subforms to calculate their results, and then handle the Form_Timer() event and set Me.Visible = true. You can also set the MousePointer to Hourglass and/or display a progress bar so the user knows somethings happening.

private sub form_open(cancel as integer)
[COLOR="Green"]  'form was opened using 'DoCmd.OpenForm Name, , , , , acHidden'[/COLOR]
  me.timerinterval = 5000
end sub

private sub form_timer()
  me.timerinterval = 0
  me.visible = true
end sub

 

[tylersontag]

Well, i ended up biting the bullet and pulling the form code out and placing it in the backend VBA code.

But setting the RecordSource directly to the form was killing performance... 8 or 9 seconds load time on the page comming up...

So i experimented with setting the Form.Recordset directly, much better performance, down to under a seconds... heres a little taste of me setting the fields and subforms all in-line

    sSQL = "SELECT DISTINCT MoveSum.* FROM MoveSum where Variable1= " & Variable1 & " And Variable2= " & Variable2 & " ORDER BY MoveSum.RowNum;"
    Set Me.MoveSum.Form.Recordset = CurrentDb.OpenRecordset(sSQL, DB_OPEN_DYNASET)
 
    sSQL = "SELECT sum(delAmt) FROM TotSum where Variable1= " & Variable1& " And Variable2= " & Variable2
    totAdditive = CurrentDb.OpenRecordset(sSQL, DB_OPEN_DYNASET).Fields(0).Value