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 - keyParentCategory FK(tblCategories.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

keyParentCategory - =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 keyParentCategory 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 - [keyParentCategory] adds levels.

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

Make [keyParentCategory] 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 [keyParentCategory] 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 [keyParentCategory] 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.

[gcoaster]

FishVal, you rock..
I am going to print this out, go to the coffee house.. come back and try it!
thank you FishVal.
you ever on the gold coast of Australia let me know!

M@

[gcoaster]

NeoPal, thank you! every little bit counts..

one comment, that is some SERIOUS VBA!!!!

being the newbie I am, I was getting errors with what i copied and pasted from FishVAl and at 2am in the morning i was just ready to just use a WAMP in a VMWARE machine and switch over to MySQL using MyPhpAdmin and php w/ XTHML + CSS with a pretty web2.0 interface and maybe some spry.. using dreamweaver and Adobe Dreamweaver Developer Toolbox,

( yea that sounds complex but i am finding the comboboxes in access hurt my brain more! HAHA )

I CANNOT imagine debugging the fallowing until i learn the basics.

Option Compare Database
Option Explicit
 
Private Sub txtFindAccountCode_AfterUpdate()
    Call CheckFilter
End Sub
 
Private Sub txtFindCreationDate_AfterUpdate()
    Me!txtFindCreationDate = IIf(IsDate(Me!txtFindCreationDate), _
                                 Format(Me!txtFindCreationDate, "d mmm yyyy"), _
                                 "")
    Call CheckFilter
End Sub
 
Private Sub cboFindAccountType_AfterUpdate()
    Call CheckFilter
End Sub
 
'CheckFilter produces the new Filter depending on the values currently in
'txtFindAccountCode, txtFindCreationDate & cboFindAccountType.
Private Sub CheckFilter()
    Dim strFilter As String, strOldFilter As String
 
    strOldFilter = Me.Filter
    'txtFindAccountCode - Text
    If Me!txtFindAccountCode > "" Then _
        strFilter = strFilter & _
                    " AND ([AccountCode] Like '" & _
                    Me!txtFindAccountCode & "*')"
    'txtFindCreationDate - Date
    If Me!txtFindCreationDate > "" Then _
        strFilter = strFilter & _
                    " AND ([CreationDate]=" & _
                    Format(CDate(Me!txtFindCreationDate), _
                           "\#m/d/yyyy\#") & ")"
    'cboFindAccountType - Numeric
    If Me!cboFindAccountType > "" Then _
        strFilter = strFilter & _
                    " AND ([AccountType]=" & _
                    Me!cboFindAccountType & ")"
    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub

[NeoPa]

Gold,

I suggest you put your actual project aside for a bit while you progress through this article. It's a bit like a tutorial and includes the database to download.
If you have trouble with it or specific questions, I can help. I expect once you start to go through it it will start to make better sense.

[gcoaster]

Hello NeoPa,
thank you for offering to help. You are a scholar and a gentleman

It doesn't look that bad, I just may read and study.. just noticed like in CSS the
lines that start with a ' is a comment */ comment */ so there is not as much code as i thought.
One thing, It is 2007, things are going FAST.. guys like me DONT HAVE TIME, especially trying to debug code and it ends up being a comment out of place. then you have to search for help on the obscure ms error message.

Trying to stay ahead of the game is almost information overload, you miss a beat and your behind, I have learned to KEEP IT SIMPLE
you get IN and you get OUT.

I could and actually will spend another 2 days figuring this out, then I get it done.. enter data and don't dive into the code again till i want to customize it or something isn't right..

this is one reason allot of people use FileMaker, they get frustrated with access and filemaker you get in and get out. but once your in your in!

by the way, i did have my doubts with going with access 07, there is not a access 2007 export to MySQL yet, but you can always make table etc etc.

anyways, thank you all!

Matt

[gcoaster]

Hello FishVal,

I need your help please, I think I am close.
I keep getting an error.. here is the code

Option Compare Database
 
Private Sub cmboMainCategory_AfterUpdate()
    With Me!cmboSubCategory
        If IsNull(Me!cmboMainCategory) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT keyCategoryID,txtCategoryName" & _
                 "FROM @CATEGORY" & _
                 "WHERE keyParentCategory=" & Me!cmboMainCategory & ";"
            End If
            Call .Requery
    End With
End Sub

-- what am i doing wrong?! thank you

[FishVal]

Hello FishVal,

I need your help please, I think I am close.
I keep getting an error.. here is the code

Option Compare Database
 
Private Sub cmboMainCategory_AfterUpdate()
    With Me!cmboSubCategory
        If IsNull(Me!cmboMainCategory) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT keyCategoryID,txtCategoryName" & _
                 "FROM @CATEGORY" & _
                 "WHERE keyParentCategory=" & Me!cmboMainCategory & ";"
            End If
            Call .Requery
    End With
End Sub
 

-- what am i doing wrong?! thank you

Hi, Matt.

The code seems to be Ok.

• what error are you getting? where code execution stops?
• what are the settings of [cmboMainCategory] and [cmboSubCategory] properties ?
  • ColumnCount
  • BoundColumn
  • RowSource
• did you try to execute RowSource statement in query builder?

[gcoaster]

Hello FishVal

Here is what I have

Table
@CATEGORY

Columns in @CATEGORY Table
mainCategoryID
name
keyCategoryID

Combo Boxes
cmboMainCategory
cmboSubCategory

Properties of Combo Boxes
    cmboSubCategory
        Control Source       | softCategory ( tried nothing as well )
        Row Source            | @CATEGORY
        Bound Column       | tried everything 1 2 3 
        Count                       | 2
        Widths                     | 0cm;1cm 
 
    cmboSubCategory
        Control Source       | softSubCategory ( tried nothing as well )
        Row Source            | @CATEGORY
        Bound Column       | tried everything 1 2 3 
        Count                       | 2
        Widths                     | 0cm;1cm

Private Sub cmboMainCategory_AfterUpdate()
    With Me![cmboSubCategory]
        If IsNull(Me!cmboCategory) Then
            .RowSource = ""
        Else
           .RowSource = "SELECT MaincategoryID, name" & _
                        "FROM @CATEGORY " & _
                        "WHERE [keyCategoryID]=" & Me!cmboMainCategory
        End If
        Call .Requery
    End With
End Sub

Matts hair… -handfull per 2 errors and no results

Question: If I ever get this working.. then how do I add the next 2?
Type and then Sub Type?
thank you FishVal

[FishVal]

Hi, Matt.

See the attached example.

[NeoPa]

Hello FishVal,

I need your help please, I think I am close.
I keep getting an error.. here is the code

Option Compare Database
 
Private Sub cmboMainCategory_AfterUpdate()
    With Me!cmboSubCategory
        If IsNull(Me!cmboMainCategory) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT keyCategoryID,txtCategoryName" & _
                 "FROM @CATEGORY" & _
                 "WHERE keyParentCategory=" & Me!cmboMainCategory & ";"
            End If
            Call .Requery
    End With
End Sub

-- what am i doing wrong?! thank you

No space before the FROM (or the WHERE for that matter).

@Gold Please notice that two of your posts now have the edit comment (Please use [code] tags).
This is because everything works so much better when you can read what's being posted. I'm sure Fish would have seen the missing space if it had been laid out clearly.

*edit* I just noticed a later post indicating you're obviously trying. Look at the (#) button when creating or editing your post and read the text at the right on the "New Post" page for hints :)

[NeoPa]

If you examine the .RowSource property after it's been set up - you'll easily see what I mean ;)

[gcoaster]

Hi, Matt.

See the attached example.

Thank you FishVal

the database you uploaded, DOES NOT have any vba..
After update just has a =RequireDescCombo(2) are you recommending not using a module?

and if this is in a form.. can the Control Source be appended to a column in a table?

[gcoaster]

Hello NeoPa,
ROW SOURCE = "nothing"

If you examine the .RowSource property after it's been set up - you'll easily see what I mean ;)

[gcoaster]

FishVal, NeoPa..

I got it to work, HOW EVER..

when the data is selected and posted to control source/table

Numbers are entered, instead of text!

LEVEL1 LEVE2 LEVEL3 LEVEL4
1 2 3 4

How do I get it to show, or post as, or enter as text?

LEVEL1 LEVE2 LEVEL3 LEVEL4
Software Micro$0ft Access 2003 PAIN IN THE

ALMOST!

[NeoPa]

Hello NeoPa,
ROW SOURCE = "nothing"

Looking at your code from post #17.
Let line #6 run (so Me!cmboCategory NOT null) then look at the contents of the .RowSource control.

[gcoaster]

Looking at your code from post #17.
Let line #6 run (so Me!cmboCategory NOT null) then look at the contents of the .RowSource control.

So Line 6

   .RowSource = "SELECT MaincategoryID, name" & _

Should Now Be

           .RowSource = "SELECT MaincategoryID, name" & _ so Me!cmboCategory NOT null
 

?!?!?

[NeoPa]

So Line 6

.RowSource = "SELECT MaincategoryID, name" & _

Should Now Be

.RowSource = "SELECT MaincategoryID, name" & _ so Me!cmboCategory NOT null

?!?!?

No. Not at all.
Add the following line into your code after line #8 (Lines #6 to #8 are actually all the same line in VBA)

Call MsgBox(.RowSource)

Then look at the SQL as it's shown to see clearly why your code is not quite right (Again Let line #6 run (so Me!cmboCategory NOT null)).

[gcoaster]

NeoPa, I am Almost there.

All I need now is to figure out how to get DLookUp to write to a field!

=DLookUp("[model]","qPRODrest")
=DLookUp("[make]","qPRODrest")
=DLookUp("[description]","qPRODrest")
=DLookUp("[UnitPrice]","qPRODrest")
=DLookUp("[purchasedPrice]","qPRODrest")

Last Step I used DLookUp to bring the data in to last leftover fields from cascade comboboxes...

I ended up using one table and 4 querys.
A little VBA as well!

Private Sub Combo0_AfterUpdate()
Me!Combo1.Requery
End Sub
 
Private Sub Combo1_AfterUpdate()
Me!Combo2.Requery
End Sub

each combobox, category/subCategory/type.. used each query!
the query's filter each other
for instance, in the subcategory query

Category | PRODUCT | Groupby |
[Forms]![INVOICE]![INVOICEsubform]![ComboCategory]

The Last query pulls in the rest and populates the left over fields, Text Boxes bound to table fields in details table..

the values are there! its working! but the values are NOT writing to the text box's!!! to the table columns!!!

I realized it when I couldn't get the grandtotal to add up! oh man! I have been working 1 week on this, micro$oft is killing me.

one last step and I am stuck!!! ahahaha i HATE ACCESS!! LOL

[NeoPa]

Please check out post #19 where it talks about using [ CODE ] tags in your posts. I've had to edit your post again and frankly I'm getting tired with tidying up after you.

Also, if you'd like me to continue working with me I'd appreciate feedback related to my posts. Explaining where you are from scratch (as you seem to have in your last post) is frustratingly difficult to follow.

How would you communicate with someone when they appear to ignore everything you try to tell them and just go on their own merry way regardless. It's not good manners and it's very difficult for me to be of any use to you.

I still don't know if you've understood my first point yet and, regardless of whether you work by jumping around from issue to issue, I know better, so don't expect me to follow you.

[gcoaster]

Ok, I am with you. I will start using the code tag with the editor

[gcoaster]

OK! I think I got it! (including putting the code in the code tags for neopa) I think I got comboboxes to filter using querys to last combobox then last combobox populates the remaining columns in table!
you have taught me well FishVal.

this solution uses hardly ANY vba and writes TEXT to record instead of ID number.

The fallowing comboboxes are cascading comboboxes and filter to comboprodname. using 5 querys.

Combocategory
Combosubcategory
Combotype
comboboxsubtype
Comboprodname

When item is selected in comboprodname combobox..
Then the remaining fields in table (which are each bound in control source)
are populated VIA vba!

The remaining text box fields on form are

Description
Make
Model
UnitPrice
purchasedPrice

So VBA in afterupdate in Comboprodnamewhich is the
LAST combobox should look something like this?

Private Sub productName_AfterUpdate()
Me.description = DLookup("[description]", "qPRODrest")
Me.make = DLookup("[make]", "qPRODrest")
Me.model = DLookup("[model]", "qPRODrest")
Me.UnitPrice = DLookup("[UnitPrice]", "qPRODrest")
Me.purchasedPrice = DLookup("[purchasedPrice]", "qPRODrest")
End Sub

wait, something is out of the ordinary.. it works!

I will post the entire project and code with comprehensible images when I am done with this here if I can and on my website,

Freely ye have received, freely give. Matt 10:8

thank you FishVal!

And NeoPa.

Matt

[NeoPa]

Ok, I am with you. I will start using the code tag with the editor

Gold,

I think I needed to say what I had to say, but I'm pleased to see that it hasn't curbed your enthusiasm.
I'm very pleased that you managed to resolve your issues (mainly with FishVal's help - He's good that chap). I was also pleased that your explanation (which is always appreciated from posters after a successful resolution) was expressed clearly and was easy to read and understand.

Please come back and post if you have further issues :)

-NeoPa.

[FishVal]

Hi, Matt.

Glad you've got it work.
Hope you had a time struggling with your code hard enough to really enjoy the moment when it got work. ;)

Best regards,
Valentine

[gcoaster]

Valentine

yes you are my Valentine! ;-)

Hello FishVal
and

NeoPa, 

you guys are great..

I got one more for you before i experience even more JOY! ( oh and there was joy when It all worked, when i closed one eye and clicked and held my breath and it worked i woke the neighbors)

Also, I understand VBA now thanks to you guys. i remember picking up the ACCESS 2007 books and seeing the VBA code i would stop and say "give me a break" I actually go back now and open the same books and understand it now! what a TRIP.

Comboxes are working great.. when I add products. and as a new database, I am finding errors and renaming categories. is there a bit of code
that I can add to the VBA that will clear the boxes AFTER?
EXAMPLE

HARDWARE | CPU | AMD | AM2 | CHEAP

lets say I want to change AMD to INTEL
when I go back and change HARDWARE in first combobox, it dynamically clears all of the fallowing combboxes?

[cboSubCat] [cboType] [cboSubType]

HARDWARE | nothing | nothing | nothing |

Also, I am not using the Querys anymore! only for the first combobox.
[cboCat]
here is the code I am using now

Private Sub cboCat_AfterUpdate()
   With Me![cboSubCat]
        If IsNull(Me!cboCat) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT DISTINCT subCategory " & _
                "FROM PRODUCT " & _
                "WHERE PRODUCT.category= '" & Me!cboCat & "' " & _
                "ORDER BY PRODUCT.subCategory;"
        End If
        Call .Requery
    End With
End Sub
Private Sub cboproductName_AfterUpdate()
Me!description = DLookup("[description]", "qPRODrest")
Me!make = DLookup("[make]", "qPRODrest")
Me!model = DLookup("[model]", "qPRODrest")
Me!UnitPrice = DLookup("[UnitPrice]", "qPRODrest")
Me!purchasedPrice = DLookup("[purchasedPrice]", "qPRODrest")
End Sub
 
 
Private Sub cboSubCat_AfterUpdate()
   With Me![cboType]
        If IsNull(Me!cboSubCat) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT DISTINCT type " & _
                "FROM PRODUCT " & _
                "WHERE PRODUCT.subCategory= '" & Me!cboSubCat & "' " & _
                "ORDER BY PRODUCT.type;"
        End If
    Call .Requery
    End With
End Sub
Private Sub cboType_AfterUpdate()
   With Me![cboSubType]
        If IsNull(Me!cboType) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT DISTINCT subType " & _
                "FROM PRODUCT " & _
                "WHERE PRODUCT.type= '" & Me!cboType & "' " & _
                "ORDER BY PRODUCT.subType;"
        End If
    Call .Requery
    End With
End Sub
Private Sub cboSubType_AfterUpdate()
   With Me![cboproductName]
        If IsNull(Me!cboSubType) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT productName " & _
                "FROM PRODUCT " & _
                "WHERE PRODUCT.subType= '" & Me!cboSubType & "' " & _
                "ORDER BY PRODUCT.productName;"
        End If
    Call .Requery
    End With
End Sub

When [productName] is selected, THEN it runs a QUERY that looks up the rest of the values filted from QUERY

VBA in [productName]

Private Sub cboproductName_AfterUpdate()
Me!description = DLookup("[description]", "qPRODrest")
Me!make = DLookup("[make]", "qPRODrest")
Me!model = DLookup("[model]", "qPRODrest")
Me!UnitPrice = DLookup("[UnitPrice]", "qPRODrest")
Me!purchasedPrice = DLookup("[purchasedPrice]", "qPRODrest")
End Sub

and here is filter in the QUERY criteria for productName

[Forms]![INVOICE]![INVOICEsubform]![cboproductName]

Thank you many many times over! I got more brat time with my 2 sons!

M@

[NeoPa]

Well Gold, It's certainly fun when you get something working and I'm glad you've had that satisfaction (and especially the extra time with your kids).
As to your combobox behaviour, every time you select an item from any of the comboboxes it will start the others (lower down the tree) again from scratch. As you can see this will happen both when the field is redone (.RowSource reset) and when a new item is selected.

-NeoPa.

[FishVal]

Data organized in a way discussed in this thread could be advantageously represented in TreeView control. See Nested Display thread.