ACC1x: How to Fill a Combo Box with Table Names (1.x)

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes how to create a combo box that will list the names of the tables in your Microsoft Access version 1.x database.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access.

MORE INFORMATION

The following example demonstrates how to create a combo box that lists the names of tables in your database:

1. Add the following to the Declarations section of the Introduction to Programming module in the sample database NWIND.MDB:

         Option Explicit
   

2. Enter the following sample function:

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

       Function TableList (Fld As Control, Id, Row, Col, Code)
         Static TbLst_Array(100), No_Entries
         Dim MyDB As Database
         Dim cnt As Integer
         Dim Returnval
         Dim SnapOfTables As Snapshot
   

         Returnval = Null
   

         Set MyDB = CurrentDB()
         Set SnapOfTables = MyDB.ListTables()
   

         Select Case code
            Case 0
            '------------------------------------------------------------
            '  CASE 0:  Initializes the function. Lets the function know
            '  how many elements will be in the list box. It also
            '  initializes the array that holds the elements.
            '------------------------------------------------------------
               SnapOfTables.MoveFirst
               No_Entries = 0
               TbLst_Array(No_Entries) = Null
               Do Until SnapOfTables.EOF
                  If SnapOfTables.tabletype = db_table And_
                  SnapOfTables.attributes = 0 Then
                     TbLst_Array(No_Entries) = SnapOfTables.Name
                     No_Entries = No_Entries + 1
                  End If
                  SnapOfTables.MoveNext
               Loop
               Returnval = No_Entries
   
            Case 1
            '------------------------------------------------------------
            ' Open. Provides a unique id number for the function. Most
            ' cases just use the following code in this case.
            '------------------------------------------------------------
               Returnval = Timer
   
            Case 2
            '------------------------------------------------------------
            ' (Reserved for future use.) This case should be omitted.
            '------------------------------------------------------------
   
            Case 3
            '------------------------------------------------------------
            ' Number of Rows. Lets the function know how many rows are
            ' going to be in the list (can be zero). Use -1 if number is
            ' unknown.
            '------------------------------------------------------------
               Returnval = No_Entries
   
            Case 4
            '------------------------------------------------------------
            ' Number of Columns. (CANNOT BE ZERO) Should match the value
            ' in the property sheet.
            '------------------------------------------------------------
               Returnval = -1
   
            Case 5
            '------------------------------------------------------------
            ' Column width. Width in twips of column specified by the col
            ' argument. Use -1 to use default widths.
            '------------------------------------------------------------
               Returnval = -1
   
            Case 6
            '------------------------------------------------------------
            ' List Entry. Gives element to be displayed in the row and
            ' column specified by the row and col arguments.
            '------------------------------------------------------------
               Returnval = TbLst_Array(row)
   
            Case 7
            '------------------------------------------------------------
            ' Format String. Format string to be used to format the list
            ' element displayed in the row and column specified by the
            ' row and col arguments. Use NULL to use the default format.
            '------------------------------------------------------------
   
            Case 8
            '------------------------------------------------------------
            '(Reserved for Future Use.) This case should be omitted.
            '------------------------------------------------------------
   
            Case 9
            '------------------------------------------------------------
            ' End. This is the last call to function. Always
            ' include this case. This is a good place for any
            ' cleanup code.
            '------------------------------------------------------------
               For No_Entries = 0 To cnt
                  TbLst_Array(cnt) = ""
               Next
         End Select
         TableList = Returnval
       End Function
   
   

3. Save the module.

4. Create a new, unbound form.

5. Place a combo box with the following properties on the form:

         Form: Form1
         ---------------------------
         Combo Box:
            ControlName: Tablelist
            RowSourceType: TableList
            ColumnCount:    1
            BoundColumn:    1
   
   

6. View the form in Form view. Click the combo box.

REFERENCES

Microsoft Access "User's Guide," Chapter 9, "Designing Forms"