Query to populate combobox with names of tables, only if they contain data

I just came up with an interesting trick that may be useful to someone else.



I have a form with a combobox, populated by names of tables in the database.
The intent is to let the user select a table that is then used for
subsequent inquiries. What was making the whole process a little inelegant
was that occasionally, some of the tables would be empty. There is no point
in offering a particular table at all, if there is no data in it for the
user to examine. What I wanted was a way to make the combobox display only
those tables that have at least one record.



It would be a fairly routine exercise to do all this with VBA and populate
the combobox in code, but I was already using a query to populate the
combobox directly and didn't want to redo the whole thing, besides which, I'm
always looking for new ways to do stuff.



The source for the combobox was this query:



SELECT Name

FROM MSysObjects

WHERE Type=4;



The combobox displays the names of all the linked tables in the database,
whether or not they contain data.



To make the combobox display only those tables that have some data, I
created a few auxiliary queries and joined the results.



First, I made individual queries for each linked table that return a literal
of one record, the table name, if the table contains data. The queries are
named "First of " and the source table name, here [First of
data-chiroptera]:



SELECT "data-chiroptera" AS TableName

FROM [data-chiroptera]

HAVING First(druh) Is Not Null;



This returns one record, one field, with the literal "data-chiroptera" if
there is anything in the [druh] field (a required field in the table) and an
empty result set otherwise. There is one such query for every linked table.



Next I made a union query, named [Union of Firsts], to join all these
one-field queries into a single recordset:



SELECT TableName

FROM [First of 2006-dotazniky]



UNION



SELECT TableName

FROM [First of data-chiroptera]



UNION



SELECT TableName

FROM [First of data-mammalia];



Finally, I modified the source query for the combobox to use this query:



SELECT MSysObjects.Name

FROM MSysObjects INNER JOIN

[Union of Firsts] ON MSysObjects.Name = [Union of Firsts]. TableName

WHERE MSysObjects.Type=4;



This populates my combobox with only the names of those tables that have
data, since the union query has a record for a table only if the table
contains data, and this last join matches only the linked tables whose names
appear in the union query. Simple, quick and no VBA. This is a static
configuration, so I can create it once and leave it alone, but it would be
simple enough to create the "First" and "Union" queries in code if the
linked tables were going to change.



Pete


--
This e-mail address is fake, to keep spammers and their address harvesters
out of my hair. If you want to get in touch personally, I am 'pdanes' and I
use yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.