ACC: How to Fill a Combo Box with Table Names

ID: Q126946

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a sample user-defined Visual Basic for Applications function that you can use to fill a combo box or list box with the names of all the tables in the current database.

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 create and use the sample ListAllTables() function:

1. Start Microsoft Access and open the sample database Northwind.mdb

   (or NWIND.MDB. in Microsoft Access 2.0).

2. Create a new module and enter the following code in the module.

   NOTE: In the following sample code, an underscore (_) at the end of a
   line is used as a line-continuation character. Remove the underscore
   from the end of the line when re-creating this code in Access Basic.

      Function ListAllTables (fld As Control, id As Long, row As _
      Long, col As Long, code As Integer)
         Dim db As Database
         Dim tbdf As TableDef
         Static tbls(256) As String
         Static Entries As Integer
         Dim i As Integer
         Dim ReturnVal
         ReturnVal = Null
         Select Case code
            Case LB_INITIALIZE         ' Initialize database.
               Set db = DBEngine.Workspaces(0).databases(0)
               Entries = 0
               For i = 0 To db.tabledefs.count - 1
                  tbls(Entries) = db.tabledefs(i).name
                  Entries = Entries + 1
               Next i
               ReturnVal = Entries
            Case LB_OPEN               ' Open.
               ReturnVal = Timer       ' Unique ID number for control.
            Case LB_GETROWCOUNT        ' Number of rows.
               ReturnVal = Entries
            Case LB_GETCOLUMNCOUNT     ' Number of columns.
               ReturnVal = 1
            Case LB_GETCOLUMNWIDTH     ' Column width.
               ReturnVal = -1          ' Use the default width.
            Case LB_GETVALUE           ' Get the data.
               ReturnVal = tbls(row)
            Case LB_END                ' End.
               For Entries = 0 To 256
                  tbls(Entries) = ""
               Next
         End Select
         ListAllTables = ReturnVal
      End Function

3. Create a blank new form that is not based on any table or query.

4. Add a combo box control to the form.

5. Set the RowSourceType property of the combo box to ListAllTables.

6. View the form in Form view. Note that the combo box lists all the tables

   in the current database.

Additional query words: tablenames

Keywords          : kbprg FmsHowto PgmObj 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998