Lazy Loading

[EternalMyrtle]

Hello,

All of my main forms contain multiple tabs and some of them take a long time to load. Would someone please explain the concept of lazy loading? Do you think it is a better way? Does it have its pitfalls?

Some practical guidelines about how it is executed would also be much appreciated.

Thank you!

 

[shadow9449]

Assuming I understand what you mean about "lazy loading", the idea is for data that's not necessary until the user clicks one of the tabs is not loaded until the tab is actually clicked. This reduces the amount of data required to access the record and can improve form performance.

I usually use this technique when the data on the tab is pulled from a related table. Example: if a client's demographic profile (pulling from the client table) is on one tab, and the sales related to this client are on another tab (pulling from the sales header table and the sales line detail table, and possibly the payment table), then I don't want the application loading those three tables every time I look up a client's phone number.

The way I do this is by putting the sales on a subform. When the user clicks the tab, the SourceObject for the subform gets changed to the subform that has the data.

Some pseudocode that would represent this:

Select Case Me.MyTabControl.Value
  Case 1
     Me.SalesSubform.SourceObject = "Sales"
  End select

That should give you the idea.

SHADOW

 

[EternalMyrtle]

I want my tabs to stay as they are (so, keep each subform in its tab) but maybe not load any of the tabs except the main one unless the user clicks on it. So, rather than loading them all, load them one at a time.

Is this possible? How do I use code to load the tabs?

 

[EternalMyrtle]

or maybe there is a better way?

 

[EternalMyrtle]

I think I will try your solution but keep my subforms in their current tabs and just use the tab click event to set the recordsource for the subforms.

Thanks for your idea.

 

[Simon_MT]

This is one approach:

You have to Manage your Tab Data

I have:
AC Artist Consignments
OH Originals History
GH Print History

So I do this:

    With CodeContextObject
        .[Page No] = .[TabCtl0]

        If .[ACFlag] <> True And .[Page No] = 3 Then
            .[ACFlag] = True
            .[Artists Consignments].SourceObject = "Artists Consignments"
        End If
        If .[OHFlag] <> True And .[Page No] = 4 Then
            .[OHFlag] = True
            .[Artists Originals History].SourceObject = "Artists Entry Originals History"
        End If
        If .[GHFlag] <> True And .[Page No] = 5 Then
            .[GHFlag] = True
            .[Artists Prints History].SourceObject = "Artists Entry Prints History"
        End If
     End With
End Function

Once the the Source Object has been set I don't set it again.

Simon

 

[EternalMyrtle]

This isn't going to work for me because some of the controls on my main form depend upon the subform controls to get data. I need to keep it this way for reasons I will not go into.

Is there some other way to improve the speed at which forms load?

Thanks...

 

[EternalMyrtle]

SimonMT: In my previous post I was responding to the other respondent. I had not yet seen your solution. I will look it over. Thanks!

 

[shadow9449]

This isn't going to work for me because some of the controls on my main form depend upon the subform controls to get data. I need to keep it this way for reasons I will not go into.

Is there some other way to improve the speed at which forms load?

Thanks...

Can you load the results of those controls using dlookup or opening the recordsource? I actually do the same in my example that I gave before. On the client's front screen it shows if the client has an outstanding balance, which is derived from the sales and payment tables. I have a query calculate if there's a balance and show it on the front screen. This is still faster than loading the entire table (I think)

SHADOW

 

[EternalMyrtle]

SimonMT: I am not understanding your code. Perhaps this is too complicated for me.

If I cannot figure out a way to only partially load a form, is there some way of letting users know that a form is loading? For some reason, the hourglass does not always show up during a form load (especially when I am loading from a link in another form). I tried using code to explicitly tell Access to turn on the hourglass but it didn't work

 

[Simon_MT]

I see what you mean but performance. My form loads the file almost instantaneously. Why does it take so long - how many records are involved.

Simon

 

[EternalMyrtle]

It is most likely not the number of records (I am not dealing with huge datasets) but the number of tabs with subforms. Some of the read-only subforms have union queries as the recordsource. I am wondering if that is part of the problem.

I will also try Shadow's solution of using lookups for the controls that are set from my subform.

I am going to have to test it out some more and see where I can improve performance since it doesn't sound like there is a simple magic-bullet solution.

 

[Simon_MT]

Are you using:

SELECT ...
UNION ALL SELECT

Simon

 

[MarkK]

Subforms, when they are present at design time, load BEFORE the main form, and they load ALL their records. Then, when the main form opens, all the subforms requery to synchronize with whatever record the parent loads. This is very sluggish, and you can get a very good performance improvement if you load the subforms programmatically AFTER the main form opens.

Do this by removing the subforms in design view, so delete the name of the form from the Source Object property of the subform control. Then, during Form_Open() or Form_Load(), set the value of the SourceObject property to the name of the form you want loaded.

Another trick to get the main form to paint immediately, is set a timer in Form_Open(), and don't load your subforms until the timer goes off, so you can wait 100 milliseconds, the main form opens and paints on screen, THEN load your subforms.

Private Sub Form_Open(Cancel As Integer)
   Me.TimerInterval = 101   [COLOR="Green"] 'start timer during main form open[/COLOR]
End Sub

Private Sub Form_Timer()
[COLOR="Green"]   'main form is now visible[/COLOR]
   Me.TimerInterval = 0      [COLOR="Green"]'stop timer[/COLOR]
   Me.MySubform.SourceObject = "MySubform"   [COLOR="Green"]'load subform[/COLOR]

 

[EternalMyrtle]

Thank you, MarkK! Will try this tomorrow.

 

[EternalMyrtle]

MarkK: This is great. Exactly what i was looking for. Thank you!

 

[The_Doc_Man]

Here's a thought, perhaps expressed differently than has been expressed by others.

Rather than use a sub-form control, I actually make a form's command button launch a separate form from the main control form, which otherwise is mostly unbound. (It is the "mostly" part that still gives me grief sometimes.) A sub-form has to be ready to go quickly (in Access terms) but if it is a separately launched form, you get some benefits.

1. The data on the separate form is more isolated in nature i.e. narrowly focused - and therefore MIGHT represent a smaller data set, so the recordset initialization is possibly faster in such cases.

2. The separate form load is going to cost less than loading a bunch of smaller but data-rich tabs with sub-form. EVERY CONTROL has to be realized (during the Form_Load event) before you ever get to the Form_Current event.

3. Any form for which you DON'T click the button never loads doodlum-squat and therefore never takes up ANY time to load.

4. If you have to consult data from your main form, all of its controls are available with syntax for looking at controls on any other open form, whether or not the open form is visible and "on top" at the moment.

The down side is that you might need to use some code to maximize or otherwise manipulate the form's child windows to keep things from getting visually "messy."

There have been several articles on the topic of "maximize form window" so I'll let you search the forum for that topic. In essence, you define some external Windows entry points and use a particular property of each form that identifies the form's child-window "handle." Having that, you have control of all sorts of neat effects. For instance, one I happen to like is that I can minimize a form before I actually close it. It gives the appearance of closing faster than it really does because you can't see it any more. If you have to do some data manipulation or event logging behind the scenes, a minimized form can still do that.