Listbox for tables

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

Is there a way to have all my tables in a listbox and then when I
double-click on one of them it opens the table in datasheet view?
 
Secret Squirrel said:
Is there a way to have all my tables in a listbox and then when I
double-click on one of them it opens the table in datasheet view?
Click to expand...


Sure, it's possible, though I wouldn't open tables in datasheet view in any
application intended for general users.

If you really want to do this, you can set the RowSource property of the
list box to a query like this:

SELECT MSysObjects.Name FROM MsysObjects
WHERE (Left$([Name],1)<>"~")
AND (Left$([Name],4) <> "Msys")
AND MSysObjects.Type In (1, 4, 6)
ORDER BY MSysObjects.Name;

Then, in the DblClick event of the list box, put code like this:

'----- start of example code -----
Private Sub lstTables_DblClick(Cancel As Integer)

DoCmd.OpenTable Me.lstTables

End Sub
'----- end of example code -----

Note that the above query uses the MSysObjects system table, which is
theoretically undocumented and subject to change. However, I think it
unlilkely that MS would change the structure of that table at this point in
such a way as to invalidate the query.
 
Thanks Dirk. I appreciate the FYI about opening tables in datasheet view.
This listbox is only intended for me to use, not general users. And I will
only be using it during off hours when nobody is on the system.

Dirk Goldgar said:
Secret Squirrel said:
Is there a way to have all my tables in a listbox and then when I
double-click on one of them it opens the table in datasheet view?
Click to expand...


Sure, it's possible, though I wouldn't open tables in datasheet view in any
application intended for general users.

If you really want to do this, you can set the RowSource property of the
list box to a query like this:

SELECT MSysObjects.Name FROM MsysObjects
WHERE (Left$([Name],1)<>"~")
AND (Left$([Name],4) <> "Msys")
AND MSysObjects.Type In (1, 4, 6)
ORDER BY MSysObjects.Name;

Then, in the DblClick event of the list box, put code like this:

'----- start of example code -----
Private Sub lstTables_DblClick(Cancel As Integer)

DoCmd.OpenTable Me.lstTables

End Sub
'----- end of example code -----

Note that the above query uses the MSysObjects system table, which is
theoretically undocumented and subject to change. However, I think it
unlilkely that MS would change the structure of that table at this point in
such a way as to invalidate the query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top