Dropdown that shows tables

[Jahappz]

Hi there i have this problem, i want to have a form where i have a drop down box that lists all available tables in the database, so if a user creates a new table its automatic listed in the dropdown box, and the selected tables posts should be listed , anyone have a sample db with this function???

thanks!

 

[ZmrAbdulla]

SQL for ComboBox show All Tables Except System Tables

SELECT MSysObjects.Name, MSysObjects.Connect FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "MSys" & "*") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6)) ORDER BY MSysObjects.Name;

SQL for ComboBox show All Tables including System Tables

SELECT MSysObjects.Name, MSysObjects.Connect FROM MSysObjects WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6)) ORDER BY MSysObjects.Name;

hope this helps

________________________________________________________________________
Zameer Abdulla
Visit Me
A sweater is usually put on a child when the parent feels chilly.

 

[Jahappz]

can you get the same without system tables?

 

[PHV]

SELECT [Name], Connect
FROM MSysObjects
WHERE [Name] Not Like "MSys*" AND [Name] Not Like '~*' AND [Type] In (1, 4, 6)
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZmrAbdulla]

First one will bring it, I used "Except" instead of "Without"

________________________________________________________________________
Zameer Abdulla
Visit Me
A sweater is usually put on a child when the parent feels chilly.

 

[Jahappz]

Yes the dropdown works perfect!

if i want to list the selected table's posts in a submenu is there an easy way to make that also?
so you can edit posts...?

 

[Jahappz]

to show you guys alittle more what im after check the db:

http://www.andreashansen.se/tek-tips/db.mdb

 

[ZmrAbdulla]

Put this on AfterUpdate Event of the Table Select Combo

Private Sub select_AfterUpdate()
    Dim MyTable As Variant
    Set MyTable = Me.select
    Me.rows.Form.RecordSource = Me.select.Value

    Me.rows.Form.Text3.ControlSource = CurrentDb.TableDefs(MyTable).Fields(0).Name
    Me.rows.Form.Text5.ControlSource = CurrentDb.TableDefs(MyTable).Fields(1).Name
End Sub

hope this helps

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.