How to link dynamically loaded unbounded subforms to main form?

Question

I have a bound main form (FormA) with a combobox on it and two unbound subforms (subfrmA & subfrmB). (Both forms are attached to a table however I want them to load onto the main form where I placed an unbound subform as a placeholder.)

The combobox has two values “a” and “b.” When a is selected I want subfrm A to load onto Form A. When b is selected I want subfrmB to load onto Form A. I think have this part working.

However when I select a record on the main form the associated subforms don’t appear. When I try to link the subforms to the main form an error message appears saying I can’t build a link between unbound forms.

The packageID is the link between the main form and subform and is a hidden field on all forms. Whenever the packageID is automatically updated the psckageID in the subform fields are also updated.

Case”A”                  
    Me.subfrmAB.SourceObject=“FormA
    Me.packageDetailsID=Me.subfrmAB.packageDetailsID
Case “B”                                                  
    Me.subfrmAB.SourceObject=“FormB”
    Me.packageDetailsID=Me.subfrmAB.packageDetailsID

EDIT: I ended up creating two subforms subfrmA (Form A) and subfrmB (Form B). Then I linked both to the parent form via the master and child links.

I make one of the subforms visible and the other invisible depending on what the user selects in the combobox of the main form.

Everything works except Form B won’t load, but the container loads. I tried loading Form B separately by itself it still won’t load. I also deleted subfrmA and Form B still doesn’t load.

Here is my edited code:

Select Case Me.Authorization.Text
    Case “A”
        Me.subfrmA.Visible = True
        Me.subfrmB.Visible = False
        Me.subfrmA.SourceObject = “Form.A”
    Case “B”
        Me.subfrmB.Visible = True
        Me.subfrmA.Visible = False
        Me.subfrmB.SourceObject = “Form.B”
End Select

The only line that doesn’t work is the Me.subfrmB.SourceObject=“Form.B” and really there’s something that’s preventing the form specifically loading. I wrote the same code for Form A and Form B but can’t figure out what’s wrong with Form B.

Answer 1

Can certainly be done. Here is a simple example that works for me.

Main form is bound to table Games. Forms used as subform are Umpires and Teams.

Combobox properties:

ControlSource: UNBOUND
RowSource: Umpires;Plate;UmpID;Teams;HomeTeam;TeamID
RowSourceType: ValueList
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 1.0";0";0"

Code:

Private Sub Combo108_AfterUpdate()
With Me
.ctrAB.SourceObject = .Combo108
.ctrAB.LinkMasterFields = .Combo108.Column(1)
.ctrAB.LinkChildFields = .Combo108.Column(2)
End With
End Sub

You could have "A", "B" for the form names in combobox RowSource and then if both forms have same name key fields, don't need them in RowSource, just hard coded. Not entirely clear what the key field names are. Then code like:

.subfrmAB.SourceObject = "subfrm" & .Combo108
.subfrmAB.LinkMasterFields ="packageDetailsID"
.subfrmAB.LinkChildFields = "packageDetailsID"

If you want to save "A" and "B" to main form record, then bind the combobox to field. Then for subforms to change for each record while navigating main form, also have code in form OnCurrent event.

Something to be aware of when coding interaction between form/subform: subforms load before main form - seems odd but is true.