Tuning: combobox of tables 2

[hkaing79]

Just curious if there are more efficient ways of going about this. I would like to populate a combobox with a list of all the tables in the database.

Function AllTables()
    Dim db As Database
    Dim tbl As TableDef
    Dim strTable As String
    
    Set db = CurrentDb()
    strTable = ""
    
    For Each tbl In db.TableDefs
        If Left(tbl.Name, 4) <> "msys" Then
            strTable = strTable & "'" & tbl.Name & "';"
        End If
    Next tbl
    
    AllTables = strTable
End Function

cboTable.RowSourceType = "Value List"
cboTable.RowSource = AllTables

Is there a built in command that I overlooked?

It's fine for now because there's not many tables, but I'm planning to use the same algorithm to populate a second combobox listing all the fields in the table (which can be quite long).

 

[PHV]

to populate a second combobox listing all the fields in the table
cboField.RowSourceType = "Field List"
cboField.RowSource = cboTable.Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya hkaing79 . . . . .

Try this:

[blue]   Dim SQL As String
   
   SQL = "SELECT Name " & _
         "FROM MSysObjects " & _
         "WHERE ((Left([Name],4)<>'MSys') AND (Type=1));"

   Me!cboTable.RowSourceType = "Table/Query"
   Me!cboTable.RowSource = SQL[/blue]

See Ya! . . . . . .

 

[hkaing79]

WOW. Thanks to both of you. Much shorter coding.