Help with linking a main form's combobox value to the subform

[ND Pard]

I am embarassed to ask this question ... but I'd sure appreciate a quick
answer.

If I create a main form with a text box bound to the field: Dept_ID and a
subform, I can link it via the field: Dept_ID because both forms contain a
field named: Dept_ID.

But I can't seem to link it if instead of using a text box in the main form,
I use a ComboBox that display the Dept_IDs. How can I link the value in the
combobox that shows a Dept_ID to the subforms Dept_ID?

 

[Arvin Meyer MVP]

I am embarassed to ask this question ... but I'd sure appreciate a quick
answer.

If I create a main form with a text box bound to the field: Dept_ID and a
subform, I can link it via the field: Dept_ID because both forms contain a
field named: Dept_ID.

But I can't seem to link it if instead of using a text box in the main
form,
I use a ComboBox that display the Dept_IDs. How can I link the value in
the
combobox that shows a Dept_ID to the subforms Dept_ID?

Remember, it's not the control on the form that's linked, it's the field
it's bound to. There should be no difference in the way a combo works than
the way a text box works. You can link a subform to a control though, as
when you are trying to link data in a nested subform.

To do that, set an unbound textbox on the main form to the subform's ID
value:

= Forms!FormName!Subform1Name.Form!ID

then use that control's name in the Link Master property of Subform2.

 

[ND Pard]

Thanks Arvin for the tip.

I resolved it by placing Afterupdate event code into my main form similar to
the following:

Me.Filter = "[Dept_ID] = '" & Me.Combo1 & "'"
Me.FilterOn = True

Thanks for the prompt response and hint.

 

[ND Pard]

Thanks for the response Arvin.

I resolved my dilema by entering AfterUpdate event code like this:

Me.Filter = "[Dept_ID] ='" & Me.Combo1 & "'"

Me.FilterOn = True

Thanks for the hint.

 

[Beetle]

That depends

Is the combo box the only control on the main form? Are you just
using it to select the DeptID and all other relevant data displays in
the subform?

Or are there other controls on the main form that display data
from the main form's record source?

If the former, you can use an unbound combo box to select the DeptID
and just use the name of the combo box in the Master Link property
of the subform control.

If the latter, then you need both the main form and the subform to
move to the correct record based on what is selected in the unbound
combo box. In that case you would use DeptID as both the Master and
Child link. Keep in mind you do not have to have a control (text box, etc.)
on your form in order to use the DeptID field as the Master link. As long
as the field exists in the record source you can refer to it. Then in the
After Update event of your unbound combo box you would use code like;

Private Sub cboMyCombo_AfterUpdate ()

With Me.RecordsetClone
.FindFirst "DeptID=" & Me.cboMyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub


This would move the main (and the subform) form to a
matching Dept record after the value in the combo box changes.

The above code assumes your DeptID field is a number data type.

 

[ND Pard]

Thanks Beetle ... all I needed to know was to write the Name of the ComboBox
for the value of the "Link Master Fields".

That was sure simple (as I figured it probably was) and sure simpler than my
earlier solution.

Thanks.