Automating Microsoft Access with VBA

One thing that you can use these collections for is to retrieve lists of objects in the database. You might think that there's little need to do this, because the objects are right there in the Database window. But you'll find that working with your own lists of objects enables you to write friendlier user interfaces for people who find the Database window a bit daunting.

For example, you can use the AllForms collection to construct a general-purpose forms launcher for the sample database. Here's how:

1. Create a new form in Design view. Set the form's caption to Form list. Place a listbox control named lstForms and a command button control named cmdOpen on the form. Set the Row Source Type property of the listbox to Value List.

2. Open the form's module and add this code:

   Option Compare Database Option Explicit Private Sub cmdOpen_Click() ' Open the selected form If Not IsNull(lstForms.Value) Then DoCmd.OpenForm lstForms.Value End If End Sub Private Sub Form_Load() ' Stock the listbox with the names of ' all forms in the database Dim AO As AccessObject For Each AO In CurrentProject.AllForms lstForms.AddItem (AO.Name) Next AO End Sub

3. Save the form as FormList. Open the FormList form and it will list all the forms in the database. Select a form in the listbox and click the button to open the form, as shown in Figure 15.2.

   Figure 15.2. A form listing all the forms in the database.