Form-loading efficiency

[Brian]

One form in an app of mine has 40+ bound controls. Splitting the table into
multiple tables & corresponding forms is not really an option because each
field really is necessary for a complete record.

The form loads rather slowly compared to most of the other forms, presumably
because of the number of bound controls. I have already gone through creating
a persistent connection to a form opened earlier in the application.

Would it help me out any here to load the control's RowSources in their
Enter events help me any here? If so, are there any pitfalls to avoid? Other
suggestions?

I could split the form into mutliple tabs, but I understand this is not
likely a move toward greater efficiency.

 

[Allen Browne]

Hi Brian. This is a matter of pinning down where the bottleneck is.

There's a good list of items to work through in Tony Toews' Access
Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
Basic things like setting the SubDataSheetName properties to No, and the
Name AutoCorrect options to No make a big difference.

You mention RowSource, so presumably you have lots of combos or list boxes.
If each of these controls has thousands of records, that is your bottleneck.
If many of the controls have the same RowSource, you might be able to work
around the issue by using a callback function as the RowSourceType. Or you
might be able to delay-load some of the very large combos:
http://allenbrowne.com/ser-32.html

 

[Brian]

Thanks, Allen.

I had been through most of the efficiency-gaining things, including turning
off Name AutoCorrect. I will try setting SubDataSheet name to [None] also.

All the combo boxes need to be used as drop-downs, though, so the method of
setting the RowSource after three-character input will probably annoy the
users. There are not a huge number of records yet, though, so I really
suspect the form is slow opening just by virtue of the sheer number of bound
controls, most of them being combo boxes with accompanying RowSource.

I thought it might be workable to just load the RowSource in each combo
box's Enter event, like this?

Private Sub Combo1_Enter()
If IsNull (combo1.RowSource) then combo1.RowSource = "Select * from Table1"
End Sub

If this is even a workable approach, how do I handle the fact that the
LimitToList property will not allow current data to display until the
control's RowSource has been loaded? Will I also have to turn that off & on
with the RowSource?

 

[Allen Browne]

If most of the controls on the form are combos that each load thousands of
records from different RowSource tables and have a zero-width bound column,
you're stuck.

If the bound column is not zero-width, the data would still show, even if
there were no RowSource yet.

If several combos draw records from the same table, you could consider
loading them from an array via a callback function. There's a demonstration
of this kind of function in Method 2 in this artricle:
http://allenbrowne.com/ser-19.html
That example loads a list box with the names of the reports, but you could
OpenRecordset in the first case (acLBInitialize) to load your array.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen.

I had been through most of the efficiency-gaining things, including
turning
off Name AutoCorrect. I will try setting SubDataSheet name to [None] also.

All the combo boxes need to be used as drop-downs, though, so the method
of
setting the RowSource after three-character input will probably annoy the
users. There are not a huge number of records yet, though, so I really
suspect the form is slow opening just by virtue of the sheer number of
bound
controls, most of them being combo boxes with accompanying RowSource.

I thought it might be workable to just load the RowSource in each combo
box's Enter event, like this?

Private Sub Combo1_Enter()
If IsNull (combo1.RowSource) then combo1.RowSource = "Select * from
Table1"
End Sub

If this is even a workable approach, how do I handle the fact that the
LimitToList property will not allow current data to display until the
control's RowSource has been loaded? Will I also have to turn that off &
on
with the RowSource?

Hi Brian. This is a matter of pinning down where the bottleneck is.

There's a good list of items to work through in Tony Toews' Access
Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
Basic things like setting the SubDataSheetName properties to No, and the
Name AutoCorrect options to No make a big difference.

You mention RowSource, so presumably you have lots of combos or list
boxes.
If each of these controls has thousands of records, that is your
bottleneck.
If many of the controls have the same RowSource, you might be able to
work
around the issue by using a callback function as the RowSourceType. Or
you
might be able to delay-load some of the very large combos:
http://allenbrowne.com/ser-32.html

 

[Brian]

Thanks, Allen.

None of these is bound to a table that has thousands of records. Most are
somewhere between 10 and 100. This is the last part of a complete overhaul of
the program in terms of efficiency. I already set up the persistent back-end
connection.

This form just remains slower to load than any others (about 15 seconds,
compared to 1/2-2 seconds for most other forms), presumably due to the number
of bound controls; almost all of them are, indeed, bound to the primary key
that is a 0-width Column1.

I am working on setting the SubdatasheetName to [None] en masse since I have
not yet tried that.

If most of the controls on the form are combos that each load thousands of
records from different RowSource tables and have a zero-width bound column,
you're stuck.

If the bound column is not zero-width, the data would still show, even if
there were no RowSource yet.

If several combos draw records from the same table, you could consider
loading them from an array via a callback function. There's a demonstration
of this kind of function in Method 2 in this artricle:
http://allenbrowne.com/ser-19.html
That example loads a list box with the names of the reports, but you could
OpenRecordset in the first case (acLBInitialize) to load your array.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen.

I had been through most of the efficiency-gaining things, including
turning
off Name AutoCorrect. I will try setting SubDataSheet name to [None] also.

All the combo boxes need to be used as drop-downs, though, so the method
of
setting the RowSource after three-character input will probably annoy the
users. There are not a huge number of records yet, though, so I really
suspect the form is slow opening just by virtue of the sheer number of
bound
controls, most of them being combo boxes with accompanying RowSource.

I thought it might be workable to just load the RowSource in each combo
box's Enter event, like this?

Private Sub Combo1_Enter()
If IsNull (combo1.RowSource) then combo1.RowSource = "Select * from
Table1"
End Sub

If this is even a workable approach, how do I handle the fact that the
LimitToList property will not allow current data to display until the
control's RowSource has been loaded? Will I also have to turn that off &
on
with the RowSource?

Hi Brian. This is a matter of pinning down where the bottleneck is.

There's a good list of items to work through in Tony Toews' Access
Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
Basic things like setting the SubDataSheetName properties to No, and the
Name AutoCorrect options to No make a big difference.

You mention RowSource, so presumably you have lots of combos or list
boxes.
If each of these controls has thousands of records, that is your
bottleneck.
If many of the controls have the same RowSource, you might be able to
work
around the issue by using a callback function as the RowSourceType. Or
you
might be able to delay-load some of the very large combos:
http://allenbrowne.com/ser-32.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

One form in an app of mine has 40+ bound controls. Splitting the table
into
multiple tables & corresponding forms is not really an option because
each
field really is necessary for a complete record.

The form loads rather slowly compared to most of the other forms,
presumably
because of the number of bound controls. I have already gone through
creating
a persistent connection to a form opened earlier in the application.

Would it help me out any here to load the control's RowSources in their
Enter events help me any here? If so, are there any pitfalls to avoid?
Other
suggestions?

I could split the form into mutliple tabs, but I understand this is not
likely a move toward greater efficiency.