synchronize two combo boxes.

  • Thread starter Thread starter abajo
  • Start date Start date
A

abajo

I'm trying to synchronize two combo boxes, one on my main
form and the second on a sub form. Microsoft's Knowledge
base article 209595 explains how to do this when both
combo boxes are on the same form. Is there a way to do
this when your combo boxes are not on the same form?

Open the sample database Northwind.mdb.
Create a new form not based on any table or query with the
following combo boxes, and save the form as Categories And
Products. Combo Box 1
-------------------------------
Name: Categories
RowSourceType: Table/Query
RowSource: Categories
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
AfterUpdate: [Event Procedure]

Combo Box 2
--------------------------
Name: Products
RowSourceType: Table/Query
ColumnWidths: 2"
Width: 2"


Add the following code to the AfterUpdate event procedure
of the Categories combo box:

Me.Products.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.Categories & _
" ORDER BY ProductName"
Me.Products = Me.Products.ItemData(0)
 
To follow your example, I think you can simply change the
RowSource of the ComboBox on the other Form to:

Forms!OtherForm!ComboBoxOnOtherForm.RowSource = _
"SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.Categories & _
" ORDER BY ProductName"
'(and if required)
Forms!OtherForm!ComboBoxOnOtherForm.Products = _
Forms!OtherForm!ComboBoxOnOtherForm.ItemData(0)

Note: You need to ensure that the OtherForm is open at the
time the code is executed. You may want to write some
sode to check that the OtherForm is open.

HTH
Van T. Dinh
MVP (Access)
 
Thanks for your help..

I tried what you said, but keep getting a runtime error
2450. I know the other form is open because it is nested
in my main form. Below is the code I am using.


Private Sub cbopca_AfterUpdate()

Forms!frm_tbl_objectives.cboobjectives.RowSource = "SELECT
objective FROM" & _
" dbo_view_tasks WHERE grant = '" & Me.cbogrant & "'
AND pca = '" & Me.cbopca & _
"' GROUP BY objective ORDER BY objective"
Forms!frm_tbl_objectives.cboobjectives = Forms!
frm_tbl_objectives.cboobjectives.ItemData(0)
End Sub

I'm trying to synchronize three combo boxes, two are on
the main form called "frm_tbl_master_budget"(the two combo
boxes are called "cbogrant" and "cbopca") the third is on
a subform called "frm_tbl_objectives" and it's combo box
is called "cboobjectives".

Thanks again
-----Original Message-----
Sure, just change the statement that creates the Recordsource SQL to refer
to the other form instead of 'me' (the current form). For example - this
would refer to the Categories control on a form named frmOtherForm.

Me.Products.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & forms! frmOtherForm.Categories & _
" ORDER BY ProductName"


Note that you should probably make sure that the other form is actually open
first or you will end up with a runtime error when it can't find
forms!frmOtherForm. Use the IsLoaded function from the Northwind database.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I'm trying to synchronize two combo boxes, one on my main
form and the second on a sub form. Microsoft's Knowledge
base article 209595 explains how to do this when both
combo boxes are on the same form. Is there a way to do
this when your combo boxes are not on the same form?

Open the sample database Northwind.mdb.
Create a new form not based on any table or query with the
following combo boxes, and save the form as Categories And
Products. Combo Box 1
-------------------------------
Name: Categories
RowSourceType: Table/Query
RowSource: Categories
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
AfterUpdate: [Event Procedure]

Combo Box 2
--------------------------
Name: Products
RowSourceType: Table/Query
ColumnWidths: 2"
Width: 2"


Add the following code to the AfterUpdate event procedure
of the Categories combo box:

Me.Products.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.Categories & _
" ORDER BY ProductName"
Me.Products = Me.Products.ItemData(0)
Click to expand...

.
Click to expand...
 
Ok, I thought we were dealing with to separate forms rather than a form and
subform. That does make a difference because to refer to a control on a
subform you must go through the subform control on the main form. A subform
is not a member of the forms collection (all open forms).

Following is the corrected reference to the combo on the subform. Note that
where I have frm_tbl_objectives you *should* have the name of the subform
control on the main form. This may or may not be the same as the name of the
form object which is named in the controlsource of the subform control. To
be sure, open the main form in design view, click once on the subform
control and then check the Name property under the Other tab on the property
sheet. Whatever you have there is what goes in place of frm_tbl_objectives
below:

me.frm_tbl_objectives.form.cboobjectives

With this in mind your new Rowsource assignment statement becomes:


me.frm_tbl_objectives.form.cboobjectives.RowSource = "SELECT objective FROM"
& _
" dbo_view_tasks WHERE grant = '" & Me.cbogrant & "' AND pca = '" &
Me.cbopca & _
"' GROUP BY objective ORDER BY objective"

me.frm_tbl_objectives.form.cboobjectives =
me.frm_tbl_objectives.form.cboobjectives.ItemData(0)
 
Back
Top