Display the Names of Access Objects in a Listbox Control
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Display the Names of Access Objects in a Listbox Controlby Susan Sales Harkins
Application: Access 2000
Operating system: Windows
When you need to let users select database objects, you'll often want to provide a custom interface that's consistent with the rest of your application, rather than having them make selections using the Database window. Those of us outside of the Access development team may never learn the inner workings of the Database window, but the feature is fairly easy to simulate using Access 2000's CurrentProject and CurrentData objects. In the article "Streamline code with CurrentProject and CurrentData" in the December 2000 issue, we used CurrentProject and the AllReports collection to display a dynamic list of reports in a combo box. As we explained then, these new objects give you quick access to Access objects without using DAO. This month, we'll expand upon the previous technique a bit. We'll show you how to populate a control with a list of Access object names based on the object type a user selects. You can easily adapt and enhance our procedure to suit your own applications.
Our technique requires a combo box, a list box and an event procedure. Users select an object type from the combo box and code attached to the control's AfterUpdate event populates the list box with the appropriate object names. You can create the form in any database, but we'll create ours in the Northwind sample database.
Create the form
To get started, select Forms on the Objects bar and then click New on the Database Window toolbar. Next, double-click on Design View in the New Forms dialog box. Using Figure A as a guide, add a combo box and a list box to the blank form. Name the combo box cboObject and the list box lstList and change the accompanying labels appropriately. We'll use cboObject to identify the type of objects we want lstList to display.
Figure A: You'll select an object type from the combo box to control what's displayed
in the Object List list box.
Table A contains the additional properties you'll need to set for both controls. Once you've set all the properties, open the form's module by clicking the Code button on the Form Design toolbar. Select cboObject from the Object dropdown list and AfterUpdate from the Procedure dropdown list. Then, enter the code shown in Listing A at the insertion point. Finally, save your form as frmObject.Table A: Properties
|cboObject||Row Source Type||Value List|
"Macro";"Module";"Data Access Page"
|lstList||Row Source Type||Value List|
Listing A: Procedure to populate listbox
Dim frm As Form, ctl As ComboBox Dim strObject As String, aob As AccessObject, obj As Object Dim strTemp As String, strList As String Set frm = Forms!frmObject Set ctl = frm!cboObject strObject = ctl.ListIndex Select Case strObject Case acTable Set obj = CurrentData.AllTables Case acQuery Set obj = CurrentData.AllQueries Case acForm Set obj = CurrentProject.AllForms Case acReport Set obj = CurrentProject.AllReports Case acMacro Set obj = CurrentProject.AllMacros Case acModule Set obj = CurrentProject.AllModules Case acDataAccessPage Set obj = CurrentProject.AllDataAccessPages End Select On Error Resume Next For Each aob In obj strTemp = aob.Name strList = strList & strTemp & "; " Next lstList.RowSource = strList
How it works
It looks like there's a lot of code in our procedure, but the majority of it declares and defines variables. The first line of real interest is the
strObject = ctl.ListIndex
statement, which defines the variable strObject. Specifically, strObject will equal the index position of the selected item in its dropdown list. Remember that these lists are 0-based, so the first item in the list returns an index value of 0, the second item returns the value 1, and so on. The next section of code is a Select Case statement which relies on strObject--the index value of the selected item in cboObject--to set the AccessObject variable obj. The obj variable defines the object collection for the upcoming For...Each statement. The Select Case statement identifies the appropriate object, either CurrentProject or CurrentData and the specific collection necessary to list the appropriate object names in cboObject.
For instance, let's suppose you select Form from cboObject. Doing so returns a list index value of 2. Therefore the Select Case statement will execute the third (remember the 0-based index) Case action,
Case acForm Set obj = CurrentProject.AllForms
The Set statement defines obj as the CurrentProject. In addition, the code specifies the AllForms collection, which limits that list of objects to just form objects.
Why use intrinsic constants?
You may have noticed that we used intrinsic constants to identify each Case condition in our Select Case statement. Doing so isn't necessary for the code to work. We could use the objects' integer values, which are 0 through 6. However, the constants make the code more readable and therefore easier to debug and maintain. You'll notice that we also listed the Case conditions in consecutive order, by each constant's integer value. This order matches the order of each object type named in cboObject, the control that lists the different object types. The order isn't critical to the success of our code; we arranged it this way to be consistent.
Simple error handling
Before running the For Each...Next loop that actually lists all the objects of a particular type by name, we included an On Error statement. You could include a complex error handler and in fact, depending on the purpose of your form, you may need to. For our purposes, a simple Resume Next statement is sufficient. If the For statement encounters an object type that doesn't exist, Visual Basic for Applications (VBA) returns an error and the Resume Next statement captures that error and continues executing the code. As a result, when there's no object of a specific type, the list box simply remains empty and that should be clue enough for the user that there are no objects of the selected type. However, you may find that you want more control in your own applications--we'll leave additional error handling to you.
The For Each...Next loop
Now that we've set all the variables, we're ready to create the list of object names needed to fill lstList. If you read the December article on the AllReports collection, you may remember our For Each...Next loop. In a nutshell, VBA loops through once for each element in the identified group. In other words, if obj specifies the Forms collection, the For Each...Next loop repeats once for every form in the application. This includes both open and closed forms, but not unsaved forms.
Each loop returns the name of the current object (table, query, form, report and so on) while maintaining a list of the object names from the previous cycles. We'll be using this list as lstList's RowSource property, which requires a semicolon between each list item. Consequently, the code also concatenates a semicolon character after adding each object name to the string variable strList. Finally, the last statement in our procedure
lstList.RowSource = strList
sets the RowSource property to the list of names created by the For Each...Next statement.
Using the form
To see the form at work, click the View Microsoft Access button on the Visual Basic Editor's (VBE) Standard toolbar and then click the View button. Next, select an object type from the Object dropdown list. Let's suppose you select Form, as shown in Figure B. Selecting this item returns an index value of 2 and sets the obj variable to CurrentProject.AllForms. As a result, the For Each...Next loop creates a list of only form names and displays them in lstList, as shown in Figure C.
Figure B: Choose an object type from the cboObject's
Figure C: Our list box displays a complete list of the
Prevent system and hidden objects from being listed
Chances are you won't want to display system or hidden objects in your controls. However, our procedure, as is, displays both system and hidden objects. As you can see in Figure D, our list box displays tables that aren't typically visible in the Database window--system objects that are prefixed with MSys.
Figure D: Our form currently displays hidden system tables from the Northwind
You can keep these objects from appearing in the list box by substituting the For Each...Next loop shown in Listing A with the one shown in Listing B. This statement includes two If statements. The first checks the first four characters of each object's name. When the first four characters equal MSys, VBA skips the remainder of that loop, which means the current object's name isn't added to the strList.Listing B: Code to omit system and hidden objects
For Each aob In obj If Left$(aob.Name, 4) <> "MSys" Then If Not (Application. _ GetHiddenAttribute(aob.Type, aob.Name)) Then strTemp = aob.Name strList = strList & strTemp & "; " End If End If Next
If the first four characters aren't MSys, the second If checks each object's GetHiddenAttribute property. If the property returns False, the object isn't hidden and VBA executes the string statements, which will add the current object to the variable strList. If the property is True, VBA ignores the remainder of the For Each statement without adding the current object's name to strList. As you can see in Figure E, only the tables that are normally visible in Northwind's database table are displayed after we change the loop.
Figure E: If you want to prevent hidden or system objects from appearing in the list
box, substitute the For Each...Next loop in your code with the one shown in
Limitations and warnings
Because we're dealing with collections, we can't easily control the order of the object names. The position of objects within a collection changes as we modify our application. Therefore, the order of these lists will change without warning. In addition, the items aren't sorted in any kind of order. Also keep in mind that there's a limit on just how long a control's RowSource property can be, and that limit is 2,048 characters. If your application contains many objects of the same type, or if your object names are extremely long, you may find this technique isn't adequate.
Finally, the code in Listing B is unaffected by the database's View options. So, even if a user pulls down the Tools menu, chooses Options and selects the Hidden Objects and System Objects check boxes in the Show section, the form still won't display those items. This may or may not be what you want, so keep this in mind when you're using these techniques in your own applications.
Our technique for creating lists of objects by object type takes advantage of the new Access 2000 CurrentProject and CurrentData objects and their collections. While today's example requires a fair amount of code, it's simple to understand and apply, thanks to the new objects.
Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.