Does it degrade performance to use subforms in MS Access?

Question

I am considering the use of a tab control on a parent form for which I would like to have around 20 tabs. Each tab I am considering the use of one or two separate sub forms. Each sub form will have varied complexity in coded logic. By taking this approach will I severally reduce the performance of my application? I am currently using this in MS Access 2003. I will expect an average of 15 users at any given time on the various forms.

Thoughts?

Answer 1

Yes, performance will be degraded slightly for each subform. One or three isn't too bad but twenty is definitely going to cause you performance issues.

Once you have the subform working to your satisfaction either save the Record Source as a query and give it a name or save the query SQL string. Then either paste the query name or the query SQL string in the VBA code in the tab control change event.

Private Sub TabCtl_Change()
   On Error GoTo TabCtl_Change_Error

    Select Case Me.TabCtl.Value
    Case Me.pagPartsConsumed.PageIndex
        If Me.PartsConsumedsbf.Form.RecordSource <> "Equipment - Parts Consumed sbf" Then _
            Me.PartsConsumedsbf.Form.RecordSource = "Equipment - Parts Consumed sbf"
....

Now just to ensure that I don't accidentally leave some subform recordsources filled in slowing down the app on startup I check to see if the file the code is running is an MDB (instead of an MDE. The function is below) then display a message telling me I have to remove the recordsource.

   If Not tt_IsThisAnMDE Then
        If Me.PartsConsumedsbf.Form.RecordSource <> "" Then _
            MsgBox "Record source of Equipment - Parts Consumed sbf not empty"
        ...
   End If

Public Function tt_IsThisAnMDE()
On Error GoTo tagError

  Dim dbs As Database
  Set dbs = CurrentDb
  Dim strMDE As String
  On Error Resume Next
  strMDE = dbs.Properties("MDE")
  If Err = 0 And strMDE = "T" Then
    ' This is an MDE database.
    tt_IsThisAnMDE = True
  Else
    tt_IsThisAnMDE = False
  End If

    Exit Function

tagError:
    Call LogError(Application.CurrentObjectName, "")
    Exit Function

End Function

Also in the form unload event I clear the Recourd Source as well.

Private Sub Form_Unload(Cancel As Integer)

   On Error GoTo Form_Unload_Error

    Me.PartsConsumedsbf.Form.RecordSource = ""
    ....

BTW I almost always would put each subform on a seperate tab. Also that many tab entries gets visusally unwieldy. When I had a similar question my fellow Access MVPs suggested using a listbox along the left hand side to control which subform is viewable.

Also each combo box and list box will also slightly degrade the performance. So if you have those on a subform then consider similar logic.

Answer 2

In addition to adding recordsets at runtime, I would generally only use one or two tabs and a number of controls to load various subforms into a subform control.

The text for the On Click event of the control might be:

=WhichPage([Form],"lblLocations")

Where WhichPage is a function with the following lines, amongst others:

Function WhichPage(frm, Optional LabelName = "")
    <..>

    Select Case LabelName

    Case "lblLocations"
        frm("sfrmAll").SourceObject = "sfrmLocations"

<...>

If necessary, the link child and link master fields can be changed at runtime. The link master field is best set to the name of a control, rather than a field, to avoid errors.

Me.sfrmAll.LinkChildFields = "LocationKey"
Me.sfrmAll.LinkMasterFields = "txtLocationKey"

Answer 3

To expand on Remou's answer...here is a sub I wrote that dynamically loads a form into a subform control. You pass in the name of the form in the call and it will load it into the subform of the Main form. The arguments map to the arguments of Docmd.OpenForm method of Access. If the main form that is hosting the subform control is not open...it just does a regular open of the form. Otherwise it loads it into the subform control. If a where clause was passed in it is used to filter the subform.

Public Sub MyOpenForm(FormName As String, _
                            Optional View As AcFormView = acNormal, _
                            Optional FilterName As String = vbNullString, _
                            Optional WhereCondition As String = vbNullString, _
                            Optional DataMode As AcFormOpenDataMode, _
                            Optional WindowMode As AcWindowMode, _
                            Optional OpenArgs As String)

On Error GoTo PROC_ERR

Dim frm As Form
Dim strNewForm As String
Dim strCurrentForm As String
Dim strNewTable As String
Dim fDoNotFilter As Boolean
Dim strActionText As String
Dim strID As String

If Not IsLoaded("frmMain") Then
    DoCmd.OpenForm FormName:=FormName, View:=View, FilterName:=FilterName, WhereCondition:=WhereCondition, DataMode:=DataMode, WindowMode:=WindowMode, OpenArgs:=OpenArgs
Else
    strCurrentForm = Forms![frmMain]![sfrMyForm].SourceObject
    If strCurrentForm <> FormName Then
      Forms![frmMain]![sfrMyForm].SourceObject = vbNullString
      Forms![frmMain]![sfrMyForm].SourceObject = FormName
    End If
    If WhereCondition <> vbNullString Then
      Forms![frmMain]![sfrMyForm].Form.Filter = WhereCondition
      Forms![frmMain]![sfrMyForm].Form.FilterOn = True
    End If
End If


PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox Err.Description
  Resume PROC_EXIT

End Sub