automatically populate combo box with available tables using SQL

jguscs

Registered User.
Local time
Today, 06:24
Joined
Jun 23, 2003
Messages
148
dcx693 proposed a very nice method of performing this task using an ADO.
In the mean time, however, some other means to performing this were suggested using an SQL scripts and a query.

The query would have in its criteria field (by WayneRyan):
Name not like "MSys*" and Type = 1

and the SQL script would look something like this (by sconly):
sSql = "SELECT [Name] " & _
"FROM MSysObjects " & _
"WHERE Type=1 and Flags=0 " & _
"ORDER BY [Name];"
cmbProjects.RowSource = sSql

and you'd want to like to a table called tblObjectTypeCodes (by Pat):
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

using the SQL (also by Pat):
SELECT tblObjectTypeCodes.TypeDesc, Count(*) AS ObjectCount
FROM MSysObjects LEFT JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
GROUP BY tblObjectTypeCodes.TypeDesc;

But really, I don't know the syntax of an SQL script. I don' t know what's necessary and what's not, etc...
Could someone help tie these codes and techniques together for me?
 
It looks like you have the SQL statement you need.

So, basically, as Sconly has said.


Code:
Dim strSQL as String
strSQL = "SELECT tblObjectTypeCodes.TypeDesc, Count(*) AS ObjectCount " & _
    "FROM MSysObjects LEFT JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type " & _
    "GROUP BY tblObjectTypeCodes.TypeDesc;"

Me.cboYourCombo.RowSource = strSQL

I don't know what event you are wanting to put this on, though.
 
Hey guys, what is that long SQL string doing? I can see it's linking to the table of object types, and it's grouping by type, but if the user just needs to find local Access non-system tables, why not use:
SELECT Name FROM MSysObjects WHERE Left(Name,4)<>'MSys' AND Type=1;
 
OK, I think I'm getting some of the SQL syntax.
I did not, however, realize you could create an SQL right in a procedure by creating it as a variable- that's good to know.
I've placed the code in the Form_Open procedure.

Mile-O-Phile:
When I put Sconly's code in (with your variable declaration), the code compiles, but when I try to select the combo box (in the actual form window), Access gives me a message: Type Mismatch in Expression. Why?

dcx693:
When I replace Sconly's code with yours, it works.
Unfortunately, it seems to effect some other code I have in my program. It compiles, but when I run it, there's an error message.
I've got two command buttons (OK and cancel) on the form, and the problem is with the OK button. The error message I get is:
"The report name 'My Report' you entered is misspelled or refers to a report that isn't open or doesn't exist."
(Note, this very same procedure worked great with your ADO-using code version, but I'm very interested in getting this to work, as it seems much simpler.)

Private Sub OK_Click()
Reports![My Report].RecordSource = ComboChooser <<--- COMPILER POINTS TO THIS LINE
DoCmd.Close
End Sub
 
You are obviously trying to open up a report. But doesn't your question ask how to populate the comb box with available tables?. The code fills the combo box with table names, not report names. If that's what you want, just replace Type=1 with Type=-32764
 
Well, actually, the report is already open.
The report opens up the form (with the combo box in it) by using a DoCmd.OpenForm command.
And yes, I'm trying to populate the combo box with available tables names (to use as the RecordSource in the report), so Type should remain =1, I believe.
It seems like your code should work...
Maybe Access has a tough time referencing properties of controls (and objects) outside of the actual object, as I'm trying to have it do by setting the value of [My Report].RecordSource equal to a control's value in the form.
Is there a way to transfer the value of my combo box to a global variable that is useable in all procedures?
That way, I could simply use that variable and set [My Report].RecordSource equal to it within the report.
 
Wait, wait wait.
Craziest thing- It works now.
Seems that any time you chage some code, the 1st time you try to run it, the error I previously described comes up.
But after that 1st run, the program works fine.
It's like Access needs to flush its memory in between the compilation and the 1st run and it's not doing that.
STILL,
Is there a way to use a global variable to pass between objects (form and report) like I was asking about before?
 

Users who are viewing this thread

Back
Top Bottom