subforms/combo boxes

[misschanda via AccessMonster.com]

I have form1 that has a subform and a macro that opens up form2. Form2 has
cascading combo boxes, with the final choice needing to be represented in the
subform on form1.. How can I input the final choice on form 2 to that of the
subform on form1???

Thanks
LA

 

[Douglas J. Steele]

You could put code in the AfterUpdate event of the last combo box on Form2.

Something like:

Private Sub MyCombo_AfterUpdate()

Forms!Form1!SubformControlName.Form!ControlOnSubformName = Me.MyCombo

End Sub

Note that depending on how you added the subform to Form1, the name of the
subform control may not be the same as the name of the form being used as a
subform.

 

[Guest]

To keep track of the players, let's assume form 1's name is: "My Form 1"
with subform named "subform_in_Form_1" Note that this is the name
of the object as referenced withing Form1, not necessarily the name of the
form defintiion in the FORMS container of the database window.
Lastly, in form2 your final combo box is named "cbo_Final".

In form2's code behind the forms
' =======================
Private Sub cbo_Final_Click()
Dim frm1 As Form
Dim sfm1 As Form

' Make sure the form is loaded to receive the message...
If Not CurrentProject.AllForms("My Form 1").IsLoaded Then
Set frm1 = Forms![My Form 1].Form
Set sfm1 = frm1.[subform_in_Form_1].Form

' You may now reference the contents of the subform. For example you may
change the FILTER property, or the RECORDSOURCE property.
-----
' Example 1
sfm1.Filter = "[something] = " & Me.cbo_Final
sfm1.FilterOn = True
-----
' Example 2
Dim SQL As String
SQL = "SELECT ..... FROM .... WHERE [something] = " & Me.cbo_Final
sfm1.Recordsource = SQL
-----
Set frm1 = Nothing
Set sfm1 = Nothing
Else
MsgBox "Oops. My Form 1 not loaded.",vbOkOnly+vbCritical,"Error"
End If
End Sub
' ===================