Form-SubForm-Subform-Form-SubForm-Subf.....

[dynamictiger]

To say this issue is difficult to describe is an understatement. To explain how the form and subforms work is even more difficult, but I will try.

I have a form, which is resident to a master subform. The master subform contains 5 other subforms. I tired the traditional approach of querying the form with a search combo box and then requerying the source objects and this was too slow, even on my development machine which would have meant my users machines would have been so slow as to be ridiculous. So I opted for links methods instead.

This works by having two hidden text fields on the master subform. All the subforms on the master are linked to the unbound hidden text fields. This resulted in an increase in speed of between two and fifteen times in loading. So far so good.
I have been working on the code behind the forms and noticed that many of the subforms are reloading more than once in the search. That is that the sub sub forms may have some code in the current event to do something. This code runs several times depending on the subform. I think this is inefficient but do not see how to stop it. A typical block of code is shown below:

Private Sub Form_Current()
Debug.Print "Form_Current.Start"
    On Error Resume Next
    Dim lngPoolID As Long


    Call UpdateLink

    lngPoolID = Me.PoolID

    Me.txtHiglight.FormatConditions(0).Enabled = True

    Me.txtHiglight.FormatConditions(0).Modify acExpression, acEqual, "[PoolID]= " & lngPoolID

    Me.txtHiglight.FormatConditions(0).Enabled = False
   


Debug.Print "Form_Current.End"

End Sub

Some of these calls are six or seven times, although the code they run is not that much it is still annoying. I would like to control the number of times the code runs or move the code to a more appropriate place in the forms to prevent it running so many times.

So far I have tried setting module level global but this seems to reset between calls, so does not work. I was thinking about setting database level global but then I am concerned that if I set them incorrectly the whole application won’t work. So now I have decided the events are in the wrong place. Which event should I use to prevent this problem?

 

[pono1]

DT,

You're far too far into your project for me to pretend to completely understand this, but, still, a few questions that may or may not jog you left, right and onto a solution: Why is the code running more than once? Because you are requerying the datasource, either explicitly or implicitly, and thereby triggering the On Currrent Event over and over?

Your link to the two hidden textboxes: does this mean you are changing the recordsource property on the sub forms to query for records based on the contents of one or more of these hidden textboxes? If not, you may want to give it a try, see if it improves performance. It would look something like this.

Forms!FrmName.RecordSource = "SELECT TblMgt.MgtID, " & _
               "TblMgt.mStreet, TblMgt.mCity, " & _
               "TblMgt.mState, TblMgt.mPcode, " & _
               "TblMgt.mPhone1, TblMgt.mCell, " & _
              "WHERE TblMgt.MgtID=[forms]![FrmName2].[txtControlName]"

Or perhaps this is what you did earlier with the combo boxes...

I know you're not asking about alternatives but ultimately, if this is going to be a multi-user DB and you want the most efficient performance, a solution may be to use unbound forms, opening a connection to your remote DB at startup and bringing over data in small packages, a record or two at a time -- as needed -- to fill your forms. The catch: tons of coding.

Good luck.

Oh, one last question: I know Perth is in a remote location, but isn't Honolulu the most isolated capital in the world?

Regards,
Tim

 

[dynamictiger]

Apparently we are further away than Honolulu. If you lived here you'd believe it. WA=Western Australia=Wait Awhile.

I have a few unbound forms in the system. I guess if I was doing this again I would consider the unbound approach. A work around I have found to tons of coding is a combination approach of recordsets to load the data and then append queries to save the data - seems to work pretty well andis a little simpler than transactions which I haven't tried yet.

I will try delinking the child links and altering the SQL statement. Most of the forms build queries on the fly to satisfy their recordsource requirements so it is not a big deal to rebuild them. This is an approach I had not thought about.

The performance is not that bad, I think any machine over 600 mHz would be fine, and for any other industry this would be fine. But for my target market I expect several 400 mHz or even below, but you can't build for everyone.

I am actually thinking to save support calls of bundling the software with a RAM upgrade as the market I work in is not techno enabled.