Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 21, 2002

Access 2000 How To's: Access Tree View and List Box Row Source

By David Nishimoto

How to Load a Category Tree and Listbox RowSource Type "Field Value" Property


In this Access 2000 article, I will show you how to create a category tree and load it into a listbox. 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 to 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 starts 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 is 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"))
    If Not rs Is Nothing Then
    End If
    Set rs = Nothing
End Sub

Active Server Page (Tree View)

  1. Generate a Category tree in HTML.

Back to Access 2000 How To's Series Home

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM