Table names in listbox

[Dan2kx]

Good morning guys,
I have another one of my peculiar requests for ya...

As title suggests i would like a dynamic list of available tables so that the user can select one and it uses that value as a vb variable. im just not sure how to construct a table of such values? can it be done in sql or would i need to use a loop in vb?

cheers,
Dan

[ADezii]

@Dan2kx
This is just 1 Method of accomplishing what you request, and it assumes your List Box Name is lstTableNames:

Dim tdf As DAO.TableDef
Dim lst As ListBox
 
Set lst = Me![lstTableNames]
 
For Each tdf In CurrentDb.TableDefs
  'Eliminate System Tables and Temporary Tables
  If Left$(tdf.Name, 4) <> "MSys" And Left$(tdf.Name, 1) <> "~" Then
    lst.AddItem tdf.Name
  End If
Next

And another Method

Dim strSQL As String
Dim lst As ListBox
 
Set lst = Me![lstTableNames]
 
strSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = 1 And " & _
         "Left$([Name], 4) <> 'MSys' And " & _
         "Left$([Name], 1) <> '~' ORDER BY [Name];"
 
lst.RowSourceType = "Table/Query"
lst.RowSource = strSQL

[Dan2kx]

Genius ADezii

Thanks

[ADezii]

@Dan2kx
You are quite welcome.