ACC: How to Fill a List Box with Database Object Names

ID: Q124344

The information in this article applies to:

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use Data Access Objects (DAO) to fill a list box or a combo box with the names of database objects. The following two sample user-defined functions are used:

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following steps demonstrate how to fill a list box or combo box with database object names:

1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 

   2.0).
2. Create a blank new form called frmFillListBox.

3. Add the following two list box controls to the form: 

      Name: ListBox1
      ------------------------------------------------------------------
      RowSourceType: Value List
      RowSource: 1;Tables;2;Queries;3;Forms;4;Reports;5;Macros;6;Modules
      ColumnCount: 2
      ColumnWidths: 0.25 in;0.75 in
      BoundColumn: 2

      Name: ListBox2
      ---------------------------
      RowSourceType: FillNameList
4. Set the AfterUpdate property of ListBox1 control to the following event 
   procedure:

      Private Sub ListBox1_AfterUpdate()

         ListBox2.Requery

      End Sub
5. Create a new module called ListBoxes, and enter the following sample 
   code in the module. Note that the FillNameList() function requires
   that two variables be declared in the module's Declarations section
   so that data can be shared with the GetNames() function. The GetNames()
   function requires an object type and an array name argument. The
   function fills the array with the database object names and returns the
   number of rows in the array. Allowable object names are Tables, Query,
   Forms, Reports, Macros, and Modules.

    '**********************************
    '  Declarations section of the module
    '**********************************

    Option Compare Database    ' Use database order for
                               ' string comparisons.
    Option Explicit

    Dim list() As String
    Dim entries

    '**************************************
    'Function FillNameList()
    '**************************************

    Function FillNameList (fld As Control, id, row, col, code)
    ' Accepts a control, an identifier, a row, a column, and a code.

    On Error GoTo ErrorHandler

    Dim ReturnVal
    Dim x As String

    If IsNull(Forms![frmFillListBox]![ListBox1]) Then
     x = "Tables"
    Else
     x = Forms![frmFillListBox]![ListBox1]
    End If

    ReturnVal = Null
    Select Case code
       Case 0                  ' Initialize.
            entries = 0
            entries = GetNames(x, list())
            ReturnVal = entries
       Case 1                  ' Open.
            ReturnVal = Timer  ' Unique ID number for the control.
       Case 3                  ' Get the number of rows.
            ReturnVal = entries
       Case 4                  ' Get the number of columns.
            ReturnVal = 1
       Case 5                  ' Get the column width.
            ReturnVal = -1     ' Use the default width.
       Case 6                  ' Get the data.
            ReturnVal = list(row)
       Case 9                  ' End.
            ReDim list(0)
            entries = 0
    End Select
    FillNameList = ReturnVal

    ErrorHandler:
    Resume Next

    End Function

    '*********************
    'Function GetNames()
    '*********************

    Function GetNames (objtype As String, names() As String)
    Dim Conta As Container, Db As Database, I, Arlen

    Set Db = CurrentDb

    ' In Microsoft Access 7.0 and earlier, the above line should read:
    '
    '          Set Db = DbEngine.Workspaces(0).Databases(0)

    Arlen = 0

    If objtype = "Macros" Then
        objtype = "Scripts"   ' Macros are called scripts, internally.
    End If

    Select Case objtype
       Case "Tables"
          If Db.Tabledefs.Count <> 0 Then
          Arlen = Db.Tabledefs.Count
          ReDim list(0 To Arlen - 1)
          I = 0
              For I = 0 To (Arlen) - 1
                  names(i) = Db.Tabledefs(i).Name
              Next I
          End If
            Case "Queries"
               If Db.Querydefs.Count <> 0 Then
                  Arlen = Db.Querydefs.Count
                  ReDim list(0 To Arlen - 1)
                  I = 0
                  For I = 0 To (Arlen) - 1
                     names(i) = Db.Querydefs(i).Name
                  Next i
               End If
            Case Else
               Set Conta = Db.Containers(objtype)
                  If Conta.Documents.Count <> 0 Then
                     Arlen = Conta.Documents.Count
                     ReDim list(0 To Conta.Documents.Count - 1)
                     I = 0
                     For I = 0 To (Arlen) - 1   ' Fill the Names array
                                                ' with object names.
                        names(i) = Conta.Documents(i).Name
                     Next I
                  End If
            End Select
         Getnames = Arlen         ' Return the length of the array to the
                                  ' FillNameList() function.
    End Function
6. View frmFillListBox in Form view, and select an object type in ListBox1. 
   ListBox2 displays the names of objects of the type selected in
   ListBox1.
Additional query words: list function 
Keywords          : kbusage FmsCmbo PgmObj 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998