If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Query to populate combobox with names of tables, only if they contain data
On Fri, 20 Nov 2009 14:37:06 +0100, "Skru spammers"
wrote: Here is an alternative with a few lines of VBA. Set your combobox to have two columns, both visible. The first column is the bound column. The RowSourceType is set to ValueList. Then in the Form_Load write: Me.myCombo.RowSource = GetTableInfo() And in some standard module you have: Public Function GetTableInfo() As String Dim strData As String Dim td As TableDef For Each td In CurrentDb.TableDefs strData = strData & td.Name & ";" & GetRecordCount(td.Name) & ";" Next td GetTableInfo = strData End Function Private Function GetRecordCount(ByVal strTable As String) As Long Dim lngCount As Long Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(strTable, dbOpenTable, dbReadOnly) If Not rs.EOF Then rs.MoveLast lngCount = rs.RecordCount rs.Close Set rs = Nothing GetRecordCount = lngCount End Function If you don't like to show MSys* tables, change the code to: For Each td In CurrentDb.TableDefs If td.Attributes And dbSystemObject Then 'no need to display system objects Else strData = strData & td.Name & ";" & GetRecordCount(td.Name) & ";" End If Next td -Tom. Microsoft Access MVP 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 |
#3
|
|||
|
|||
Query to populate combobox with names of tables, only if they contain data
Hi Tom,
that's a usable method, but in reading the code, I see you're displaying all the tables, with their record counts. That can certainly be useful, but in my case, I wanted to not display the names of empty tables at all, so I would need to add a test to not include the cases where GetRecordCount returns zero. Also, when you use a value list, you're rather sharply limited in length. My app has only a few tables, so it would be fine, but a longer list might run into problems. Regardless, thank you, another one for the toolbox. Of course, once you go to VBA, there are any number of ways to accomplish the task. The cute thing about the method I posted was that it does -everything- with SQL, which is my preference, whenever possible. It's easier to read, easier to troubleshoot, faster to execute, inherently modular and much less prone to obscure logic bugs. Nothing against VBA, I use it constantly, but my personal preference is to do as much as possible with database properties and queries, and only code what can't be done any other way. And finally, your version can also be expressed in SQL. Simply change my "First of" queries to "Count of" queries, containing the following SQL: SELECT "data-chiroptera" AS TableName, Count(Druh) as RecordCount FROM [data-chiroptera]; or if you want to exclude empty tables: SELECT "data-chiroptera" AS TableName, Count(Druh) as RecordCount FROM [data-chiroptera] HAVING Count(Druh)0; and everthing else works the same. Pete "Tom van Stiphout" píse v diskusním príspevku ... On Fri, 20 Nov 2009 14:37:06 +0100, "Skru spammers" wrote: Here is an alternative with a few lines of VBA. Set your combobox to have two columns, both visible. The first column is the bound column. The RowSourceType is set to ValueList. Then in the Form_Load write: Me.myCombo.RowSource = GetTableInfo() And in some standard module you have: Public Function GetTableInfo() As String Dim strData As String Dim td As TableDef For Each td In CurrentDb.TableDefs strData = strData & td.Name & ";" & GetRecordCount(td.Name) & ";" Next td GetTableInfo = strData End Function Private Function GetRecordCount(ByVal strTable As String) As Long Dim lngCount As Long Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(strTable, dbOpenTable, dbReadOnly) If Not rs.EOF Then rs.MoveLast lngCount = rs.RecordCount rs.Close Set rs = Nothing GetRecordCount = lngCount End Function If you don't like to show MSys* tables, change the code to: For Each td In CurrentDb.TableDefs If td.Attributes And dbSystemObject Then 'no need to display system objects Else strData = strData & td.Name & ";" & GetRecordCount(td.Name) & ";" End If Next td -Tom. Microsoft Access MVP 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 |
Thread Tools | |
Display Modes | |
|
|