ACC: How to Fill a Combo Box with Table Names (126946)

SUMMARY

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:

Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0).

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

Create a blank new form that is not based on any table or query.
Add a combo box control to the form.
Set the RowSourceType property of the combo box to ListAllTables.
View the form in Form view. Note that the combo box lists all the tables in the current database.