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

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:

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.

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:

Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0).
Create a blank new form called frmFillListBox.

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

Set the AfterUpdate property of ListBox1 control to the following event procedure:

      Private Sub ListBox1_AfterUpdate()

         ListBox2.Requery

      End Sub

On the Tools menu, click References. Then, scroll down to the "Microsoft DAO 3.5 Object Library" and select it. The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.5 Object Library.

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

View frmFillListBox in Form view, and select an object type in ListBox1. ListBox2 displays the names of objects of the type selected in ListBox1.