Access - How to link dynamically loaded unbounded subforms to main form?

Question

New to access and vba.

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. — So far I think have this part working

However when I select a record on the main form the associated subforms doesn’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 snd is a hidden field on all forms. Whenever the packageID is automatically updated the psckageID in the subform fields are also updated.

form design view

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

                                                              
            Me.subfrmAB.SourceObject=“FormB”
            Me.packageDetailsID=Me.subfrmAB.packageDetailsID

EDIT: So what I ended up doing was 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 perfect 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 that’s preventing it from loading .

An unbound subform has no record to show until filled with one record, not several. I guess you need to rephrase your question or reconsider your setup. — Gustav
I’m new to access and vba. Is there a way to do the below. I’d like to dynamically load a specific subform onto the main form based on combobox selection from the main form. And when user clicks on a specific record within the main for, I’d like the associated subform to appear. — Sonia Selvan
What do you mean by 'unbound' subform? If form has a RecordSource it is bound. Set subform container control properties: SourceObject, MasterLink, ChildLink. Edit question to post attempted code. What tables are forms bound to? — June7
Code should be posted as text, not image. Why would you need to black out object names? Why are you setting PackageDetail_ID value? That makes no sense. Are all forms bound to data - have RecordSource? — June7
You don't populate a form by setting one of its field values. That will not pull existing records, it only puts value into a field. You need to either set RecordSource property with a filtered dataset by reference to a query object or use an SQL statement. Or apply filter by setting Filter and FilterOn properties of form that has static RecordSource. How did you get smart quote characters in your code - VBA will not like those. And your code shows syntactical errors. Did you not copy/paste? — June7

June7 June7 · Accepted Answer · 2021-01-29T00:22:16

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.

Access - How to link dynamically loaded unbounded subforms to main form?

2 Answers