By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Table names in listbox

100+
P: 365
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
Jun 19 '09 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,679
@Dan2kx
This is just 1 Method of accomplishing what you request, and it assumes your List Box Name is lstTableNames:
Expand|Select|Wrap|Line Numbers
  1. Dim tdf As DAO.TableDef
  2. Dim lst As ListBox
  3.  
  4. Set lst = Me![lstTableNames]
  5.  
  6. For Each tdf In CurrentDb.TableDefs
  7.   'Eliminate System Tables and Temporary Tables
  8.   If Left$(tdf.Name, 4) <> "MSys" And Left$(tdf.Name, 1) <> "~" Then
  9.     lst.AddItem tdf.Name
  10.   End If
  11. Next
And another Method
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim lst As ListBox
  3.  
  4. Set lst = Me![lstTableNames]
  5.  
  6. strSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = 1 And " & _
  7.          "Left$([Name], 4) <> 'MSys' And " & _
  8.          "Left$([Name], 1) <> '~' ORDER BY [Name];"
  9.  
  10. lst.RowSourceType = "Table/Query"
  11. lst.RowSource = strSQL
Jun 19 '09 #2

100+
P: 365
Genius ADezii

Thanks
Jun 19 '09 #3

ADezii
Expert 5K+
P: 8,679
@Dan2kx
You are quite welcome.
Jun 19 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.