Solved Populate combo box with list of objects in current database

[Mike Krailo]

I have a subform used for updating version data and need to use a combo box that has as it's row source all the user objects in the database. So when the combo box is viewed it looks similar to the following screenshot. Right now all I have is a simple SQL to select Table info directly in the rowsource for that combo box. What I need in addition to that are the forms, reports, modules, etc... all the objects not including system objects.

I found many functions that list out those different objects individually as a debug.print to the immediate window but not the required SQL to do it all in one shot. Is there a way to do that all in SQL or am I going to have to create a temp table and populated with all the required info another way?

My first attempt at this is not working due to the addition of the ObjType column in the SQL string as shown below. The SQL works fine in the query designer grid but has a compile error in VBA code. EDIT: Ok, I figured out that the double quotes needed to be single quotes. My brain wasn't working this morning.

Here is the code. It just does a make table query.

Function CreateObjTbl()
   Dim StrSQL As String

   ' Empty the AllObjects table first
   StrSQL = "DELETE * FROM AllObjects;"
   DoCmd.RunSQL (StrSQL)

   StrSQL = "SELECT MSysObjects.Name, "Table" AS ObjType INTO AllObjects " _
          & "FROM MSysObjects " _
          & "WHERE (((MSysObjects.Name) Not Like 'f_*') AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=6) AND ((Left([Name],4))<>'MSys')) " _
          & "ORDER BY MSysObjects.Name;"

   DoCmd.RunSQL (StrSQL)
End Function

Apparently it doesn't like that "Table" AS ObjType part. I could make it into a parameter query like the following and get no syntax error but that's not what I want.

SELECT MSysObjects.Name, [Table] AS ObjType INTO AllObjects

Any idea's on how to proceed with this?

 

[theDBguy]

Hi. Maybe you could start with this and modify to your needs.

SELECT MSysObjects.Name, Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32761,"Module",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32768,"Form") AS [Object]
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*" And (MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like "f_*") AND ((MSysObjects.Type) In (1,5,-32761,-32764,-32766,-32768)))
ORDER BY MSysObjects.Type;

Hope it helps...

 

[Mike Krailo]

That's what I was looking for DBguy. Thank you for your quick help on this.

 

[theDBguy]

That's what I was looking for DBguy. Thank you for your quick help on this.

You're welcome. Good luck with your project.