By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Populate Combos with Database Objects

100+
P: 114
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
Dec 10 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,669
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?
Dec 10 '07 #2

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate
  3. FROM MSysObjects
  4. WHERE (((MSysObjects.Type) Not In (1,2,3,-32757,-32768,-32761,-32766)) AND (Left([Name],1)<>"~"))
  5. ORDER BY MSysObjects.Type, MSysObjects.Name;
  6.  
  7.  
Regards

Jim :)
Dec 10 '07 #3

100+
P: 114
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
Dec 10 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
As Recordsources for your comboboxes:

Queries:

Expand|Select|Wrap|Line Numbers
  1.   SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
  2.  (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;
  3.  
Forms:

Expand|Select|Wrap|Line Numbers
  1.   SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
  2.  (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;
Tables:

Expand|Select|Wrap|Line Numbers
  1.   SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
  2.  (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
  3.  
Reports:

Expand|Select|Wrap|Line Numbers
  1.   SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
  2.  (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;
  3.  
Modules:

Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
  2.  (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;
  3.  
Macros:

Expand|Select|Wrap|Line Numbers
  1.   SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
  2.  (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;
  3.  
Linq ;0)>
Dec 10 '07 #5

ADezii
Expert 5K+
P: 8,669
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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Open(Cancel As Integer)
    2. Dim aob As AccessObject
    3.  
    4. With CurrentData
    5. For Each aob In .AllTables
    6.   'Filter out System and Temporary Tables
    7.   If Left$(aob.Name, 4) <> "MSys" And Left$(aob.Name, 1) <> "~" Then
    8.     Me![cboTables].AddItem aob.Name
    9.   End If
    10. Next aob
    11.   For Each aob In .AllQueries
    12.     Me![cboQueries].AddItem aob.Name
    13.   Next aob
    14. End With
    15.  
    16. With CurrentProject
    17.   For Each aob In .AllForms
    18.     Me![cboForms].AddItem aob.Name
    19.   Next aob
    20.  
    21.   For Each aob In .AllReports
    22.     Me![cboReports].AddItem aob.Name
    23.   Next aob
    24.  
    25.   For Each aob In .AllDataAccessPages
    26.     Me![cboPages].AddItem aob.Name
    27.   Next aob
    28.  
    29.   For Each aob In .AllMacros
    30.     Me![cboMacros].AddItem aob.Name
    31.   Next aob
    32.  
    33.   For Each aob In .AllModules
    34.     Me![cboModules].AddItem aob.Name
    35.   Next aob
    36. End With
    37. End Sub
  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.
Dec 10 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.