Cascading ComboBoxes Access 2007 Help!! 1

[gcoaster]

Hello!
I have spent the last WEEK trying to get "cascading comboboxes" to work in access 2007. I have searched and searched and either solutions are not complete, too complex .. I don’t know what to do. Could someone please help?

I have 1 table that holds categories with a ID NAME and FK,
1 table that holds products, One FORM to enter products in. Form is BOUND to PRODUCTS TABLE

And ALL I need to do is in EACH product select for instance

SOFTWARE / FREEWARE / SYSTEM UTITIES / XP
Or if hardware is selected first…
HARDWARE / CPU / AMD / etc etc



Sample data for CATEGORY table.

categoryID
categoryName
Key
--------------------------------------------------------------------
1
HARDWARE

2
SOFTWARE

3
CPU
1
4
FREEWARE
2
5
AMD
3
6
System Utilities
4
7
Revo Uninstaller
6


TABLE SOURCE

@Categories

categoryID
Autonumber(Long) PK
categoryName
Text
Key
Number (long)


ComboBoxes Properties

4 Combo boxes on FORM
1
cmbCategory
2
cmboSubCategory
3
cmboType
4
cmboSubType

FORM
ADDPRODUCTS

COMBOBOX
Column Count
Bound Column
Column Width
Control Source
Row Source
cmbCategory
2
1
1cm;1cm
category

cmboSubCategory
2
1
1cm;1cm
subCategory

cmboType
2
1
1cm;1cm
type

cmboSubType
2
1
1cm;1cm
subType


combos are 2-column (categoryID, categoryName )
the first is bound and invisible (column width is set to 0).

VBA
-----------------------------------------------------------------------------------
Option Compare Database

Private Sub ComboCategory_AfterUpdate()
With Me!ComboSubCategory
If IsNull(Me!ComboCategory) Then
.RowSource = ""
Else
.RowSource = "SELECT [categoryID],[categoryName] " & _
"FROM @CATEGORY " & _
"WHERE [key]=" & Me!ComboCategory & ";"
End If
Call .Requery
End With
End Sub

Private Sub ComboSubCategory_AfterUpdate()
With Me!ComboType
If IsNull(Me!ComboSubCategory) Then
.RowSource = ""
Else
.RowSource = "SELECT [categoryID],[categoryName] " & _
"FROM @CATEGORY " & _
"WHERE [key]=" & Me!ComboSubCategory & ";"
End If
Call .Requery
End With
End Sub

Private Sub ComboType_AfterUpdate()
With Me!ComboSubType
If IsNull(Me!ComboType) Then
.RowSource = ""
Else
.RowSource = "SELECT [categoryID],[categoryName] " & _
"FROM @CATEGORY " & _
"WHERE [key]=" & Me!ComboType & ";"
End If
Call .Requery
End With
End Sub

 

[randy700]

Build the rowsource in each combo box. In the AfterUpdate event of "higher level" boxes, do a requery of each "lower level" box.

Example:
cboCategory.Rowsource
SELECT CategoryID, CategoryName, Key FROM tblCategory

cboHardware.Rowsource
SELECT insert fields here FROM tblHardware WHERE HardwareID = Forms!FormName!cboCategory

cboSoftware.Rowsource
SELECT insert fields here FROM tblSoftware WHERE SoftwareID = Forms!FormName!cboHardware

Private Sub cboCategory_AfterUpdate()
    cboHardware.Requery
    cboSoftware.Requery
End Sub

Private Sub cboHardware_AfterUpdate()
    cboSoftware.Requery
End Sub

Randy

 

[gcoaster]

Thank you Randy!