Synchronize Two Combo Boxes on a Form

[KBDB]

from the Help files........
In this example, the second combo box is filled with the results of an SQL
statement. This SQL statement finds all the products that have a CategoryID
that matches the category selected in the first combo box.

Whenever a category is selected in the first combo box, its AfterUpdate
event procedure sets the second combo box's RowSourceType property. This
refreshes the list of available products in the second combo box. Without
this procedure, the contents of the second combo box would not change.

Private Sub cboCategories_AfterUpdate()

' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" tblProducts WHERE CategoryID = " &
Me.cboCategories & _
" ORDER BY ProductName"

Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

****
Me.cboCategories is giving the right value
Me.choProducts.RowSource is giving the right query.
But the recordsource is not being updated.
Me.choProducts.ItemData(0) is null

Any Ideals?

 

[banem2]

from the Help files........
In this example, the second combo box is filled with the results of an SQL
statement. This SQL statement finds all the products that have a CategoryID
that matches the category selected in the first combo box.

Whenever a category is selected in the first combo box, its AfterUpdate
event procedure sets the second combo box's RowSourceType property. This
refreshes the list of available products in the second combo box. Without
this procedure, the contents of the second combo box would not change.

Private Sub cboCategories_AfterUpdate()

' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" tblProducts WHERE CategoryID = " &
Me.cboCategories & _
" ORDER BY ProductName"

Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

****
Me.cboCategories is giving the right value
Me.choProducts.RowSource is giving the right query.
But the recordsource is not being updated.
Me.choProducts.ItemData(0) is null

Any Ideals?

You are trying to set first available value in second combo box using
ProductName instead ProductID. Correct code is (you need to include
ProductID):

Me.cboProducts.RowSource = "SELECT ProductID, ProductName FROM" & _
" tblProducts WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"

The set for second combo box

ColumnCount: 2
ColumnWidths: 0;1"

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[KBDB]

I tryed it your way and I receive this run time error message:

the record source "SELECT KeyType, Verbiage FROM tblMachineType WHERE
Keyword1 = HIC specified on this form does not exsist

KeyType is my Key for table Machinetype. Verbiage is the field I am trying
to display in Combo Box 2. Keyword1 is a field in MachineType that holds one
value that matches from table Keywords with only one field named Keys. My
combo box 1
is for table keywords

Combo Box 1 Row Source
SELECT [Keywords].[Keys] FROM Keywords;

Count column 1 & Width 1"

Private Sub Combo0_AfterUpdate()
Me.Combo2.RowSource = "SELECT KeyType, Verbiage FROM" & _
" tblMachineType WHERE keyword1 = " & Me.Combo0
End Sub

Count Column 2 and Width 0";1"

I just don't know what I'm doing wrong..

Please Help

-Kathleen

 

[Ken Sheridan]

Kathleen:

There is another way you can do this without the need for much VBA code at
all. In the properties sheet of Combo2 set its RowSource property so that it
references Combo0:

SELECT KeyType, Verbiage FROM tblMachineType WHERE keyword1 = Form!Combo0
ORDER BY Verbiage;

Note how the Form property is used here to refer to the current form without
having to explicitly reference it.

I've added an ORDER BY clause here to sort the drop down list, but you can
omit it if you want it unsorted.

Then in Combo0's AfterUpdate event procedure all you need to do is requery
Combo2 and select its first item (if that's what you want):

' requery the second combo box
Me.Combo2.Requery
' select first item in its list
Me.Combo2 = Me.Combo2.Itemdata(0)

An alternative to selecting the first item from Combo2's list would be to
drop down the list automatically so the user can select an item:

' requery the second combo box
Me.Combo2.Requery
' move focus to Comb02
Me.Combo2.SetFocus
' drop down its list
Me.Combo2.DropDown

Ken Sheridan
Stafford, England

I tryed it your way and I receive this run time error message:

the record source "SELECT KeyType, Verbiage FROM tblMachineType WHERE
Keyword1 = HIC specified on this form does not exsist

KeyType is my Key for table Machinetype. Verbiage is the field I am trying
to display in Combo Box 2. Keyword1 is a field in MachineType that holds one
value that matches from table Keywords with only one field named Keys. My
combo box 1
is for table keywords

Combo Box 1 Row Source
SELECT [Keywords].[Keys] FROM Keywords;

Count column 1 & Width 1"

Private Sub Combo0_AfterUpdate()
Me.Combo2.RowSource = "SELECT KeyType, Verbiage FROM" & _
" tblMachineType WHERE keyword1 = " & Me.Combo0
End Sub

Count Column 2 and Width 0";1"

I just don't know what I'm doing wrong..

Please Help

-Kathleen

You are trying to set first available value in second combo box using
ProductName instead ProductID. Correct code is (you need to include
ProductID):

Me.cboProducts.RowSource = "SELECT ProductID, ProductName FROM" & _
" tblProducts WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"

The set for second combo box

ColumnCount: 2
ColumnWidths: 0;1"

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[banem2]

Combo Box 1 Row Source

SELECT [Keywords].[Keys] FROM Keywords;

As I have said - first combo box needs to contain ID field, the one
which is linked on table level with other table. You could not filter
second table by text entry from first combo box, you can do it only by
ID field.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[KBDB]

Thank you so very much!

That worked very well!

Kathleen

Kathleen:

There is another way you can do this without the need for much VBA code at
all. In the properties sheet of Combo2 set its RowSource property so that it
references Combo0:

SELECT KeyType, Verbiage FROM tblMachineType WHERE keyword1 = Form!Combo0
ORDER BY Verbiage;

Note how the Form property is used here to refer to the current form without
having to explicitly reference it.

I've added an ORDER BY clause here to sort the drop down list, but you can
omit it if you want it unsorted.

Then in Combo0's AfterUpdate event procedure all you need to do is requery
Combo2 and select its first item (if that's what you want):

' requery the second combo box
Me.Combo2.Requery
' select first item in its list
Me.Combo2 = Me.Combo2.Itemdata(0)

An alternative to selecting the first item from Combo2's list would be to
drop down the list automatically so the user can select an item:

' requery the second combo box
Me.Combo2.Requery
' move focus to Comb02
Me.Combo2.SetFocus
' drop down its list
Me.Combo2.DropDown

Ken Sheridan
Stafford, England

I tryed it your way and I receive this run time error message:

the record source "SELECT KeyType, Verbiage FROM tblMachineType WHERE
Keyword1 = HIC specified on this form does not exsist

KeyType is my Key for table Machinetype. Verbiage is the field I am trying
to display in Combo Box 2. Keyword1 is a field in MachineType that holds one
value that matches from table Keywords with only one field named Keys. My
combo box 1
is for table keywords

Combo Box 1 Row Source
SELECT [Keywords].[Keys] FROM Keywords;

Count column 1 & Width 1"

Private Sub Combo0_AfterUpdate()
Me.Combo2.RowSource = "SELECT KeyType, Verbiage FROM" & _
" tblMachineType WHERE keyword1 = " & Me.Combo0
End Sub

Count Column 2 and Width 0";1"

I just don't know what I'm doing wrong..

Please Help

-Kathleen

KBDB wrote:
from the Help files........
In this example, the second combo box is filled with the results of an SQL
statement. This SQL statement finds all the products that have a CategoryID
that matches the category selected in the first combo box.

Whenever a category is selected in the first combo box, its AfterUpdate
event procedure sets the second combo box's RowSourceType property. This
refreshes the list of available products in the second combo box. Without
this procedure, the contents of the second combo box would not change.

Private Sub cboCategories_AfterUpdate()

' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" tblProducts WHERE CategoryID = " &
Me.cboCategories & _
" ORDER BY ProductName"

Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

****
Me.cboCategories is giving the right value
Me.choProducts.RowSource is giving the right query.
But the recordsource is not being updated.
Me.choProducts.ItemData(0) is null

Any Ideals?


You are trying to set first available value in second combo box using
ProductName instead ProductID. Correct code is (you need to include
ProductID):

Me.cboProducts.RowSource = "SELECT ProductID, ProductName FROM" & _
" tblProducts WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"

The set for second combo box

ColumnCount: 2
ColumnWidths: 0;1"

Regards,
Branislav Mihaljev, Microsoft Access MVP