Synchronized combo boxes in subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Rachel said:
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;
Click to expand...

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]
 
That fixed it! Thanks! :)

Rick Brandt said:
Rachel said:
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;
Click to expand...

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]
Click to expand...
 
That fixed it! Thanks! :)

Rick Brandt said:
Rachel said:
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;
Click to expand...

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]
Click to expand...
 
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!
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
Back
Top