David Nishimoto
davepamn@relia.net
How to Load a Category Tree and Listbox RowSource Type 
"field value" property
Overview: In this Access 2000 article, I will show you how to
create a category tree and load it into a listbox.
In this article, I will demonstrate the steps to create 
a static value list and associate it with a listbox.
The algorithm can be easily ported to a treeview control 
or html for an active server page.

Creating the Category table

1. Create a table called "category"
2. Include the following fields:
	categoryid: autonumber
	parentid: number
	title: text
3. Add the following data values to the category table


Parent Id
Category Id (autonumber)
Title


0
1
Hardware


0
2
Computers


2
3
DeskTop


2
4
Server


Add a Listbox
1. Add a listbox to your form called "lbxCategory"
2. Insert the following VB code to the form

1. Static values can be added to a listbox
2. Each field value is separated with a ";" delimiter
3. Rows are determined by the listboxes columncount
4. When the ColumnHeads property is set to true the
first row becomes the column headings.
5. The RowSourceType settings tell the listbox
control whether the data is dynamically bound to a data table
or static text. In this case, the listbox control is bound to static
text.

Option Explicit
Option Compare Database
Dim sFieldValues As String
Private Sub Form_Load()
'Heading Column titles
sFieldValues = "Parent Id;Category Id, Title;"
Call LoadCategory(0)
lbxCategory.RowSourceType = "Value List"
lbxCategory.RowSource = sFieldValues
lbxCategory.ColumnCount = 3
lbxCategory.ColumnHeads = True
End Sub

1. The Load Category procedure start with the root parent id being "0".
2. Each category is recursively checked, to see, if it has children. 
Children are concatenated to the sFieldValues string.
3. Each value list entry concatenated to the sFieldValue string
embedding the parentid, categoryid, and title information.

Private Sub LoadCategory(sId)
Dim rs As Object
Dim sql
Dim sNewId
'Check for the bottom of the tree
If IsNull(sId) Then
Exit Sub
End If

sql = "select * from category where parentid=" & sId
Set rs = CurrentDb().OpenRecordset(sql)
Do While Not rs.EOF
sFieldValues = sFieldValues & sId & ";" & rs("categoryid") & ";" & rs("title") & ";"
'Recursive call to check for children
Call LoadCategory(rs("categoryid"))
rs.MoveNext
Loop
If Not rs Is Nothing Then
rs.Close
End If

Set rs = Nothing
End Sub