combo box automatically populate with all available tablesin DB
- Thread starter jguscs
- Start date
dcx693
Registered User.
- Local time
- Today, 07:34
- Joined
- Apr 30, 2003
- Messages
- 3,265
Really easy, use VBA and DAO or ADO (enough acronyms?) to open the collection of tables and get their names. Be sure to skip those tables that begin "Msys" since they are Access system tables. Concatenate the table names separating them with semicolons, then assign that to the recordsource of your combo box.
jguscs,
This thread should help:
http://www.access-programmers.co.uk...hreadid=47368&highlight=msysobjects+and+wayne
Pay particular attention to Pat's link about table types.
Wayne
This thread should help:
http://www.access-programmers.co.uk...hreadid=47368&highlight=msysobjects+and+wayne
Pay particular attention to Pat's link about table types.
Wayne
dcx693
Registered User.
- Local time
- Today, 07:34
- Joined
- Apr 30, 2003
- Messages
- 3,265
Here's a simpler question: what version of Access are you using?
If you're using Access 2000 or above use this code in a module. Just change the reference to the form name to be that of your form name:
Sub Gettablenames()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim str As String
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
str = str & ";" & tbl.Name
End If
Next
Forms!formname!comboxbox.RowSourceType = "Value List" Forms!formname!comboxbox.Recordsource = Right(str, Len(str) - 1)
Set cat = Nothing
End Sub
If you're using Access 97 use this:
Sub Gettablenames()
Dim dbs As DAO.Database
Dim tdf As TableDef
Dim str As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
str = str & ";" & tdf.Name
End If
Next tdf
Me.cboTest.RowSourceType = "Value List"
Me.cboTest.RowSource = Right(str, Len(str) - 1)
Set dbs=Nothing
End Sub
If you're using Access 2000 or above use this code in a module. Just change the reference to the form name to be that of your form name:
Sub Gettablenames()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim str As String
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
str = str & ";" & tbl.Name
End If
Next
Forms!formname!comboxbox.RowSourceType = "Value List" Forms!formname!comboxbox.Recordsource = Right(str, Len(str) - 1)
Set cat = Nothing
End Sub
If you're using Access 97 use this:
Sub Gettablenames()
Dim dbs As DAO.Database
Dim tdf As TableDef
Dim str As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
str = str & ";" & tdf.Name
End If
Next tdf
Me.cboTest.RowSourceType = "Value List"
Me.cboTest.RowSource = Right(str, Len(str) - 1)
Set dbs=Nothing
End Sub
Last edited:
dcx693
Registered User.
- Local time
- Today, 07:34
- Joined
- Apr 30, 2003
- Messages
- 3,265
If you get the "User-defined type not defined error" and it points to cat As ADOX.Catalog then while in the Visual Basic Editor, go to the Tools menu, choose References, and make sure a reference to "Microsoft ADO Ext xx for DDL and Security" is checked.
Wow, thanks for all the assistance, dcx693.
As advised, I created a module (how does Access know to look here though?). I named it "Tables."
Then, as you suspected, it gave me the "Not Defined" error, so I checked the "Microsoft ADO Ext 2.5 for DDL and Security" and continued. I also referenced the Combo Box in my form appropriately and there were no errors upon compilation.
The only thing is that the combo box still doesn't list anything.
Did I miss something?
As advised, I created a module (how does Access know to look here though?). I named it "Tables."
Then, as you suspected, it gave me the "Not Defined" error, so I checked the "Microsoft ADO Ext 2.5 for DDL and Security" and continued. I also referenced the Combo Box in my form appropriately and there were no errors upon compilation.
The only thing is that the combo box still doesn't list anything.
Did I miss something?
dcx693
Registered User.
- Local time
- Today, 07:34
- Joined
- Apr 30, 2003
- Messages
- 3,265
You can call your module anything you want to (within certain limits), Access will look for the name of the procedure or function within it.
I calle my procedure Gettablenames. You should place that procedure call somewhere appropriate, perhaps into the Form's open event.
Also, how did you refer to your form's combo box from the module? You need to get the naming exactly right. It has to be in the form of: Forms!formname!comboboxname, where you subsititute your form name for formname and your combo box name for comboboxname.
I calle my procedure Gettablenames. You should place that procedure call somewhere appropriate, perhaps into the Form's open event.
Also, how did you refer to your form's combo box from the module? You need to get the naming exactly right. It has to be in the form of: Forms!formname!comboboxname, where you subsititute your form name for formname and your combo box name for comboboxname.
OK, I think I figured out how to call another procedure.
I my case, in the Form's Open procedure, I used:
Tables.Gettablenames
(where Tables is my module and Gettablenames is your sub-procedure)
Now there is an error upon compilation:
Run-Time Error 438: Object doesn't support this property or method. And it points to the line:
Forms!TableChooser!ComboChooser.RecordSource = Right(str, Len(str) - 1)
To answer your other question, I believe I'm referencing my combo box correctly (you can see how I am in the code above with the error 438).
TableChooser is my Form name
ComboChooser is my combo box name
I my case, in the Form's Open procedure, I used:
Tables.Gettablenames
(where Tables is my module and Gettablenames is your sub-procedure)
Now there is an error upon compilation:
Run-Time Error 438: Object doesn't support this property or method. And it points to the line:
Forms!TableChooser!ComboChooser.RecordSource = Right(str, Len(str) - 1)
To answer your other question, I believe I'm referencing my combo box correctly (you can see how I am in the code above with the error 438).
TableChooser is my Form name
ComboChooser is my combo box name
Well, the error was probably due to the fact that a combo box doesn't use RecordSource, it uses ControlSource.
So I changed that little bit and it compiled.
Forms!TableChooser!ComboChooser.ControlSource = Right(str, Len(str) - 1)
Still, the combo box isn't populating with the list of tables.
So I changed that little bit and it compiled.
Forms!TableChooser!ComboChooser.ControlSource = Right(str, Len(str) - 1)
Still, the combo box isn't populating with the list of tables.
It worked!
Thanks!
I realize that this is the part of the code that includes and excludes certain tables:
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
str = str & ";" & tbl.Name
End If
Next
How would I exclude a specific table (like tblObjectTypeCodes) from the list?
Thanks!
I realize that this is the part of the code that includes and excludes certain tables:
For Each tbl In cat.Tables
If Left(tbl.Name, 4) <> "MSys" Then
str = str & ";" & tbl.Name
End If
Next
How would I exclude a specific table (like tblObjectTypeCodes) from the list?
You should get yourself a big book on Access and VBA and dig in. Lots of fun.I've heard (and seen) that it's also possible to populate the combo box with all available table names using an SQL script.
(Which isn't to say that your method isn't preferrable... it works great! But I'm interested in SQL scripts, too.
For example, I've seen advice from Pat that says to create the table tblObjectTypeCodes with the following information in it:
Type TypeDesc
-32768 Form
-32766 Macro
-32764 Reports
-32761 Module
-32757 Access Object - ?
1 Table - Local Access Tables
2 Access Object - Database
3 Access Object - Containers
4 Table - Linked ODBC Tables
5 Queries
6 Table - Linked Access Tables
and then create the following SQL script:
SELECT tblObjectTypeCodes.TypeDesc, Count(*) AS ObjectCount
FROM MSysObjects LEFT JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
GROUP BY tblObjectTypeCodes.TypeDesc;
but obviously this has to be applied to my combo box and i'm not sure how. The SQL code also doesn't compile properly, according to the debugger. (It says "Type mismatch expression" when you try to run it.) Any guesses?
(Which isn't to say that your method isn't preferrable... it works great! But I'm interested in SQL scripts, too.
For example, I've seen advice from Pat that says to create the table tblObjectTypeCodes with the following information in it:
Type TypeDesc
-32768 Form
-32766 Macro
-32764 Reports
-32761 Module
-32757 Access Object - ?
1 Table - Local Access Tables
2 Access Object - Database
3 Access Object - Containers
4 Table - Linked ODBC Tables
5 Queries
6 Table - Linked Access Tables
and then create the following SQL script:
SELECT tblObjectTypeCodes.TypeDesc, Count(*) AS ObjectCount
FROM MSysObjects LEFT JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
GROUP BY tblObjectTypeCodes.TypeDesc;
but obviously this has to be applied to my combo box and i'm not sure how. The SQL code also doesn't compile properly, according to the debugger. (It says "Type mismatch expression" when you try to run it.) Any guesses?
