ACC: How to Fill a Combo Box with Table Names

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article demonstrates 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 combo box's RowSourceType property to ListAllTables.

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

REFERENCES

For more information about DAO, search the Help Index for "DAO collections," or ask the Microsoft Access 97 Office Assistant.