Use of listbox to display tables

[SeRene]

Hi, I need to make use of a listbox to display the tables
available in my database and provide a command button to
click and view the table selected in the listbox. Is there
a way to get this done? I was told that it has something
to do with the Properties --> Data --> Row Source.
However, after multiple attempts, i do not get any results
and making use of the listbox wizard doesnt get me what i
wanted.

Can someone help me with this problem??
Thank You!!!

 

[Dave]

Crate a form and add a list box called List0

then add this code to the module
I hope this is what you want,
back up everything before you try

Dave

---------------------------------------------------
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Dim db As Database
Dim varstr As String
Dim tdfLoop As TableDef
Dim i As Integer

Set db = CurrentDb()
With db
' Enumerate TableDefs collection.
For Each tdfLoop In .TableDefs

varstr = varstr & tdfLoop.Name & ", "

Next tdfLoop

End With


Me.List0.RowSourceType = "Value List"
Me.List0.RowSource = varstr


End Sub

Private Sub List0_Click()

DoCmd.OpenTable Me.List0

End Sub

 

[fredg]

Hi, I need to make use of a listbox to display the tables
available in my database and provide a command button to
click and view the table selected in the listbox. Is there
a way to get this done? I was told that it has something
to do with the Properties --> Data --> Row Source.
However, after multiple attempts, i do not get any results
and making use of the listbox wizard doesnt get me what i
wanted.

Can someone help me with this problem??
Thank You!!!

Set the List Box Row Source Type property to Table/Query.
As Row Source for the list box:
SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

You do not need a command button to open the selected table.
Code the List Box DoubleClick event:
DoCmd.OpenTable Me!ListBoxName

That's it.

 

[SeRene]

WOW!! Fantastic!! Thanks, Fred. Your method is so
simplified and it definitely suits an Access-ignorant
person like me!

One more question, i need to provide a command button
because not only do i need to allow the user to click on
the selected table to view, i also need to allow the user
to click on the desired table from the listbox to import
the latest data from an excel file!

Can you kindly teach me how i can achieve this?
Thank You!!

 

[SeRene]

Hi Dave,
Thanks for your wonderful codes!!!
=)
However, is there any way i can manipulate the codes to
display only the tables which i want to see instead of
displaying ALL tables??