Access Slave Source Object

Question

What I'm trying to achieve is choosing combo box (Combo_sf) to dictate the form in the subform control (sf_record). I have about 10 forms, their names are listed in the combo box data. I am new to VBA and 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 put this in the combo box after the update event, but when I make my choice, nothing happens in the form. Am I approaching this right or will I work better?

Answer 1

Your approach should work. I set the cbxSubform combo cbxSubform in my main form and added one line of code to my AfterUpdate() event AfterUpdate() ...

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

... and changing the selection in the combo box immediately switches the subforms. Are you sure the AfterUpdate() code for your combo box really shoots? (You can add MsgBox or Debug.Print for verification.)

Answer 2

It could be this line that disconnects you:

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

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

Answer 3

On this line, the code seems to be trying to change the SourceObject property of the Form object.

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

However, the SourceObject is a property of the control of the subordinate form, not the form contained in this control. Therefore, if the subform control is called sf_record, do it like this.

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

In addition, if the procedure after the upgrade works from [frm_Mnu_Manage Configuration Settings] , you can use Me to link to the form.

 Me!sf_record.SourceObject = strLoadTable 

Finally, if Me.Combo_sf.Value is the name of the form, you do not need to attribute its name to "Form". In my test, it worked anyway, but I would just leave the “Form”.

 strLoadTable = Me.Combo_sf.Value