List tables in a combo or list box

[mjdavie]

Could anybody tell me how I can list my tables in a list box or combo box,

Thanks in advance
Mat

 

[Pat Hartman]

This query will produce a list of all the tables in or linked to your database. You can use it as the recordsource of a combobox.

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

 

[mjdavie]

Cheers Pat, it worked a treat

 

[flavioso]

I found this post from aways back, and would like to know if anyone knows a way to open the table from the combo box without having to create a "switchboard"?

I appreciate any help.

Thanks,

Brad

This query will produce a list of all the tables in or linked to your database. You can use it as the recordsource of a combobox.

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

 

[Pat Hartman]

1. I wouldn't open raw tables for a user. I would use forms.
2. Use the BeforeUpdate event of the combo box to run the code to open a form (or if you insist, open a query).