Variable Subforms from Main Form

[darkstar257]

I have a main form that contains entries of materials. I wanted to have a subform table of variables for each material type. When you build a sub form in a main form it's always static. Is there a way of dynamically populating one or accessing one depending on some boolean? I want to make the main form show a different subform depending on what material type is selected.

 

[vapid2323]

Would something like this work for you?

Private Sub FrmStaffView_AfterUpdate()
If Me.FrmStaffView.Value = 1 Then
    Me.frmSiteStaff.SourceObject = "frmSiteStaff"
    Me.frmSiteStaff.LinkChildFields = "tblSite.SiteID"
    Me.frmSiteStaff.LinkMasterFields = "SiteID"
Else
    Me.frmSiteStaff.SourceObject = "frmHQStaff"
    Me.frmSiteStaff.LinkChildFields = "tblSiteHQ.SiteHQID"
    Me.frmSiteStaff.LinkMasterFields = "fk_SiteHQID"
End If
End Sub

 

[darkstar257]

That worked great thank, but after I set my entire database up. I made the code run during Private Sub Form_Current()... However, I keep getting "The Object Doesn't Contain the Automation Object '...[subform name]...'" error every time I try to enter in a new or update a field in the subforms. But after I click OK, that subform entry behaves normally as intended. This is annoying as it happens in every new entry when data is altered for the first time.

Whenever I start new record entry in the main form, the two subforms will populate with a proper subform depending on "Material Type." The issue seems like the proper subform pulls up, but the actual blank record entry doesn't get created in the subform from the code. All the code seems to do is relink the master and child fields to subform.