Displaying table names with a combobox

[Dave - Freedonia]

I am trying to display the names of the tables in an Access database in
a combo box.

I have created a query to get the table names. Here is the query:
------
SELECT [MsysObjects].[Name] AS Tablename
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") And ((Left$([Name],4))<>"Msys") And
(([MsysObjects].[Type])=6))
ORDER BY [MsysObjects].[Name];
------

When run, this will show me the table names.

If I create a new form and insert a combo box and use the above query as
the record source, I get this error message:
"No valid fields can be found in 'qryTableNames'. Please select a new
source."

qryTableNames is the name of the query from above. If I change the
query to a make table query and change combobox to look at that table, I
still get the same error message.

I noticed for the make table query, when I view the table design, the
field (TableName) is listed as a text field but the column has
AutoNumber at the bottom.

I am using Access 2000 SP1 and Windows XP SP1.

Any ideas?

Thanks,
Dave

 

[Dirk Goldgar]

I am trying to display the names of the tables in an Access database
in a combo box.

I have created a query to get the table names. Here is the query:
------
SELECT [MsysObjects].[Name] AS Tablename
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") And ((Left$([Name],4))<>"Msys") And
(([MsysObjects].[Type])=6))
ORDER BY [MsysObjects].[Name];
------

When run, this will show me the table names.

If I create a new form and insert a combo box and use the above query
as the record source, I get this error message:
"No valid fields can be found in 'qryTableNames'. Please select a new
source."

qryTableNames is the name of the query from above. If I change the
query to a make table query and change combobox to look at that
table, I still get the same error message.

I noticed for the make table query, when I view the table design, the
field (TableName) is listed as a text field but the column has
AutoNumber at the bottom.

I am using Access 2000 SP1 and Windows XP SP1.

Any ideas?

Thanks,
Dave

Type 6 is linked tables. Is it linked tables you're interested in, or
was it local tables, type 1, that you wanted?

 

[Dave - Freedonia]

Yes, type 6 linked tables. I was trying to create a listing of the
linked table names in a combo box.

This is a front end database that I am working on. The tables are
stored in a backend MS Access database.

I got the code from http://www.mvps.org/access/ in the queries section.
After a copy/paste of the code, I noticed I only got the local table
names and so I did a 'general' query and found type 6 was what I needed
for the table names I was looking for.

Dave

I am trying to display the names of the tables in an Access database
in a combo box.

I have created a query to get the table names. Here is the query:
------
SELECT [MsysObjects].[Name] AS Tablename
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") And ((Left$([Name],4))<>"Msys") And
(([MsysObjects].[Type])=6))
ORDER BY [MsysObjects].[Name];
------

When run, this will show me the table names.

If I create a new form and insert a combo box and use the above query
as the record source, I get this error message:
"No valid fields can be found in 'qryTableNames'. Please select a new
source."

qryTableNames is the name of the query from above. If I change the
query to a make table query and change combobox to look at that
table, I still get the same error message.

I noticed for the make table query, when I view the table design, the
field (TableName) is listed as a text field but the column has
AutoNumber at the bottom.

I am using Access 2000 SP1 and Windows XP SP1.

Any ideas?

Thanks,
Dave

Type 6 is linked tables. Is it linked tables you're interested in, or
was it local tables, type 1, that you wanted?

 

[Dirk Goldgar]

Yes, type 6 linked tables. I was trying to create a listing of the
linked table names in a combo box.

This is a front end database that I am working on. The tables are
stored in a backend MS Access database.

I got the code from http://www.mvps.org/access/ in the queries
section. After a copy/paste of the code, I noticed I only got the
local table names and so I did a 'general' query and found type 6 was
what I needed for the table names I was looking for.

Interesting. I get the same result when I use the combo box wizard. It
appears to be because the table is hidden. If you click Tools ->
Options..., go to the View tab, put a check in the box next to "System
objects", and click OK, then the wizard doesn't have a problem. So you
could do that, create the combo, then hide System objects again.

However, if you skip the wizard and create the combo box manually, you
can just enter the name of the query in the Row Source property
yourself, and it should work fine. It does for me, anyway.