How to Get a Table Name into Combo box




How to List a Table Name in Combo box

In this How To, I will show you how to put a list of all tables in a combo box.  You don’t have to have a table to enter a name of all tables. You can just load all table names into the combo box when form is loaded.  There are four tables in the picture below and I have them listed in the combo box when form1 is loaded. Follow the steps below to load the table list into the combo box.

  1. Create a blank form
  2. Click on the Combo Box icon

  1. Click on a Cancel button to cancel the Combo Box Wizard

4. Double click on combo box to open the Property Sheet

5. Change Row Source Type to Value List under the Data tab of Combo box property

6. Click on Event tab of property sheet

7. Select Event Procedure of On Load event

8. Click on 3 dots (…) at the end of On Load event line to open the VBA window

9. Enter the code Me.Combo3.RowSource = GetTableList

10. Create a function GetTableList() below

11. Save and view form

 

Private Sub Form_Load()
Me.Combo3.RowSource = GetTableList
End Sub
Function GetTableList() As String
Dim db As Database
Dim tdf As TableDef
Dim strList As String

Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
    strList = strList & tdf.Name & ";"
End If
Next tdf
GetTableList = strList
Set tdf = Nothing
db.Close
Set db = Nothing
End Function

 

When form1 is loaded, the table name will be listed under the combo box of Table List as shown in the picture below.

Under the GetTableList function, you will see the If Statement of “If Left(tdf.Name, 4) <> “MSys” Then” is used. There are many system tables which start with Msys were hidden. So you can see only four tables in the combo box above.

If Left(tdf.Name, 4) <> “MSys” Then

          strList = strList & tdf.Name & “;”

End If

If this If Statement is not used, the code For Each Loop will look like code below. The combo box will include all system tables as shown in the picture below.

For Each tdf In db.TableDefs

         strList = strList & tdf.Name & “;”

Next tdf