Objects are the components of a database. They include the tables, queries, forms, reports, macros, and modules that appear in the database window. They also include the controls (text boxes, list boxes, and so on) on a form or report. The key to successful programming lies in your ability to manipulate the database objects by using VBA code at runtime. It's also very useful to be able to add, modify, and remove application objects at runtime.
An overview of the superficial Access Object Model is shown in Figure 10.1. At the top of the model, you can see the Application object, which refers to the active Access application. It contains all of Access's other objects and collections, including the Forms collection, the Reports collection, the Modules collection, the Screen object, and the DoCmd object. The Application object can be used to modify the properties of, or execute commands on, the Access application itself, such as specifying whether Access's built-in toolbars are available while the application is running.
The Forms collection contains all the currently open forms in the database. Using the Forms collection, you can perform an action, such as changing the color, on each open form.
Note
The Forms collection isn't the same as the list of all forms in the database; that list is part of the Documents collection of the Forms container object. The Forms container object is discussed in Chapter 15, “What Are Data Access Objects and Why Are They Important?”
Just as the Forms collection contains all the currently open forms, the Reports collection contains all the currently open reports. Using the Reports collection, you can perform an action on each open report.
The Modules collection contains all the standard and class modules that are open. All open modules are included in the Modules collection, regardless of whether they're compiled and whether they contain code that's currently running.
The Screen object can be used to refer to the form, report, or control that has the focus. The Screen object contains properties that refer to the active form, active report, active control, and previous control. Using these properties, you can manipulate the currently active form, report, or control, as well as the control that was active just before the current control. If you try to refer to the Screen object when no form or report is active, a runtime error occurs.
The DoCmd object is used to perform macro commands or Access actions from VBA code; it's followed by a period and the name of an action. Most of the DoCmd actions—the OpenQuery action, for example—also require arguments. The OpenQuery action is used to execute an Access query. It receives the following arguments:
Query Name: The name of the query you want to execute
View: Normal, design, or preview
Data Mode: Add, edit, or read only
Here's an example of the OpenQuery action of the DoCmd object:
DoCmd.OpenQuery "qryCustomers", acNormal, acReadOnly
The OpenQuery action is performed by the DoCmd object. The first argument, the query name, is "qryCustomers". This is the name of the query that's opened in Datasheet view (rather than Design view or Print preview). It's opened in read-only mode, meaning the resulting data can't be modified.
Many people, especially those used to a procedural language, don't understand the concept of objects, properties, and events. You need a thorough knowledge of Access's objects, their properties, and the events each object can respond to if you want to be a productive and successful Access programmer.
As mentioned earlier in this chapter, objects are all the things that make up your database. They include tables, queries, forms, reports, macros, and modules, as well as the components of those objects. For example, a Table object contains Field and Index objects. A Form contains various controls (text boxes, combo boxes, list boxes, and so on). Each object in the database has specific properties that determine its appearance or behavior. Each object also has specific methods, which are actions that can be taken on it.
A property is an attribute of an object, and each object has many properties. Often, different types of objects share the same properties; at other times, an object's properties are specific to that particular object. Forms, combo boxes, and text boxes all have Width properties, for example, but a form has a RecordSource property that the combo box and text box don't have.
Most properties can be set at design time and modified at runtime; however, some can't be modified at runtime, and others can't be accessed at design time (just modified at runtime). Access's built-in Help for each property tells you one of the following:
You can set the property in the object's property sheet, a macro, or Visual Basic.
You can set this property only in Design view.
You can access this property by using Visual Basic or a macro.
Each of these descriptions indicates when the property can be modified.
As a developer, you set the values of many objects' properties at design time; the ones you set at design time are the starting values at runtime. Much of the VBA code you write modifies the values of these properties at runtime in response to different situations. For example, a text box has a Visible property. If a client is paying by cash, you might not want the text box for the credit-card number to be visible. If they're paying by credit card, you might want to set the Visible property of the text box with the credit-card number to True. This is just one of the many things you can do to modify the value of an object's property at runtime in response to an event or action that has occurred.
You might wonder how you can determine all the properties associated with a particular object (both those that can be modified at design time and those that can be modified at runtime). Of course, to view the properties that can be set at design time, you can select the object and then view its property sheet. Viewing all the properties associated with an object is actually quite easy to do; just invoke Help by pressing F1. Click the Index tab in the Help Topics dialog box and type the name of the object whose properties you want to view. In Figure 10.2, combo boxes has been typed into the text box. Notice that one of the entries in the list box at the bottom of the dialog box is “properties.” If you double-click on that entry, the Topics Found dialog box opens. (See Figure 10.3.) When you select Combo Box Control Properties, you're given a complete list of properties associated with a combo box. (See Figure 10.4.) To find out about a specific property, click the property you want to view.
Windows is an event-driven operating system; in other words, the operating system responds to many events that are triggered by actions the user takes and by the operating system itself. Access exposes many of these events through its Object Model. An event in an Access application is something your application can respond to. Events include mouse movements, changes to data, a form opening, a record being added, and much more. Users initiate events, as does your application code. It's up to you to determine what happens in response to the events that are occurring. You respond to events by using macros or VBA code. Each Access object responds to different events. If you want to find out all the events associated with a particular object, take the following steps:
Select the object (for example, a text box).
Open the Properties window.
Click the Event tab, shown in Figure 10.5.
Scroll through the available list of events.
Methods are actions that can be taken on the objects in your database. As with properties and events, different objects have different methods associated with them. A method is like a function or subroutine, except that it's specific to the object it applies to. For example, a form has a GoToPage method that doesn't apply to a text box or any other object. If you search for help on methods, you see that you can get help on reference topics. (See Figure 10.6.) If you select methods, reference topics, a Methods Reference appears. (See Figure 10.7.) When a particular method is selected, you get specific help on that method and how it's used. (See Figure 10.8.) If you click the “Applies To hypertext” entry, you get a list of all objects the selected method applies to. You can click the “Example hypertext” entry to view sample code that includes an appropriate implementation of the selected method.
The Object Browser is a powerful tool that can help you learn about and work with the objects that are part of both Access 97 and the Microsoft Windows environment. The Object Browser displays information about Microsoft Access and other objects and can help you with coding by showing you all the properties and methods associated with a particular object.
Access objects are complex—they have many properties and methods. The Object Browser helps you to understand and use objects, properties, and methods by doing the following:
Displaying the types of objects available
Allowing you to quickly navigate between application procedures
Displaying the properties and methods associated with a particular object
Finding and pasting code into your application
The Object Browser can easily be invoked from the Module window. You can click the Object Browser button on the toolbar, press F2, or choose View | Object Browser. The dialog box shown in Figure 10.9 appears.
The Object Browser displays two levels of information about the selected library or database. If you select the CHAP10.MDB database from this book's CD-ROM, your screen will look similar to Figure 10.9. The Classes list box displays all modules, including Form and Report modules, in the database. The “Members of” list box displays any procedures that have been defined in the selected module. Notice the basUtils module, which is part of the CHAP10.MDB database. Looking at the list box on the right, you can see the functions included in the basUtils module. You can click to select each Form and Report module in the list box on the left and view the associated methods and properties in the list box on the right.
You can use the Project/Library drop-down list to select a different object library. The Classes list box displays the types of objects available in the selected library or database. The “Members of” list box displays the methods, properties, and data elements defined for the selected object. (See Figure 10.10.) The Access item has been selected from the Library combo box, so the list box on the left shows all of Access 97's classes. The list box on the right shows all the members of the selected object—in this case, the Application object. You can even add other libraries to the Library drop-down list by referring to other type libraries. This method is covered in Chapter 25, “Automation: Communicating with Other Applications.”
After you have located the method or property you're interested in, you have the option of pasting it into your application. With the member selected in the list box, simply click the Copy button in the Object Browser, then paste it in the appropriate module. If you want to get more information about a particular method or property, just click the Help button in the Object Browser or press F1.
Access objects are categorized into collections, which are groupings of objects of the same type. The Forms collection, for example, is a grouping of all the open forms in a database. Each form has a Controls collection that includes all the controls on that form. Each control is an object, and you must refer to an object through the collection it belongs to. For example, you refer to a form through the Forms collection. VBA offers three ways to refer to an object; if you want to refer to the frmProjects form, for example, you can choose from the following options:
Forms!frmProjectsForms("frmProjects")Forms(0)
Referring to the form as Forms(0) assumes that frmProjects was the first form opened. However, you need to understand that although an element number is assigned as each form is loaded, this element number changes as forms are loaded and unloaded at runtime. For example, the third form that's loaded can initially be referred to as “element two,” but if the second form is unloaded, that third form becomes element one. In other words, you can't rely on the element number assigned to a form; that number is a moving target.
You must refer to a control on a form first through the Forms collection, then through the specific form. The reference looks like this:
Forms!frmProjects!txtClientID
In this example, Forms is the name of the collection, frmProjects is the name of the specific form, and txtClientID is the name of a control on the frmProjects form. If this code is found in the Code module of frmProjects, it could be rewritten like this:
Me!txtClientID
Me refers to the current form or report. It's generic because the code could be copied to any form having a txtClientID control and it would still run properly. Referring to a control on a report is very similar to referring to a control on a form. Here's an example:
Reports!rptTimeSheet!txtHoursBilled
This example refers to the txtHoursBilled text box on the rptTimeSheet report, part of the Reports collection. After you know how to refer to an object, you're ready to write code that modifies its properties and executes its methods.
To modify an object's properties and execute its methods, you must refer to the object and then supply an appropriate property or method, as shown in this example:
Forms!frmHello!cmdHello.Visible = False
This line of code refers to the Visible property of cmdHello, found in the frmHello form, which is in the Forms collection. Notice that you must identify the object name “frmHello” as being associated with the Forms collection. If you want to change the Caption property of frmHello to say "Hello World", you would use the following code:
Forms!frmHello.Caption = "Hello World"
Tip
You might be confused about whether you're looking at an object's property or method, but there are a couple of quick ways to tell. A property is always used in some type of an expression. For example, you might be setting a property equal to some value:
Forms!frmClients!txtAddress.Visible = False
Here, you're setting the Visible property of the txtAddress text box on the frmClients form from True to False. You also might retrieve the value of a property and place it in a variable:
strFirstName = Forms!frmClients!txtFirstName.Value
You also might use the value of a property in an expression, as in the following example:
MsgBox Forms!frmClients!txtFirstName.Value
The pattern here is that a property is always used somewhere in an expression. It can be set equal to something, something can be set equal to its value, or it's otherwise used in an expression.
A method, however, is an action taken on an object. The syntax for a method is Object.Method. A method isn't set equal to something, nor is anything set equal to it. A method looks like this:
Forms!frmHello!txtHelloWorld.SetFocus
In this example, the SetFocus method is being executed on the text box called txtHelloWorld.
A method that returns an object variable looks like this:
Dim cbr As CommandBar
Set cbr = CommandBars.Add("MyNewCommandBar")In this example, the CommandBars collection Add method is used to set the value of the CommandBar object variable named cbr. For more information, see the section “Declaring and Assigning Object Variables” later in this chapter
Note
Many people are confused about when to use a bang (!) and when to use a period. You should use a bang whenever you're separating an object from its collection, as shown in these two examples:
Forms!frmClients Forms!frmClients!txtClientID
In the first example, frmClients is part of the Forms collection. In the second example, txtClientID is part of the Controls collection of the frmClients form.
The period is used to separate an object from a property or method, like so:
Forms!frmClients.RecordSource = "tblClients" Forms!frmClients!txtClientID.Visible = False
The first example sets the RecordSource property of frmClients to tblClients, and the second example sets the Visible property of the txtClientID on the frmClients form to False.
Many people are in the habit of separating objects from their collections by using periods rather than bangs. However, following the standard of using bangs to separate objects from their collections and periods to separate a property or method from an object makes your code much easier to read. Furthermore, when using data access objects (covered in Chapter 15), you must include the Microsoft DAO 2.5/3.5 Compatibility Layer if you want backward compatibility for the Collection.Object syntax of referring to objects.
Each object has a default property, and if you're working with an object's default property, you don't have to explicitly refer to it in code. Take a look at the following two code samples:
Forms!frmHello!txtHello.Value = "Hello World" Forms!frmHello!txtHello = "Hello World"
The Value property is the default property of a text box, so you don't need to explicitly refer to it in code. However, I prefer to always explicitly state the property—this practice contributes to the code's readability and keeps novice Access programmers who work with my code from having to guess which property I'm changing.
Object variables are variables that reference an object of a specific type, such as databases, record sets, forms, controls, and even objects created in other applications. They allow you to create shortcut references to objects and pass objects to subroutines and functions. You can use them to streamline code by using short names to refer to objects with long names and to optimize code by supplying a direct pointer to a particular object.
First, you must declare an object variable; then, you assign—or point—the object variable to a particular object, as shown in the following code:
Private Sub cmdChangeCaption_Click()
Dim cmdAny As CommandButton
Set cmdAny = Me!cmdHello
cmdAny.Caption = "Hello"
End SubThis code creates an object variable called cmdAny of the type CommandButton. You then use the Set statement to point your CommandButton object variable toward the cmdHello object on the current form, using the Me keyword. Finally, you modify the caption of the cmdAny object variable. Because an object variable is a reference to the original object, you're actually changing the caption of the cmdHello command button.
The difference between object variables and regular variables is illustrated by the following code:
Dim intVar1 As Integer Dim intVar2 As Integer intVar1 = 5 intVar2 = intVar1 intVar1 = 10 Debug.Print intVar1 'Prints 10 Debug.Print intVar2 'Prints 5
This code uses ordinary variables. When you dimension these variables, each one is assigned a separate memory location. Although intVar2 is initially assigned the value of intVar1, changing the value of intVar1 has no effect on intVar2. This differs from the following code, which uses an object variable:
Private Sub Command5_Click()
Dim ctlText As TextBox
Set ctlText = Forms!frmSales!txtProductID
ctlText.Text = "New Text"
Debug.Print Forms!frmSales!txtProductID.Text 'Prints New Text
End SubThis routine creates an object variable called ctlText of type TextBox. It then associates the object variable with Forms!frmSales!txtProductID. Next, it modifies the Text property of the object variable. Because the object variable is actually pointing to the text box on the form, the Debug.Print statement prints the new text value.
Access supports the use of generic object variables, including Application, Control, Form, and Report. Generic object variables can be used to refer to any object of that generic type:
Private Sub ChangeVisible_Click()
Dim ctlAny As Control
Set ctlAny = Me!txtCustomerID
ctlAny.Visible = False
End SubIn this example, ctlAny can be used to point to any control. Compare that with the following code:
Private Sub cmdChangeVisible_Click()
Dim txtAny As TextBox
Set txtAny = Me!txtCustomerID
txtAny.Visible = False
End SubHere, your object variable can be used only to point to a text box.
Many people get confused about the differences between an object and a collection. Think of an object as a member of a collection. For example, frmHello is a form that's a member of the Forms collection; cmdHello, a command button on frmHello, is a member of the Controls collection of frmHello. Sometimes you want to manipulate a specific object, but other times you want to manipulate a collection of objects.
You have already learned quite a bit about manipulating a single object, such as setting the Enabled property of a text box:
txtCustomerID.Enabled = False
This line of code affects only one text box and only one of its properties. However, when you're manipulating a single object, you might want to affect several properties at the same time. In that case, it's most efficient to use the With...End With construct, explained in the following section.
One method you can use to modify several properties of an object is to modify the value of each property, one at a time:
txtCustomerID.Enabled = False txtCustomerID.SpecialEffect = 1 txtCustomerID.Fontsize = 16 txtCustomerID.FontWeight = 700
Contrast this with the following code:
With txtCustomerID .Enabled = False .SpecialEffect = 1 .Fontsize = 16 .FontWeight = 700 End With
This code uses the With...End With statement to assign multiple properties to an object. In addition to improving the readability of your code, the With...End With construct results in a slight increase in performance.
A collection is like an array of objects. What makes the array special is that it's defined and maintained by Access. Every collection in Microsoft Access is an object, each with its own properties and methods. The VBA language makes it easy for you to manipulate Access's collections of objects; you simply use the For Each...Next construct, covered in the following section.
In the “Determining the Type of a Control” section of this chapter, you learn how to loop through the collection of controls on a form, performing actions on all the command buttons. This illustrates a practical use of a collection. In the following example, you loop through all the open forms, changing the caption of each form:
Sub FormCaptions()
Dim frm As Form
For Each frm In Forms
frm.Caption = frm.Caption & " - " & CurrentUser
Next frm
End SubThis routine uses the For...Each construct to loop through each form in the Forms collection, setting the caption of each form to the form's caption concatenated with the current user name. As you travel through the loop, the code frm.Caption refers to each individual member of the Forms collection.
The Jet Engine maintains information about all the components of an Access 97 database. This information is stored in both container and document objects. Access 97 ships with eight containers: Databases, Forms, Modules, Relationships, Reports, Scripts, Tables, and SysRel. Every container object is made up of a collection of documents, which are the instances of the objects in the container.
It's easy to confuse the Forms container with the Forms collection. The Forms container comprises all the saved forms that are part of the database; the Forms collection comprises only the forms currently running in memory. If you want to see a list of all the forms that make up a database, you must use the Forms container, but if you want to change the caption of all the open forms, you must use the Forms collection.
To view all the forms stored in the database, you must loop through the Forms container and list the documents, as shown in the following code:
Sub FormList()
Dim db As DATABASE
Dim cnt As Container
Dim doc As Document
Set db = CurrentDb()
Set cnt = db.Containers("Forms")
For Each doc In cnt.Documents
Debug.Print doc.Name
Next doc
End SubThis code creates a database object variable and a container object variable. It uses the CurrentDB() function (discussed in Chapter 15) to point the database object variable to the current database. It then points the Container object variable to the Forms container. Next, it uses the Document object to loop through each document in the Documents collection of the Forms container and print out the name of each form in the database.
You can create custom collections in addition to the built-in collections. Custom collections are similar to arrays, but they offer several advantages:
Collections are dynamically allocated. They take up memory based only on what's in them at a given time. This is different from arrays, whose size must be either predefined or redimensioned at runtime. When an array is redimensioned, Access actually makes a copy of the array in memory, taking up substantial resources. By using custom collections, you can avoid that.
A collection always knows how many elements it has, and elements can easily be added and removed.
Each element of a collection can contain a different type of data.
Elements can be added into any element of a collection.
Defining a custom collection is easy—simply use the Dim keyword to create an object of the type Collection, as shown here:
Dim colSports As New Collection
The Dim statement tells the compiler you want to declare a variable, and the As New keywords indicate that you're creating a new instance of something. Specifically, you're creating a new instance of a Collection object. The New keyword is covered in more detail later in the chapter. For now, take a look at how you can add items to, and remove items from, a custom collection.
The Add method adds a new item to a custom collection. It looks like this:
colSports.Add "Basketball"
This line of code adds the text "Basketball" to the colSports collection. The Add method has three optional arguments: Key, Before, and After. Key is a string name you can use to uniquely identify an element; the Before and After arguments allow you to specify where in the collection the new item will be placed. Here's an example:
Sub NewCollection() Dim colSports As New Collection colSports.Add "Basketball" colSports.Add "Skiing" colSports.Add "Skating", Before:=1 colSports.Add "Hockey", After:=2 End Sub
This code creates a new collection called colSports and adds two consecutive elements to the collection: Basketball and Skiing. It then adds Skating before Basketball. Skating becomes Element 1 and Basketball becomes Element 2. Finally, it adds Hockey after Element 2 (Basketball).
Warning
Unlike almost every other array or collection in VBA, custom collections are one-based rather than zero-based. This is a big change if you're used to relying on arrays and collections being zero-based.
Removing objects from a custom collection is just as easy as adding them. You use the Remove method, which looks like this:
Sub RemoveElements() Dim colSports As New Collection colSports.Add "Basketball" colSports.Add "Skiing" colSports.Add "Skating" colSports.Add "Hockey" colSports.Remove 2 End Sub
This routine removes Element 2 (Skiing) from the collection.
Just as you can loop through built-in collections, you can also loop through a custom collection. The code looks like this:
Sub LoopThroughCollection()
Dim colSports As New Collection
Dim varSport As Variant
colSports.Add "Basketball"
colSports.Add "Skiing"
colSports.Add "Skating", Before:=1
colSports.Add "Hockey", After:=2
For Each varSport In colSports
Debug.Print varSport
Next varSport
End SubThis code uses a For Each...Next loop to loop through each element of colSports. Notice that the routine declares a variant variable as the type of object in the collection. This is done so that different types of values can be stored in each object in the collection. Access refreshes any collection when you first open a database and specifically refer to it.
When you add an item to a collection, you can specify a custom key for the object. This makes it easy to return to the item in the collection whenever necessary. The code that follows illustrates how to specify a custom key:
Sub CustomKey()
Dim colSports As New Collection
colSports.Add "Basketball", "B"
colSports.Add "Skiing", "S1"
colSports.Add "Skating", "S2"
colSports.Add "Hockey", "H"
Debug.Print colSports.Item("S1")
End SubThis code adds several items to the colSports collection. As each item is added, it's assigned a unique key. Each item in the collection can then be easily accessed by using its unique key. The Item method is often used when adding several instances of a form, such as a Customer form to a collection. The Customer ID of each customer is added as the unique key for each form in the collection. This unique identifier allows you to readily return to a specific instance of the Customer form. This technique is illustrated in the TrackInstances procedure found in the section “Returning to a Unique Item in a Collection” later in this chapter.
The New keyword creates a new instance of an existing object. For example, you might want to display information about several customers, each customer on its own form, so you want each instance of the Customer form to contain the same objects, variables, and code. You might also want to create multiple instances of a form called frmSales; each instance would show information about a different customer.
There are a couple of ways you can use the New keyword. The first way looks like this:
Dim frmNew As New Form_frmCustomers frmNew.Visible = True
This method creates a new instance of frmCustomers, using an object variable called frmNew. It then sets the Visible property of the new instance to True.
In the second method, you create an object variable of the specific type of your form, then point the object variable to a new instance of the form:
Dim frmNew As Form_frmCustomers Set frmNew = New Form_frmCustomers frmNew.Visible = True
Notice that you first create an object variable specifically of the type frmCustomers, then point the object variable to a new instance of frmCustomers. The object variables used to hold the new instances of the form have a scope just like any other variable. This means that if you place the object variable in a procedure, the variable goes out of scope as soon as the Exit Sub or Exit Function is reached, causing the new instance of the form to vanish. For this reason, object variables that hold new instances of forms are generally module-level or public in scope. Here's an example (frmMultiInstance from CHAP10EX.MDB):
Private colForms As New Collection Private Sub cmdNewInstance_Click() Static intCounter As Integer Dim frm As Form Set frm = New Form_frmMultiInstance intCounter = intCounter + 1 colForms.Add frm frm.Caption = "New Instance " & intCounter frm.Visible = True End Sub
The Private declaration for the collection called colForms is placed in the General Declarations section of frmMultiInstance. The cmdNewInstance_Click event routine maintains a Static variable that's incremented each time the command button is clicked. A Local form variable called frm is declared and set equal to a new instance of the frmMultiInstance form. The new instance is added to the Private collection; next, its caption is set and its Visible property is set equal to True. This example illustrates two important points:
Each instance of the form maintains its own Static variable, so if you create more than one instance from the same parent, the number in the title increments. If you create a child form from one of the new instances, the new instance increments and maintains its own Static variable.
All the child forms close if you close the original parent because the collection called colForms is Private to frmMultiInstance.
Both of these “idiosyncrasies” are remedied in the following section, after you learn to pass objects to functions and subroutines.
One of the beauties of object variables is that you can easily pass them to a subroutine or function. They are always passed by reference, meaning that when you change the object's property in a routine, you're changing the property in the original example, too. As you will see, the ability to pass an object to a function or subroutine solves the problems created when building multiple instances of a form in the preceding example. The following code is found in CHAP10EX.MDB in the frmPassedToSub form and the basExamples module:
Private Sub cmdNewInstance_Click() Call NewInstance(Me) End Sub
The cmdNewInstance command button, found in frmPassedToSub, passes the current form (Me) to a routine called NewInstance (found in basExamples). The NewInstance routine looks like this:
Private mcolForms As New Collection Private mintCounter As Integer Sub NewInstance(frmAny As Form) Dim frm As Form Set frm = New Form_frmPassedToSub mintCounter = mintCounter + 1 mcolForms.Add frm frm.Caption = "New Instance " & mintCounter frm.Visible = True End Sub
The techniques you've learned so far don't allow you to return to a specific item in a collection. Using the Collection object's Key property, covered earlier in the chapter, you can easily return to a specific item in a collection. The code that follows illustrates this useful technique found in the frmTrackInstances form:
Private Sub cmdMore_Click()
Call LocateInstance
End Sub
Sub LocateInstance()
On Error Resume Next
mcolForms.Item("" & PaymentID & "").SetFocus
If Err.Number Then
Call TrackInstances
End If
End SubThe mcolForms is a Private collection declared in the General Declarations section of the frmTrackInstances form. The LocateInstance routine tries to set focus to a specific form in the mcolForms custom collection. If an error occurs, no instance exists for the selected payment. The TrackInstances routine that follows is then called:
Sub TrackInstances()
On Error GoTo Track_Err
Dim frm As Form_frmPmtInfo, colItem As Object
Const conKeyInUseErr = 457
Set frm = New Form_frmPmtInfo
mcolForms.Add Item:=frm, key:=PaymentID & ""
frm.Caption = "Payment ID " & PaymentID
frm.RecordSelectors = False
frm.NavigationButtons = False
frm.RecordSource = "Select * from tblPayments Where " & _
"tblPayments!PaymentID = " & Me!PaymentID & ";"
frm.Visible = True
Track_Bye:
Exit Sub
Track_Err:
If Err = conKeyInUseErr Then
mcolForms.Remove (PaymentID)
Resume
Else
Resume Track_Bye
End If
End SubThe TrackInstances routine creates a new instance of the frmPmtInfo form. As each instance is created, the subroutine adds the PaymentID of the selected record to the new element of the collection. This unique key is used by the LocateInstance routine called by the Click event of the cmdMore command button. When the user clicks the cmdMore command button, the LocateInstance routine tries to set focus to the form in the collection containing the unique key. If the user closes one of the pop-up forms and then tries to open it again by clicking the More button, code in the TrackInstances procedure error handler removes the item from the collection before trying to again add it to the collection.
When writing generic code, you may need to determine the type of a control. For example, you might want to loop through all the controls on a form and flip the Enabled property of all the command buttons. To do this, use the TypeOf keyword, which is actually part of the If statement. Here's an example of how it's used (you can find this in CHAP10EX.MDB in the module called basExamples):
Sub FlipEnabled(frmAny As Form, ctlAny As Control)
Dim ctl As Control
'Loop through the Controls collection using the For..Each Construct
ctlAny.Enabled = True
ctlAny.SetFocus
For Each ctl In frmAny.Controls
'Evaluate the type of the control
If TypeOf ctl Is CommandButton Then
'Make sure that we don't try to disable the command button _
that invoked this routine
If ctl.Name <> ctlAny.Name Then
ctl.Enabled = Not ctl.Enabled
End If
End If
Next ctl
End SubThe FlipEnabled procedure is called from the form frmTypeOf. Each command button on the form (Add, Edit, Delete, and so on) sends the form and the name of a control to the FlipEnabled routine:
Private Sub cmdAdd_Click() Call FlipEnabled(Me, Me!cmdSave) End Sub
The FlipEnabled routine receives the form and control as parameters. It begins by enabling the command button that was passed to it and setting focus to it. The FlipEnabled routine then uses the VBA construct For...Each to loop through all the controls on a form. The For...Each construct repeats a group of statements for each object in an array or collection—in this case, the Controls collection. The code evaluates each control on the form to determine whether it's a command button. If it is, and it isn't the command button that was passed to the routine, the routine flips the control's Enabled property. The following VBA keywords are specific object types that can be evaluated by the TypeOf statement:
BoundObjectFrame | OptionButton | Label |
CheckBox | OptionGroup | PageBreak |
ComboBox | Rectangle | Image |
CommandButton | Subform | CustomControl |
Chart | Subreport | UnboundObjectFrame |
Line | TextBox | Tab |
ListBox | ToggleButton | Page |
Besides using the TypeOf statement described previously, you can also use a control's ControlType property to determine what kind of control it is.
VBA offers the convenience of performing actions on the active control, the active form, and other specially recognized objects. The following is a list of special properties that refer to objects in the Access Object Model:
The ActiveControl property refers to the control that has focus on a screen object, form, or report.
The ActiveReport property refers to the report that has focus.
The Form property refers to the form that a subform is contained in or to the form itself.
Me refers to the form or report where code is currently executing.
The Parent property refers to the form, report, or control that contains a control.
PreviousControl refers to the control that had focus immediately before the ActiveControl.
RecordsetClone refers to a clone of the form's underlying recordset.
The Report property refers to the report that a subform is contained in or to the report itself.
The Section property refers to the section in a form or report where a particular control is located.
The following example of using the Screen.ActiveForm property shows how a subroutine can change the caption of the active form:
Sub ChangeCaption()
Screen.ActiveForm.Caption = Screen.ActiveForm.Caption & _
" - " & CurrentUser()
End SubThis subroutine modifies the caption of the active form, appending the value of the CurrentUser property onto the end of the existing caption.
Objects are used throughout the Time and Billing application. Almost every aspect of the application uses the skills explained in this chapter. The examples that follow apply the techniques you learned to open multiple instances of the Projects form and to enable and disable command buttons in response to the user's making changes to the data on the frmClients form.
While viewing a project, a user might want to see information from another project. This can be done by creating multiple instances of the Projects form. The cmdViewOtherProjects command button on the frmProjects form calls the CreateInstances routine:
Private Sub cmdViewOtherProjects_Click()
Call CreateInstances(Me)
End SubThe CreateInstances routine is a generic routine that can create multiple instances of any form. It's found in basUtils and looks like this:
Sub CreateInstances(frmAny As Form) Dim frm As Form Set frm = New Form_frmTimeCards mintCounter = mintCounter + 1 mcolForms.Add frm frm.Filter = frmAny.Filter frm.Visible = True End Sub
The CreateInstances routine receives a reference to the form that was passed to it. It then dimensions a form object variable and sets it equal to a new instance of the frmTimeCards form. It increments a Private variable (mintCounter) and adds the form to a Private custom collection called mcolForms. The Filter of the new instance is set to match the filter of the instance that the form came from. This ensures that the new instance shows only projects associated with the client selected on the frmClients form. Finally, the new instance of the form is made visible.
When a user is in the middle of modifying form data, there's really no need for him or her to use the Project, Time Cards, Payments, and Invoice portions of the application. It makes sense to disable these features until the user has opted to save the changes to the Client data. The “clean” form begins with the Project, Time Cards, Payments, and Invoice command buttons enabled and the Save button disabled. The KeyPreview property of the form is set to Yes so that the form previews all keystrokes before the individual controls process them. The KeyDown event of the form looks like this:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If Not cmdSave.Enabled Then
If ImportantKey(KeyCode, Shift) Then
Call FlipEnabled(Me, ActiveControl)
End If
End If
End SubThe KeyDown event automatically receives the code of the key that was pressed and whether Shift, Alt, or Ctrl was pressed along with that key. The event routine checks to determine whether the Save button is already enabled. If it is, there's no reason to continue; the Enabled property of the command buttons has already been flipped. If Save isn't already enabled, the ImportantKey function is called. It receives the key that was pressed and detects whether Shift, Alt, or Control was used. The ImportantKey function looks like this:
Function ImportantKey(KeyCode, Shift)
ImportantKey = False
If Shift = 4 Then
Exit Function
End If
If KeyCode = vbKeyDelete Or KeyCode = vbKeyBack Or (KeyCode > 31 _
And KeyCode < 256) Then
If KeyCode >= 37 And KeyCode <= 40 Then
' Right, Left, Up And Down Arrows
Else
ImportantKey = True
End If
End If
End FunctionThis generic function, found in basUtils, sets its default return value to False. It tests to see whether the Alt key was pressed. If so, the user was accessing a menu or accelerator key, which means there's no reason to flip the command buttons, so the function is exited. If Alt wasn't pressed, the key that was pressed is evaluated. If the Delete key, Backspace key, or any key with an ANSI value between 31 and 256 (excluding values 37 to 40—the arrow keys) was pressed, a True is returned from this function. The Keydown event of the form then calls the FlipEnabled routine. It looks like this:
Sub FlipEnabled(frmAny As Form, ctlAny As Control)
Dim ctl As Control
If TypeOf ctlAny Is CommandButton Then
ctlAny.Enabled = True
ctlAny.SetFocus
End If
For Each ctl In frmAny.Controls
If TypeOf ctl Is CommandButton Then
If ctl.Name <> ctlAny.Name Then
ctl.Enabled = Not ctl.Enabled
End If
End If
Next ctl
End SubThis generic routine, also found in basUtils, flips the Enabled property of every command button in the form except the one that was passed to it. The FlipEnabled routine receives a form and a control as parameters. It begins by creating a control object variable, then tests to see whether the type of control that was passed to it is a command button. If so, it enables the control and sets focus to it. The routine then loops through every control on the form that was passed to it. It tests to see whether each control is a command button. If it finds a command button, and the name of the command button isn't the same as the name of the control that was passed to it, it flips the Enabled property of the command button. The idea is this: When the user clicks Save, you can't immediately disable the Save button because it still has focus. You must first enable a selected control (the one that was passed to the routine) and set focus to the enabled control. After the control is enabled, you don't want to disable it again, so you need to eliminate it from the processing loop.
You need a way to flip the command buttons back the other way. The Click event of the Save button has the following code:
Private Sub cmdSave_Click() DoCmd.RunCommand Command:=acCmdSaveRecordacMenuVer70 Call FlipEnabled(Me, Me!Projects) End Sub
This code saves the current record and calls the FlipEnabled routine. The FlipEnabled routine flips the command buttons back to their original state. You probably want to add a Cancel button that issues an Undo and also flips the command buttons. You should also either prohibit users from moving to other records until they select Save, or flip the command buttons if the Save button is enabled and the user moves to another record. If you fail to do this, users move to another record, their changes are automatically saved (by Access), and the command buttons still reflect a “dirty” state of the form.
The ability to successfully work with objects and understand their properties, methods, and events is fundamental to your success as an Access programmer. Working with properties involves setting properties at design time and changing their values in response to events that occur at runtime. The ability to create multiple form instances and pass forms and other objects to subroutines and functions makes the VBA language extremely robust and flexible.