Microsoft KB Archive/109327

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

Q109327

---

The information in this article applies to:

• 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.

The combo box will be filled with the names of the tables in the NWIND database.

REFERENCES

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

Microsoft Access "Introduction to Programming," pages 82-85

Keywords : kbusage
Issue type : kbhowto
Technology :

Last Reviewed: November 4, 2000 © 2001 Microsoft Corporation. All rights reserved. Terms of Use.