Populate ListBox with Table Names

[gammaman]

I am trying to populate a ListBox with the names of the tables from another database. I have the following function which loops the table names from the database I want. The function is caleld on Form Load and passed into the ListBox but the List Box is empty

NOTE: The message box in the function does display the table names when called from the form. I just cannot get the list box to populate.

Public Function ListTables()
Dim db As Database
Dim i As Integer
Dim s As String
Dim tdefs As TableDefs, tdef As TableDef
Set db = DBEngine.OpenDatabase("C:\MyPath\Analyzed Tables.mdb")
For i = 0 To db.TableDefs.count - 1
s = db.TableDefs(i).Name
ListTables = s
'MsgBox s
Next i
End Function

Private Sub Form_Load()
List1.RowSource = ListTables
End Sub

 

[vbaInet]

If you do a SELECT query on the MSysObjects table you'll find two fields that returns the table names and type. Enter some criteria to filter it out.

 

[gammaman]

Ok. So I got it working. I removed the function and just did everyting on form load. I am now trying to take the selected value from the listbox and pass it into TransferDatabase to import the selected item into the current db.

Private Sub Form_Load()
 
Set db = DBEngine.OpenDatabase("C:\MyPath\Analyzed Tables.mdb")
'Set dbs = Application.CurrentData
Set lst = Me.List5
 
With lst
For Each tb3 In db.TableDefs
If Left$(tb3.Name, 4) <> "MSys" And Left$(tb3.Name, 1) <> "~" And Left$(tb3.Name, 3) <> "SYS" Then
.AddItem tb3.Name
End If
Next
End With
db.Close
End Sub

Private Sub List5_DblClick(Cancel As Integer)
DoCmd.TransferDatabase,acImport,"C:\MyPath\AnalyzedTables.mdb",acTable,List5.ItemsSelected,Current()
End Sub

I am getting wrong data type for one of the arguments error.

[/CODE]

 

[vbaInet]

Have you tested to see if List5.ItemsSelected is really doing what you think it does?

 

[gammaman]

Ok, If I do Msg on List5.Value I get the name of the table. How can I take it and use it as the source parameter on the TransferDatabase?

 

[vbaInet]

ItemsSelected is a collection, think of it like an array. So if you want to get all the values of all the items selected using the ItemsSelected property of the listbox, you need to loop through it like you would to an array. Use the For Each loop.