Access Subform Source object

Question

What I am trying to achieve is for a combo box (Combo_sf) selection to dictate the form in the subform control (sf_record) I have about 10 forms, their names are in the combo box data. I am new to VBA and am not sure if my approach is right:

Private Sub Combo_sf_AfterUpdate()
    Dim strLoadTable As String  

    strLoadTable = "Form." & Me.Combo_sf.Value
    MsgBox strLoadTable

    Forms![frm_Mnu_Manage Configuration Settings]!sf_record.Form.SourceObject = strLoadTable

End Sub

I have placed this in the combobox's after update event but when I make my selection nothing happens in the form. Am I approaching this right or would another way work better?

Answer 1

Your approach should work. I put a combo box named cbxSubform on my main form and added one line of code to its AfterUpdate() event handler...

Private Sub cbxSubform_AfterUpdate()
Me.mySubform.SourceObject = Me.cbxSubform.Value
End Sub

...and changing the selection in the combo box switches the subforms immediately. Are you sure that the AfterUpdate() code for your combo box is actually firing? (You could add a MsgBox or a Debug.Print to check.)

Answer 2

It could be this line which is tripping you up:

strLoadTable = "Form." & Me.Combo_sf.Value

What is your form object called? If your form is called Form.myTableName it could be the . that is throwing it out, try setting it to a form without a dot in its name.

Answer 3

In this line, it seems the code attempts to change the SourceObject property of a Form object.

Forms![frm_Mnu_Manage Configuration Settings]!sf_record.Form.SourceObject = strLoadTable

However, SourceObject is a property of a subform control, not the form contained in that control. So if the subform control is named sf_record, do it this way.

Forms![frm_Mnu_Manage Configuration Settings]!sf_record.SourceObject = strLoadTable

Also, if the after update procedure runs from [frm_Mnu_Manage Configuration Settings], you can use Me to refer to the form.

Me!sf_record.SourceObject = strLoadTable

Finally, if Me.Combo_sf.Value is the name of a form, you don't need to prefix its name with "Form.". It worked either way in my test, but I would just leave off "Form.".

strLoadTable = Me.Combo_sf.Value