Optimizing Access 2007 Forms

[tcb]

I converted an Access 2000 mdb to 2007 for testing. There is a form
that has great functionality from a user standpoint but really takes a
performance hit in 2007.

For example, on an after update event of a combo box the settings of
several controls can change such as enabled, visible, rowsource,
caption, and requery.

Something like this may happen:

[cboExportWeek].RowSource = "SELECT Season, SeasonID FROM Seasons
ORDER BY [StartDate]"
[cboExportWeek].BoundColumn = 2
[cboExportWeek].Requery


And this (changing the enabled setting for this group of controls
takes about 1.5+ seconds in A2007):

[optItem].Enabled = False
[optLinkedItem].Enabled = False
[optDept].Enabled = False
[optClass].Enabled = False
[optUPC].Visible = False
[optWeek].Enabled = False
[optMonth].Enabled = False
[optManufacturer].Enabled = False
[optNone].Enabled = True
[optBrand].Enabled = False
[optCategory].Enabled = False
[optSubcategory].Enabled = False
[optSegment].Enabled = False
[optUD1].Enabled = False
[optUD2].Enabled = False
[optUD3].Enabled = False
[optUD4].Enabled = False
[optProductFamily].Enabled = False
[optVendorStyle].Enabled = False

I need advice on how to optimize this form. In XP and 2000 the form
is functional. In Vista and 2007 it is not usable. Thanks.

 

[Allen Browne]

Setting a property is much slower than reading it, so don't change it if
it's already set correctly.

This kind of thing:
With Me.optItem
If .Enabled Then .Enabled = False
End With

The prefix "opt" generally indicates an option button, which is usually in
an option group. You would normally use the Value of the group to toggle the
state of the controls. Or perhaps this was just an example.