combo box with table list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a combo box that contains the list of tables and queries
in my database. Once the user selects a table or query, I then want to
populate a second list box to display all the fields for the table/query
selected in the first combo box. I'm using Access 2000. I have the following
function that returns a list of the current tables/queries, though it returns
some internal tables which I'd want to exclude.

Dim mylist As String
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
mylist = ""
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
mylist = IIf(Nz(mylist) = "", mylist, mylist & "; ") & "Table: "
& obj.Name
Next obj
For Each obj In dbs.AllQueries
mylist = IIf(Nz(mylist) = "", mylist, mylist & "; ") & "Query: "
& obj.Name
Next obj
mytablesandqueries = mylist

I'm having trouble (a) setting the row source of the first combo box to the
list of tables and queries and (b) excluding the internal tables. Can anyone
help?
Thank you,
Gwyn
 
I'm trying to create a combo box that contains the list of tables and queries
in my database. Once the user selects a table or query, I then want to
populate a second list box to display all the fields for the table/query
selected in the first combo box. I'm using Access 2000. I have the following
function that returns a list of the current tables/queries, though it returns
some internal tables which I'd want to exclude.

Dim mylist As String
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
mylist = ""
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
mylist = IIf(Nz(mylist) = "", mylist, mylist & "; ") & "Table: "
& obj.Name
Next obj
For Each obj In dbs.AllQueries
mylist = IIf(Nz(mylist) = "", mylist, mylist & "; ") & "Query: "
& obj.Name
Next obj
mytablesandqueries = mylist

I'm having trouble (a) setting the row source of the first combo box to the
list of tables and queries and (b) excluding the internal tables. Can anyone
help?
Thank you,
Gwyn
Click to expand...

This is the RowSource for the first Combo Box to show all the queries
and tables:

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

Set the second combo box RowSource Type to Field List.
Leave it's Rowsource blank.
Code the First Combo Box AfterUpdate event:
Me![Combo2].RowSource = Me![Combo1]

Change Combo1 and Combo2 to the actual names of your List boxes.
 
Thanks! Just what I needed!

fredg said:
I'm trying to create a combo box that contains the list of tables and queries
in my database. Once the user selects a table or query, I then want to
populate a second list box to display all the fields for the table/query
selected in the first combo box. I'm using Access 2000. I have the following
function that returns a list of the current tables/queries, though it returns
some internal tables which I'd want to exclude.

Dim mylist As String
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
mylist = ""
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
mylist = IIf(Nz(mylist) = "", mylist, mylist & "; ") & "Table: "
& obj.Name
Next obj
For Each obj In dbs.AllQueries
mylist = IIf(Nz(mylist) = "", mylist, mylist & "; ") & "Query: "
& obj.Name
Next obj
mytablesandqueries = mylist

I'm having trouble (a) setting the row source of the first combo box to the
list of tables and queries and (b) excluding the internal tables. Can anyone
help?
Thank you,
Gwyn
Click to expand...

This is the RowSource for the first Combo Box to show all the queries
and tables:

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

Set the second combo box RowSource Type to Field List.
Leave it's Rowsource blank.
Code the First Combo Box AfterUpdate event:
Me![Combo2].RowSource = Me![Combo1]

Change Combo1 and Combo2 to the actual names of your List boxes.
Click to expand...
 
Back
Top