Synchronized combo boxes in subform

[Guest]

My two combo boxes work perfectly when I open the subform on it own from the
Forms window - but when I open the main form in which the subform resides -
the combo boxes in the subform no longer work. Any ideas?

Thank you in advance for any help!

-Rachel

 

[kingston via AccessMonster.com]

What do these combo boxes do? Can you post the Row Sources and code if any?
If they refer to one another, do you use a Me. reference or a Forms!FormName!
reference? Use the Me. reference to avoid problems when embedding one form
into another.

 

[Guest]

The first combo box lists our branches and the second lists our customers
within each branch. Here's the code for both:

Private Sub cboFind_AfterUpdate()
Me.RecordsetClone.FindFirst "[COMPANY_NAME] = '" & Me.cboFind & "'"
Me.CUSTOMER_NUMBER.Value = Me.cboFind.Column(1)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark

End If
End Sub

Private Sub cboFirst_AfterUpdate()
cboFind.Requery
cboFind.SetFocus
End Sub

Row Source for Branch:
SELECT Tbl_Customers_03.BRCH_LOCATION_CODE FROM Tbl_Customers_03 GROUP BY
Tbl_Customers_03.BRCH_LOCATION_CODE;

Rose Source for Customer:
SELECT Tbl_Customers.CL_NAME, Tbl_Customers.CL_ID,
Tbl_Customers.BRCH_LOCATION_CODE
FROM Tbl_Customers
WHERE (((Tbl_Customers.BRCH_LOCATION_CODE)=[Forms]![Frm_Quotes]![cboFirst]))
ORDER BY Tbl_Customers.CL_NAME;


These combo boxes work perfectly when the subform is opened on its own - but
they don't work at all when the main form is opened that contains the subform.

Thank you!!!
Rachel

 

[Rick Brandt]

Rose Source for Customer:
SELECT Tbl_Customers.CL_NAME, Tbl_Customers.CL_ID,
Tbl_Customers.BRCH_LOCATION_CODE
FROM Tbl_Customers
WHERE (((Tbl_Customers.BRCH_LOCATION_CODE)=[Forms]![Frm_Quotes]![cboFirst]))
ORDER BY Tbl_Customers.CL_NAME;

References like...
[Forms]![Frm_Quotes]![cboFirst]

....do not work when the form is made into a subform. You now have to reference
that form by going thorugh the parent form and the subform control.

[Forms]![ParentFormName]![SubformControlName].Form![cboFirst]

 

[Guest]

That fixed it! Thanks!

Rose Source for Customer:
SELECT Tbl_Customers.CL_NAME, Tbl_Customers.CL_ID,
Tbl_Customers.BRCH_LOCATION_CODE
FROM Tbl_Customers
WHERE (((Tbl_Customers.BRCH_LOCATION_CODE)=[Forms]![Frm_Quotes]![cboFirst]))
ORDER BY Tbl_Customers.CL_NAME;

References like...
[Forms]![Frm_Quotes]![cboFirst]

....do not work when the form is made into a subform. You now have to reference
that form by going thorugh the parent form and the subform control.

[Forms]![ParentFormName]![SubformControlName].Form![cboFirst]

 

[Guest]

That fixed it! Thanks!

Rose Source for Customer:
SELECT Tbl_Customers.CL_NAME, Tbl_Customers.CL_ID,
Tbl_Customers.BRCH_LOCATION_CODE
FROM Tbl_Customers
WHERE (((Tbl_Customers.BRCH_LOCATION_CODE)=[Forms]![Frm_Quotes]![cboFirst]))
ORDER BY Tbl_Customers.CL_NAME;

References like...
[Forms]![Frm_Quotes]![cboFirst]

....do not work when the form is made into a subform. You now have to reference
that form by going thorugh the parent form and the subform control.

[Forms]![ParentFormName]![SubformControlName].Form![cboFirst]

 

[kingston via AccessMonster.com]

Modify the row source:
...
WHERE (((Tbl_Customers.BRCH_LOCATION_CODE)='" & Me.cboFirst "'))
...
or use a fully qualified reference.

The first combo box lists our branches and the second lists our customers
within each branch. Here's the code for both:

Private Sub cboFind_AfterUpdate()
Me.RecordsetClone.FindFirst "[COMPANY_NAME] = '" & Me.cboFind & "'"
Me.CUSTOMER_NUMBER.Value = Me.cboFind.Column(1)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark

End If
End Sub

Private Sub cboFirst_AfterUpdate()
cboFind.Requery
cboFind.SetFocus
End Sub

Row Source for Branch:
SELECT Tbl_Customers_03.BRCH_LOCATION_CODE FROM Tbl_Customers_03 GROUP BY
Tbl_Customers_03.BRCH_LOCATION_CODE;

Rose Source for Customer:
SELECT Tbl_Customers.CL_NAME, Tbl_Customers.CL_ID,
Tbl_Customers.BRCH_LOCATION_CODE
FROM Tbl_Customers
WHERE (((Tbl_Customers.BRCH_LOCATION_CODE)=[Forms]![Frm_Quotes]![cboFirst]))
ORDER BY Tbl_Customers.CL_NAME;

These combo boxes work perfectly when the subform is opened on its own - but
they don't work at all when the main form is opened that contains the subform.

Thank you!!!
Rachel

What do these combo boxes do? Can you post the Row Sources and code if any?
If they refer to one another, do you use a Me. reference or a Forms!FormName!

[quoted text clipped - 8 lines]