Link to home
Start Free TrialLog in
Avatar of seba_k
seba_k

asked on

Dropdown list with Category & subcategories out of the same table

Hey,

I'm quite new to access, so this might be a stupid question, or just impossible.

But here I go:
I have a table with categories that looks like:
-catid
-parentcatid
-name

Parentcatid is also a catid and the current category is a subcategory of the parent category.
That way i can have unlimited subcategories.

However i want to have a dropdown list on a form with all categories, and the more a category is nested, the more it is indented.

So like:

Category
subcategory
subsubcategory
subsubcategory
subsubsubcategory
category
subcategory
...


A bit like the 'forum jump' select menu on this forum.

Any idea how to do this?

Thanks in advance,
Avatar of wiswalld
wiswalld
Flag of United States of America image

When you add the combobox to the form set its data source to your table. Make the column count equal to how many fields you have and set the column widths to something like 1,1,1,1

Is this what you are looking for?
That's a classical hierarchy structure, which requires a recursive query and that is not supported by Access.  You have a couple of options:

Write a recursive procedure to build a temp table and then bind that to your dropdown.

-or-

Modify your data structure to represent your hierarchy a different way.  


Avatar of seba_k
seba_k

ASKER

@wiswalld, no that doesn't give me the correct result

also, a correction of my question: i copied this question from a forum where i also asked it.
The example should be:
Category
subcategory
  subsubcategory
    subsubcategory
      subsubsubcategory
    subsubcategory
category
  subcategory
  subcategory
...

@dgmg: as i'm a beginner it's hard to write a recursive procedure (especially with as good as non vb experience)

But i found another solution here for somewhat the same, but in a treeview, and there they quoted this code, which maybe someone could modify to work for a dropdown.

this is the question:
https://www.experts-exchange.com/questions/21668116/How-to-Create-Tree-View-Category-Structure-to-Match-Non-Standard-Database-Tables.html?sfQueryTermInfo=1+categori+same+subcategori+tabl

and this is the code snipped i was talking about


Sub AddChildren(nodBoss As Node, rst As DAO.Recordset)
 Dim nd As Node, StrText As String, bkMark As String, FoldType As Integer, i As Integer
 
 rst.FindFirst "[GroupName] ='" & nodBoss & "'"
 
Do Until rst.NoMatch
 StrText = rst![NodeName]
 FoldType = rst!FoldType
 Set nd = TreeView1.Nodes.Add(nodBoss, tvwChild, StrText, StrText, FoldType)
 nd.Tag = StrText
 bkMark = rst.Bookmark
 i = i + 1
 AddChildren nd, rst        'CALLING ITSELF
 rst.Bookmark = bkMark
 rst.FindNext "[GroupName] ='" & nodBoss & "'"
Loop
 
End Sub
 
Private Sub Form_Load()
  Dim db As Database, rst As Recordset, nd As Node
  Dim StrText As String, bkMark As String, FoldType As Integer
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset("categorie", dbOpenDynaset, dbReadOnly)
  rst.FindFirst "GroupName Is Null"
 
  Do Until rst.NoMatch
    StrText = rst![NodeName]
    FoldType = rst!FoldType
    Set nd = TreeView1.Nodes.Add(, , StrText, StrText, FoldType)
    nd.Tag = StrText
   
    bkMark = rst.Bookmark
    AddChildren nd, rst
    rst.Bookmark = bkMark
    rst.FindNext "GroupName Is Null"
  Loop
    TreeView1.Nodes(1).Expanded = True
    TreeView1.Nodes(1).Image = 2
   
    rst.Close
    Set db = Nothing
    AddList TreeView1.Nodes(1)
       
End Sub

Open in new window

That is a recursive procedure: note the comment "calling itself".  See if this gets you jumpstarted.  Don't have time to recode it for you, but what it should do is this:

'process the categories
clear the rowsource of the combo box
call the procedure passing hierarchy level=0, and catid=0 as arguments
 
'inside the prodedure
if level = 0
  open a new record set to find all the categories
else
  open a new recordset to find all the subcategories for the passed catid

loop through the recordset, for each category found
   concatentate the category name to your combobox rowsource, using space(level) to indent according to the hierarchy level
   call the procedure again passing level + 1 and the catid of the category just added to the rowsource.
 
close the recordset  


Avatar of seba_k

ASKER

hey,

well you actually explained just the things i already knew and not the parts i don't know.
Especially these 2 things I don't find:

I have some program experience in other languages (java), so I get what the code does and what it should do, but I don't know the vb/access specific parts.
how do i open a recordset in vb? Is this just the name of a query? can i set parameters for that query (like the parentid)

How do I concatenate something to a rowsource of a combobox?

Than I think I have enough information to make this.

Thanks,
Seba
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of seba_k

ASKER

Thanks, i'll accept it as a good answer, but I still don't have it figured out completely, maybe you can still have a look at my last post.
Thanks,
seba
Avatar of seba_k

ASKER

thanks,

however the combobox is still empty :(.

i have a combobox named: 'categorie'

and this code:
Option Compare Database
Sub AddChildren(catid As Integer, rst As DAO.Recordset, level As Integer)
    Dim StrText As String, bkMark As String, i As Integer, d As Integer
    rst.FindFirst "[parentid] ='" & catid & "'"
    
    Do Until rst.NoMatch
        Dim d As Integer
        StrText = ""
        For d = 0 To level
            StrText = StrText & " "
        Next d
        StrText = StrText & rst![naam]
        categorie.RowSource = Nz(categorie.RowSource & ";", categorie.RowSource) & StrText
        bkMark = rst.Bookmark
        i = i + 1
        level = level + 1
        AddChildren rst.catid, rst, level        'CALLING ITSELF
        rst.Bookmark = bkMark
        rst.FindNext "[parentid] ='" & catid & "'"
    Loop
 
End Sub
 
Private Sub Form_Load()
    Dim db As Database, rst As Recordset
    Dim StrText As String, bkMark As String, FoldType As Integer
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT catid,parentid,naam FROM categorie ORDER BY parentid ASC", dbOpenDynaset, dbReadOnly)
 
    categorie.RowSource = Null
    AddChildren 0, rst, 0
 
    rst.Close
    Set db = Nothing
End Sub

Open in new window