Tables (not their fields) in a "dynamic" list box

[Guest]

1. I would like to have a form with a dropdown box listing all the tables in
my database.
2. When a new table is created, it will automatically be added to the list
box.
Are they both possible?
Thank you in advance for your help.

 

[fredg]

1. I would like to have a form with a dropdown box listing all the tables in
my database.
2. When a new table is created, it will automatically be added to the list
box.
Are they both possible?
Thank you in advance for your help.

As rowsource of a list box or combo box:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

If you add additional tables, they will be added the next time the
form is opened. If you need to show the table immediately, you need to
requery the combo box:
Me![ComboName].requery

 

[Douglas J. Steele]

1. I would like to have a form with a dropdown box listing all the tables
in
my database.
2. When a new table is created, it will automatically be added to the
list
box.
Are they both possible?
Thank you in advance for your help.

As rowsource of a list box or combo box:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

You might want to include types 4 (tables linked through ODBC) and 6 (other
linked tables) in that query as well.

 

[Guest]

Fred,
Thank you very much. It does exactly what I was looking for.

1. I would like to have a form with a dropdown box listing all the tables in
my database.
2. When a new table is created, it will automatically be added to the list
box.
Are they both possible?
Thank you in advance for your help.

As rowsource of a list box or combo box:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

If you add additional tables, they will be added the next time the
form is opened. If you need to show the table immediately, you need to
requery the combo box:
Me![ComboName].requery

 

[Guest]

Thank you, Doug. Excellent suggestion.

1. I would like to have a form with a dropdown box listing all the tables
in
my database.
2. When a new table is created, it will automatically be added to the
list
box.
Are they both possible?
Thank you in advance for your help.

As rowsource of a list box or combo box:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

You might want to include types 4 (tables linked through ODBC) and 6 (other
linked tables) in that query as well.