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

ID: Q124344


The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97


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:

  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 : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: October 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.