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

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

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

• The GetNames() function. This function fills an array with object names from the active database.
• The FillNameList() function. This function calls the GetNames() function and fills the list box or combo box.

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. Place the following two list box controls on 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. Place an event procedure in the AfterUpdate event of ListBox1:

         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.