Access 07 ComboBox Filter results category subcategory type SubType

[gcoaster]

Hello Everyone!

I have a question regarding "cascading combobox(s) list(s)"

I would like:

ComboBox 2 to show results from ComboBox 1's selection,
then ComboBox 4 to show results from ComboBox 3's choice

For Instance these are the combo boxes
[category] [subcategory] [type] [subtype]

This is the sample of choices in the combo boxes
HARDWARE - CABLES - USB - 2.0
HARDWARE - CPU - AMD - AM2

QUESTION(s)

Do I create ONE table for this? OR
Do I create 4 tables = tblcategory tblsubcategory tbltype tblsubtype ?
Do I place a QUERY in the middle of the TABLE(s) and the FORM?

Should I use VBA to make this work?
Should I use sql in the control source to make this work?

What is the best solution? so many answers, so many posts,..
a lookup in a table field.

thank you in advanced!!!

Matt

[FishVal]

Hi, Matt.

So basically you want a tree with "one parent has many childs". You need one tables for this.

 tblCategories
keyCategoryID          Autonumber(Long)    PK
txtCategoryName        Text
keyParentCategory      FK(tblCategories.keyCategoryID)
 

And take a look at Cascading Combo/List Boxes

[gcoaster]

Hello,
thank you for your reply FishVal, I appreciate it.
that article you gave me is excellent.. I found it yesterday.

HOWEVER it deals with 2 comboboxes
Company and Employees

I have 4

category subcategory type subtype

and I really don't know how to make this VBA code go past 2 columns, it only deals with 2.. Company and Employees
would I add another ELSE section? or just make another module for type!?! i think I am getting it!

Private Sub Company_AfterUpdate()
        With Me![category]
          If IsNull(Me!Company) Then
            .RowSource = ""
          Else
            .RowSource = "SELECT [EmpName] " & _
                         "FROM TblEmployees " &  _
                         "WHERE [ID]=" & Me!Company
          End If
          Call .Requery
        End With
    End Sub

[gcoaster]

And I almost didn't survive until i found this!!!! PRAISE THE LORD!!!

Support WebCast: Microsoft Access: Combo Box Survival Guide
http://support.microsoft.com/default.aspx?kbid=324585

[FishVal]

Hi, there. I really don't understand your difficulties.
Place the folllowing code in AfterUpdate event handlers of each combo but the last one. Assumed you are going with table structure I suggested in post#2 and combos are 2-column (keyCategoryID, txtCategoryName ), the first is bound and invisible (column width is set to 0).

Private Sub <level N combo>_AfterUpdate()
        With Me![<level N+1 combo>]
          If IsNull(Me![<level N combo>]) Then
            .RowSource = ""
          Else
            .RowSource = "SELECT  keyCategoryID, txtCategoryName " & _
                         "FROM tblCategories " &  _
                         "WHERE keyParentCategory=" & Me![<level N combo>] & ";"
          End If
          Call .Requery
        End With
    End Sub
 

Other options:

• you have 1 combo which changes it's own RowSource in AfterUpdate to walk down the tree; this case you should think how you want to walk up level one or/and to the topmost categories level
• you use treeview control to browse through categories

[gcoaster]

Hello FishVal,
thank you for your time.

the table structure you suggested in post#2 is for ONE TABLE.
and that is a table called tblCategories, there is no mention of another table.

the table has 3 columns

1 - keyCategoryID Autonumber(Long ) PK
2 - txtCategoryName Text HARDWARE SOFTWARE
3 - keyParentCatego ry FK(tblCategorie s.keyCategoryID )

2 would hold HARDWARE SOFTWARE

where would CPU, CD, CABLES go?
in the Foreign Key column? I dont think so? really?

[FishVal]

Hi, gcoaster.

keyCategoryID   txtCategoryName  keyParentCategory
1                 HARDWARE        Null
2                 SOFTWARE        Null
3                 CPU             1
4                 CD              1
5                 CABLES          1
6                 Office 2007     2
7                 USB             5
8                 USB 2.0         7
 

keyCategoryID - inique category key
txtCategoryName - name of category

so far the categories list is flat, to split it to levels add

keyParentCatego ry - =keyCategoryID of the parent category, to make it more representative in table view add lookup feature to the field

Retrieve to the topmost combo records where keyParentCatego ry Is Null.

Still doesn't make you sense?

[gcoaster]

AH HA!!! now I get it! I took one look at it and now I totally understand..
I am visual.. I had to see an example of it to understand. +

however I dont understand what you mean by flat... when you mention
"so far the categories list is flat,"
AND
" to make it more representative in table view add lookup feature to the field"

[FishVal]

AH HA!!! now I get it! I took one look at it and now I totally understand..
I am visual.. I had to see an example of it to understand.

Thats good.

however I dont understand what you mean by flat... when you mention
"so far the categories list is flat,"

I mean that with the first two fields you get table where all categories are equal, no levels defined. The third field - [keyParentCatego ry] adds levels.

AND
" to make it more representative in table view add lookup feature to the field"

Make [keyParentCatego ry] as Number/Long field, then go to "Lookup" tab and set

• Display Control = Combo Box
• Row Source Type = Table/Query
• Row Source = SELECT tblCategories.* FROM tblCategories;
• Bound Column = 1
• Column Count = 2
• Column Widths = 0";2"
• the rest as you like

this will transform [keyParentCatego ry] field in table view from textbox to combobox, the drawback is that having added new records you'll need to reopen the table to require the combo.

You may stay without lookup feature and write numbers to [keyParentCatego ry] field, this case no need in table reopening.

Or you may design form where combo rowsource will be required automatically.

[NeoPa]

I don't mean to disturb the conversation here, but there is another article (Example Filtering on a Form.) that may also help. It goes through adding multiple ComboBoxes to filter the data as per the selection.
Please ignore if this would only complicate matters, but you may find it helpful.