Extremely large forms

[way2bord]

I have an extremely large data entry form (think 500 - 600 various combo/text/chkbox objects).

Despite personal objections, all objects must be present in a single visual end-user form.
....

In terms of load speed, processing, form automations and vba -- what is the fastest and most effective way of dealing with this monstrous form?

Would division into multiple subforms negatively impact the already slow load speed?

Are there any tricks to "speed up" load time for this form?
Are there any tricks to speed up processing and backend data interaction for this form? (various end-user access is given/denied to portions of form via log-in, automated notifications are distributed based on certain submitted fields, data auditing of all fields is enabled, dynamic normalization of data occurs, dynamic formatting of data occurs, etc.)

:banghead:

 

[MarkK]

My 2c is that 500-600 controls on a form is silly. There is no serious problem that would benefit from having that as a solution.

 

[billmeye]

Are all these controls Bound to the same table? I would suggest breaking the form (and table(S)) up into small manageable units. Use a tab control on the Main form with all but the first tab's subform control SourceObject set to "". In the tab controls On Change event load each subform as needed. It is very fast and user friendly. For example,

Private Sub TabCtl0_Change()
20    Select Case Me.TabCtl0.Pages.item(Me.TabCtl0).NAME

      Case "page1"
30    Me.subfrm1.SourceObject = "subfrm1"

40    Case "page2"
50    Me.subfrm1.SourceObject = "subfrm2"

60    Case "page3"
70    Me.subfrm3.SourceObject = "subfrm3"
Case else
End Select

 

[way2bord]

My 2c is that 500-600 controls on a form is silly. There is no serious problem that would benefit from having that as a solution.

While I agree with you - and I am working to change the current paradigm - several teams want a single form with full viewable content. No pop-ups, no tabs, and denormalized data entry (think: textboxes for customer A, customer B, customer C...).

Are all these controls Bound to the same table? I would suggest breaking the form (and table(S)) up into small manageable units. Use a tab control on the Main form with all but the first tab's subform control SourceObject set to "". In the tab controls On Change event load each subform as needed. It is very fast and user friendly. For example,

Controls aren't really bound to any table - the input is manipulated prior to being appended into a table. It is distributed to the appropriate tables via recordset manipulations in VBA.
Tabs aren't an option as this would prevent all data from being viewed at once. If they take a screenshot, all fields must be present on screen in one shot.

 

[billmeye]

Then group controls based on assigning Tags named customer A, customer B, customer C etc. and hide all but those needed when the form loads. Have buttons that when clicked make visible/hide each customer. Maybe that would speed things up. Using Tags allows you to cycle through controls without the need for specifically identifying the control by name or type etc.

Sub Button1_OnClick()
If Me.Button1.Caption = "Make Visible" then Me.Button1.Caption = "Hide" Else Me.Buntton1.Caption = "Make Visible"
For Each ctl In Me.Controls
    If Me.Button1.Caption = "Make Visible" then
        If ctl.Tag = "Customer A" then ctl.visible = True
    Else
        If ctl.Tag = "Customer A" then ctl.visible = False
    End If
Next ctl
End Sub
Sub Button2_OnClick()
If Me.Button2.Caption = "Make Visible" then Me.Button2.Caption = "Hide" Else Me.Buntton2.Caption = "Make Visible"
For Each ctl In Me.Controls
    If Me.Button2.Caption = "Make Visible" then
        If ctl.Tag = "Customer B" then ctl.visible = True
    Else
        If ctl.Tag = "Customer B" then ctl.visible = False
    End If
Next ctl
End Sub
'etc. for each grouping

 

[way2bord]

Then group controls based on assigning Tags named customer A, customer B, customer C etc. and hide all but those needed when the form loads. Have buttons that when clicked make visible/hide each customer. Maybe that would speed things up. Using Tags allows you to cycle through controls without the need for specifically identifying the control by name or type etc.

Sub Button1_OnClick()
If Me.Button1.Caption = "Make Visible" then Me.Button1.Caption = "Hide" Else Me.Buntton1.Caption = "Make Visible"
For Each ctl In Me.Controls
    If Me.Button1.Caption = "Make Visible" then
        If ctl.Tag = "Customer A" then ctl.visible = True
    Else
        If ctl.Tag = "Customer A" then ctl.visible = False
    End If
Next ctl
End Sub
Sub Button2_OnClick()
If Me.Button2.Caption = "Make Visible" then Me.Button2.Caption = "Hide" Else Me.Buntton2.Caption = "Make Visible"
For Each ctl In Me.Controls
    If Me.Button2.Caption = "Make Visible" then
        If ctl.Tag = "Customer B" then ctl.visible = True
    Else
        If ctl.Tag = "Customer B" then ctl.visible = False
    End If
Next ctl
End Sub
'etc. for each grouping

This might work to an extent. Hide portions of the form by section. Encourage the end-user to get used to "pseudo-forms" by section, while speeding up initial load time by hiding some initial visuals...

 

[MarkK]

A thing takes the same time to load if it is visible or not.

 

[Pat Hartman]

Since tables/queries can contain a max of 255 columns, you require a minimum of two tables/queries and therefore a minimum of two forms.

I also think this is a poor design.

 

[gemma-the-husky]

even if they think that's what they want, they don't what that.

what you are designing can only look like a continuous form, or a data sheet - so provide one of those instead.

at the moment presumably you re-design your form every time there is a new customer?