Combo box list

[BaoBao]

Good day, all

l have 2 table to store the following information :
Table 1: item_list
item_no item_name
1001 Color Book
1002 Computer
1003 Mobile phone

Table 2: subitem_list

subitem_no item_no subitem_name
2001 1001 Microsoft Access 2003
2002 1001 Microsoft Excel 2003
2003 1001 Microsoft Word 2003
2004 1002 Lenovo T61
2005 1002 Compaq B1800
2006 1002 NEC laptop

How can l do if l need to create 2 combo box, combo box 1 will show
item_name then the combo box 2 will show subitem_name? l have try to create a
query (link item_no) but it's not works in my form.

Thank you very much!
BaoBao

 

[Steve Sanford]

This is called cascading combo boxes.

Create two combo boxes.
Rename one combo box to "cboItemList" (no quotes)
Rename the other one to "cboSubItemList" (no quotes)

Open the properties for "cboItemList".
On the DATA tab, set these properties:

ROW SOURCE :

SELECT Item_list.item_no, Item_list.item_name FROM Item_list ORDER BY
Item_list.item_name;

BOUND COLUMN : 1

On the FORMAT tab, set these properties:

COLUMN COUNT: 2
COLUMN WIDTHS: 0



Now select the "cboSubItemList" property dialog box and set these properties:

ROW SOURCE :

SELECT Subitem_list.subitem_no, Subitem_list.item_no,
Subitem_list.subitem_name FROM Subitem_list WHERE Subitem_list.item_no
=[Forms]![YOUR_FORM_NAME].[cboItemList];

Note: in the above line, change "YOUR_FORM_NAME" to the name of your form.

BOUND COLUMN : 1

On the FORMAT tab, set these properties:

COLUMN COUNT: 3
COLUMN WIDTHS: 0";0";2"


The last thing to do is add some code to the afterupdate event of the
"cboItemList" combo box.

Open the properties for "cboItemList". Click on the "EVENT" tab. click in
the line for "After Update". Using the dropdown, select [Event Procedure].
Then click on the three dots on the right.

The code to add is:

Me.cboSubItemList.Requery
Me.cboSubItemList = ""


so the procedure looks like

Private Sub cboItemList_AfterUpdate()
Me.cboSubItemList.Requery
Me.cboSubItemList = ""
End Sub



Save the form and test the combo boxes.

HTH