Populate Combos with Database Objects

[martin DH]

Hello. I remember seeing a similar post but of course cannot find it now.

I'm creating a table and form to track enhancements to a database (when a new report is created, when a form is modified, etc). I wanted to have a combo box for each database object (table, query, form, etc) that lists the objects currently in those families - the point being that a user can select what object he/she just updated or created.

How can I populate these combos with the current list of objects, separated by family? Thank you for any help or showing me any related threads!

Martin

[ADezii]

Hello. I remember seeing a similar post but of course cannot find it now.

I'm creating a table and form to track enhancements to a database (when a new report is created, when a form is modified, etc). I wanted to have a combo box for each database object (table, query, form, etc) that lists the objects currently in those families - the point being that a user can select what object he/she just updated or created.

How can I populate these combos with the current list of objects, separated by family? Thank you for any help or showing me any related threads!

Martin

The Method that you can use depends on the Version of Access that you are currently working with. What is your Access version?

[Jim Doherty]

Hello. I remember seeing a similar post but of course cannot find it now.

I'm creating a table and form to track enhancements to a database (when a new report is created, when a form is modified, etc). I wanted to have a combo box for each database object (table, query, form, etc) that lists the objects currently in those families - the point being that a user can select what object he/she just updated or created.

How can I populate these combos with the current list of objects, separated by family? Thank you for any help or showing me any related threads!

Martin

Hi ya,

Depending what it is you are actually doing Martin, probably the easiest way for you is to query Access's MySysObjects system table. You can restrict whatever you want to see actually listed by applying various criteria to the 'Type' column or restrictive criteria to the 'Name' column and so on, the following SQL should give you one idea. Listings are not however dedicated/specific to particular user changes, if that is a requirement. I am sure others will contribute ways in code, by looping through object collections etc.

 
SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.Type) Not In (1,2,3,-32757,-32768,-32761,-32766)) AND (Left([Name],1)<>"~"))
ORDER BY MSysObjects.Type, MSysObjects.Name;

Regards

Jim :)

[martin DH]

My apologies, Access 2003 in XP.

An admin would simply select from the appropriate combo box the object they modified/created and this value will be stored in the table - that's all this would really entail. Thanks!

Martin

[missinglinq]

As Recordsources for your comboboxes:

Queries:

[CODE=sql] SELECT MSysObjects.Nam e FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Ty pe)=5 ORDER BY MSysObjects.Nam e;
[/CODE]
Forms:

[CODE=sql] SELECT MSysObjects.Nam e FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Ty pe)=-32768 ORDER BY MSysObjects.Nam e;[/CODE]

Tables:

[CODE=sql] SELECT MSysObjects.Nam e FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Ty pe)=1 ORDER BY MSysObjects.Nam e;
[/CODE]
Reports:

[CODE=sql] SELECT MSysObjects.Nam e FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Ty pe)= -32764 ORDER BY MSysObjects.Nam e;
[/CODE]
Modules:

[CODE=sql]SELECT MSysObjects.Nam e FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Ty pe)= -32761 ORDER BY MSysObjects.Nam e;
[/CODE]
Macros:

[CODE=sql] SELECT MSysObjects.Nam e FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Ty pe)= -32766 ORDER BY MSysObjects.Nam e;
[/CODE]
Linq ;0)>

[ADezii]

My apologies, Access 2003 in XP.

An admin would simply select from the appropriate combo box the object they modified/created and this value will be stored in the table - that's all this would really entail. Thanks!

Martin

Martin, I think this is just what the Doctor ordered, the code has thoroughly been tested, and is fully functional. Follow these 3 simple steps:

1. Create 7 Combo Boxes on your Form named:
   1. cboTables
   2. cboQueries
   3. cboForms
   4. cboReports
   5. cboPages
   6. cboMacros
   7. cboModules
2. Set the Row Source Type of all Combo Boxes = Value List. This step is critical.
3. Copy and Paste the following code to your Form's Open() Event:
   [CODE=vb]Private Sub Form_Open(Cance l As Integer)
   Dim aob As AccessObject
   
   With CurrentData
   For Each aob In .AllTables
   'Filter out System and Temporary Tables
   If Left$(aob.Name, 4) <> "MSys" And Left$(aob.Name, 1) <> "~" Then
   Me![cboTables].AddItem aob.Name
   End If
   Next aob
   For Each aob In .AllQueries
   Me![cboQueries].AddItem aob.Name
   Next aob
   End With
   
   With CurrentProject
   For Each aob In .AllForms
   Me![cboForms].AddItem aob.Name
   Next aob
   
   For Each aob In .AllReports
   Me![cboReports].AddItem aob.Name
   Next aob
   
   For Each aob In .AllDataAccessP ages
   Me![cboPages].AddItem aob.Name
   Next aob
   
   For Each aob In .AllMacros
   Me![cboMacros].AddItem aob.Name
   Next aob
   
   For Each aob In .AllModules
   Me![cboModules].AddItem aob.Name
   Next aob
   End With
   End Sub[/CODE]
4. Let me kow if this is what you wanted.

P.S. You can further Filter out Temporary Objects as in Line #7, if you so desire.