| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
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) that appear on a form or report. The key to successful programming lies in your ability to manipulate the database objects using VBA code at runtime. It is 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. The Application object refers to the active Access application. It contains all of Access's other objects and collections. These include the Forms collection, the Reports collection, the Screen object, and the DoCmd object. The Application object can be used to modify the properties of, or execute commands upon, the Access application itself. An example is to specify whether Access's built-in toolbars are available while the application is running.
Figure 10.1. An overview of Access's superficial objects.
The Forms collection contains all of the currently open forms within the database. Using the Forms collection, you can perform an action on each open form. For example, you could change the color of each open form.
The Forms collection is not the same as the list of all forms within the database. The list of forms within the database 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 Do You Care."
Just as the Forms collection contains all of the currently open forms, the Reports collection contains all of the currently open reports. Using the Reports collection, you can perform an action on each open report.
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 prior to the current control. If you attempt 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. The DoCmd object is followed by a period and the name of an action. Most of the DoCmd actions also require arguments. An example is the OpenQuery action. The OpenQuery action is used to execute an Access query. It receives the following arguments:
An example of the OpenQuery action of the DoCmd object is this:
DoCmd.OpenQuery "qryCustomers", acNormal, acReadOnly
The OpenQuery action is performed on the DoCmd object. The first argument, the query name, is "qryCustomers". This is the name of the query that is opened. It is opened in Datasheet view (rather than Design view or Print preview). It is opened in Read Only mode, meaning the resulting data cannot be modified.
Many people, especially those used to a procedural language, do not understand the concept of objects, properties, and events. A knowledge of Access's objects, their properties, and the events that each object can respond to is necessary if you want to be a productive and successful Access programmer.
As mentioned earlier in this chapter, objects are all of the things that make up your database. They include the 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 within the database has specific properties that determine its appearance or behavior. Each object also has specific methods, which are actions that can be taken upon it.
A property is an attribute of an object. Each object has many properties. Often, different types of objects share the same properties. At other times, the properties of an object are specific to that particular object. For example, forms, combo boxes, and text boxes all have Width properties. On the other hand, a form has a RecordSource property, which the combo box and text box do not have.
Most properties can be set at design time and modified at runtime. Although this is true of most properties, some properties can be set at design time and cannot be modified at runtime. Other properties cannot be accessed at design time and can be modified only at runtime. Access's built-in Help for each property tells you one of the following:
Each of these descriptions indicates when the property can be modified.
As a developer, you set the values of the properties of many objects at design time. The properties that you set at design time are the starting values for the object's properties at runtime. Much of the VBA code that you write will modify 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 are paying by credit card, you might want to set the visible property of the text box containing the credit-card number to true. This is just one of the many things that you can do to modify the value of an object's property at runtime in response to something that has occurred.
Many people wonder how you can determine all of 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 of the properties associated with an object is actually quite easy to accomplish. All you need to do is to invoke help by pressing F1. Click the Index tab in the Help Topics dialog 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 is properties. Double-click on properties. A Topics Found dialog appears (see Figure 10.3). Select Combo Box Control Properties. You are provided with 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.
Figure 10.2. The Help Topics dialog.
Figure 10.3. The Topics Found dialog.
Figure 10.4. A list of properties associated with a combo box.
An event is something that your application can respond to. Events include mouse movements, changes to data, a form opening, a record being added, and much, much, more. Users initiate events, as does your application code. It is up to you to determine what happens in response to the events that are occurring. You respond to events using macros or VBA code. Each Access object responds to different events. If you want to find out all of the events associated with a particular object, take the following steps:
Figure 10.5. The list of events associated with a text box.
Methods are actions that can be taken on the objects within 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 is specific to the object to which it applies. For example, a form has a GoToPage method. This method does not apply to a text box or to any other objects. If you search for help on methods, you see that you can get help on Application methods, Form methods, Report methods, and so on. (see Figure 10.6). If you select a particular category of methods (for example, Form methods), a Methods Reference appears (see Figure 10.7). When a particular method is selected, you get specific help on that method and how it is used (see Figure 10.8). If you click the Applies To hypertext entry, you get a list of all objects to which the selected method applies. You can click the Example hypertext entry to view sample code that includes an appropriate implementation of the selected method.
Figure 10.6. Getting help on methods.
Figure 10.7. The Methods Reference.
Figure 10.8. Getting help on a specific method.
The Object Browser is a powerful tool that can help you to learn about and better work with the objects that are a part of both Access 95 as well as the Microsoft Windows environment. The Object Browser displays information about Microsoft Access and other objects. It can assist you with the coding process by showing you all of the properties and methods associated with a particular object.
Access objects are complex. They contain numerous properties and methods. The Object Browser helps you to understand and utilize objects, properties, and methods by
The Object Browser can easily be invoked from the module window. You can click on the Object Browser button on the toolbar, press F2, or select View|Object Browser. The dialog shown in Figure 10.9 appears.
Figure 10.9. The Object Browser dialog with the database object selected.
The Object Browser displays two levels of information about the selected library or database. If you select the CHAP10.MDB database that ships on your sample code CD-ROM, your screen will look similar to Figure 10.9. The Modules/Classes list box displays all modules, including form and report modules, in the database. The Methods/Properties list box displays any procedures that have been defined within the selected module. Notice the module basUtils, which is part of the CHAP10.MDB database that ships on the sample code CD-ROM. Looking at the list box on the right, you can see the functions which are part of the basUtils module. You can click to select each form and report module listed 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 Libraries/Database drop-down to select a different object library. The Modules/Classes list box displays the types of objects that are available within the selected library or database. The Methods/Properties list box displays the methods, properties, and data elements defined for the selected object. This is illustrated in Figure 10.10. The item called Access - Microsoft Access 95 has been selected from the Libraries/Database box. The list box on the left shows all of Access 95's modules and classes. The list box on the right shows all of the properties and methods that are associated with the selected object—in this case, the Application object. You can even add other libraries to the Libraries/Database drop-down by referencing other type libraries. This is covered in Chapter 24, "OLE: Communicating With Other Applications."
Figure 10.10. The Object Browser window with the Access 95 library selected.
After you have located the method or property that you are interested in, you are given the option of pasting it into your application. With the desired method or property selected, simply click on the Paste Text button found in the Object Browser window. If you wish to obtain more information about a particular method or property, simply click on the Help button within the Object Browser.
Access objects are categorized into collections. A collection is a grouping of objects of the same type. For example, the Forms collection is a collection of all of the open forms within a database. Each form contains a Controls collection comprising all of the controls on that form. Each control is an object.
You must refer to an object via the collection that it belongs to. For example, you refer to a form through the Forms collection. VBA gives three ways to refer to an object such as a form. If you want to refer to the form frmProjects, you can select between the following options:
Referring to the form as Forms(0) assumes that frmProjects was the first form opened. It is important to understand that although as each form is loaded an element number is assigned, this element number changes as forms are loaded and unloaded at runtime. For example, the third form that is 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 not rely on the element number that a form is assigned; that number is a moving target.
You must refer to a control on a form first through the Forms collection and 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 within the code module of frmProjects, the code could be rewritten like this:
Me!txtClientID
Me refers to the current form or report. It is generic in that the code could be copied to any form that contains 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, which is part of the Reports collection. After you know how to refer to an object, you are ready to write code that modifies its properties and executes its methods.
Modifying an object's properties and executing its methods is a matter of referring to the object and then supplying an appropriate property or method. Here's an example:
Forms!frmHello!cmdHello.Visible = False
This line of code refers to the visible property of cmdHello, which is contained within the form frmHello, which is contained within the Forms collection. Notice that you must identify the object name frmHello as 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"
You might be confused about whether you are looking at a property of an object or a method of an object. 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:
Many people are confused about when to use a bang (!) and when to use a period. You should use a bang whenever you are separating an object from the collection that it resides in. For example:
Each object has a default property. If you are working with the default property of an object, you do not need to explicitly refer to the property in code. Consider the following two code samples:
Forms!frmHello!txtHello.Value = "Hello World"
and
Forms!frmHello!txtHello = "Hello World"
The Value property is the default property of a text box. Because it is the default property, you do not need to explicitly refer to it in code. My personal preference is to always explicitly state the property. This contributes to the readability of the code and keeps novice Access programmers who work with my code from having to guess which property I am changing.
Forms and reports are called class modules. This means that they act as templates for objects that you create instances of at runtime. Public procedures of a form and report become custom properties and methods of the form object at runtime. You can set the values of a form's custom properties and execute its methods using the syntax that you just learned.
Custom properties of a form or report can be created in one of two ways:
Let's take a look at both options.
The following steps are involved in creating and accessing a Custom form or report property based on a Public variable. The example is included in CHAP10EX.MDB in the forms frmPublicProperties and frmChangePublicProperty:
Figure 10.11. A form containing a Custom property.
Figure 10.12. A form modifying a Custom property.
To test the Custom property created in the preceding example, run the form called frmPublicProperties. This form is found in the database called CHAP10EX.MDB, found on your sample code CD-ROM. Click on the Change Form Caption command button. Nothing happens because the value of the Custom property has not been set. Open up the form called frmChangePublicProperty. Click on the Change Form Property command button. Return to frmPublicProperties and again click on the Change Form Caption command button. The caption of the form should now change.
Close the form called frmPublicProperties and try clicking on the Change Form Property command button. A runtime error occurs, indicating that the form you are referring to is not open. You can eliminate the error by placing the following code in the Click event of cmdPublicFormProperty:
Private Sub cmdPublicFormProperty_Click()
Form_frmPublicProperties.CustomCaption = _
"This is a Custom Caption"
Forms!frmPublicProperties.Visible = True
End Sub
This code modifies the value of the Public property using the syntax Form_FormName.Property. If the form is not loaded, this syntax loads the form but leaves it hidden. The next command sets the Visible property of the form to True.
A PropertyLet routine is a special type of subroutine that automatically executes whenever the value of the property is changed. A PropertyGet routine is another special subroutine that automatically executes whenever the value of the custom property is retrieved. Instead of using a Public variable to create a property, you insert two special routines: PropertyLet and PropertyGet. This example is found in CHAP10EX.MDB in the forms frmPropertyGetLet and frmChangeWithLet. To insert the PropertyLet and PropertyGet routine:
Figure 10.13. The Insert|Procedure dialog.
Figure 10.14. The PropertyGet and PropertyLet routines.
The completed routines are shown in Figure 10.15. Notice that the Click event code for the cmdChangeCaption command button has not changed. The PropertyLet routine, which automatically executes whenever the value of the CustomCaption property is changed, takes the uppercase value of what it is being sent and places it in a Private variable called mstrCustomCaption. The PropertyGet routine takes the value of the Private variable and returns it to whomever asked for the value of the property. The sequence of events is as follows. The following code is placed in the form called frmChangeWithLet:
Private Sub cmdPublicFormProperty_Click() Form_frmPropertyGetLet.CustomCaption = "This is a Custom Caption" Forms!frmPropertyGetLet.Visible = True End Sub
This routine attempts to set the value of the custom property called CustomCaption to the value "This is a Custom Caption". Because the value of the property is being changed, the Property Let routine within frmPropertyGetLet is automatically executed. It looks like this:
Public Property Let CustomCaption(ByVal CustomCaption As String) mstrCustomCaption = UCase$(CustomCaption) End Property
The Property Let routine receives the value "This is a Custom Caption" as a parameter. It uses the UCase function to manipulate the value it was passed and convert it to uppercase. It places the manipulated value into a Private variable called mstrCustomCaption. The PropertyGet routine is not executed until the user clicks on the cmdChangeCaption button in the frmPropertyGetLet form. The Click event of cmdChangeCaption looks like this:
Private Sub cmdChangeCaption_Click() Me.Caption = CustomCaption End Sub
Because this routine needs to retrieve the value of the Custom property CustomCaption, the PropertyGet routine automatically executes. It looks like this:
Public Property Get CustomCaption() As String CustomCaption = mstrCustomCaption End Property
The PropertyGet routine takes the value of the Private variable, set by the PropertyLet routine, and returns it as the value of the property.
You might wonder why this method is preferable to declaring a Public variable. The use of the UCase function within PropertyLet should serve to illustrate the point. Whenever you expose a Public variable, you cannot do much to validate or manipulate the value that you receive. The PropertyLet routine gives you the opportunity to validate and manipulate the value that the property is being set to. By placing the manipulated value in a Private variable and then retrieving the value of the Private variable when the property is returned, you gain full control over what happens internally to the property.
Custom methods are simply Public functions and subroutines placed within a form module or a report module. As you will see, they can be called using the Object.Method syntax. Here are the steps involved in creating a Custom method; they are found in CHAP10EX.MDB in the forms frmMethods and frmExecuteMethod:
Figure 10.15. The Custom method.
Figure 10.16. Executing a custom method.
Figure 10.15 shows the Custom method ChangeCaption found with the form frmMethods. The method changes the caption of the form. Figure 10.16 shows the click event of cmdExecuteMethod found in the form frmExecuteMethod. It issues the ChangeCaption method of the form frmMethods and then sets the Visible property of the form to True.
Object variables are variables that reference an object of a specific type. They can be used to reference databases, record sets, forms, controls, and even objects created within other applications.
Object variables allow you to create shortcut references to objects and to pass objects to subroutines and functions. They can be used to streamline code by using short names to refer to objects with long names. They can also be used to optimize code by providing 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. This is illustrated in the following code:
Private Sub cmdChangeCaption_Click()
Dim cmdAny As CommandButton
Set cmdAny = Me!cmdHello
cmdAny.Caption = "Hello"
End Sub
This code creates an object variable called cmdAny. It is an object variable of the type CommandButton. You then use the Set statement to point your CommandButton object variable towards the object cmdHello contained on the current form (Me). Finally, you modify the caption of the object variable cmdAny. Because an object variable is a reference to the original object, you are 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 = iVar1 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 iVar2 is initially assigned the value of iVar1, changing the value of iVar1 has no effect on iVar2. 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 Sub
This 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. Generic object variables include 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 Sub
In this example, ctlAny can be used to point to any control. Contrast this with the following code:
Private Sub cmdChangeVisible_Click()
Dim txtAny As TextBox
Set txtAny = Me!txtCustomerID
txtAny.Visible = False
End Sub
Here, your object variable can be used only to point at a text box.
After you are done working with an object variable, you should set its value to Nothing. This frees up all memory and system resources associated with the object.
Set frmNew = Nothing
Many people get confused about the difference between and object and a collection. Think of an object as a member of a collection. For example, frmHello is a form that is 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, and at other times you want to manipulate a collection of objects.
You have already learned quite a bit about manipulating a single object. An example is when you are setting the enabled property of a text box as in:
txtCustomerID.Enabled = False
This line of code affects only one text box and only one property of the text box. In manipulating a single object, you might want to affect multiple properties at one time. In that case, it is most efficient to use the With..End With construct. This is covered in the following section.
One method that 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.
A collection is like an array of objects. What makes the array special is that it is 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 very easy for you to manipulate Access's collections of objects. You do this using 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 of the command buttons. This illustrates a very practical use of a collection. Here, you loop through all of the open forms, changing the caption of each form. The code looks like this:
Sub FormCaptions()
Dim frm As Form
For Each frm In Forms
frm.Caption = frm.Caption & " - " & CurrentUser
Next frm
End Sub
This routine uses the For..Each construct to loop through each form in the forms collection, setting the caption of each form to the caption of the form, 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 database engine maintains information about all of the components of an Access 95 database. This information is stored in both container and document objects. Access 95 ships with eight containers. They are Databases, Forms, Modules, Relationships, Reports, Scripts, Tables, and SysRel. Every container object is made up of a collection of documents. These documents are the instances of the objects within the container.
It is 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 that are 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. If you want to change the caption of all of the open forms, you must use the Forms collection.
To view all of the forms stored in the database, you must loop through the Forms container and list the documents. The following code accomplishes this task:
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 Sub
This code creates a database object variable and a container object variable. It uses the CurrentDB() function, discussed in detail in Chapter 15, to point the database object variable to the current database. It then points the Container object variable to the container of forms. Next, it uses the Document object to loop through each document within the Documents collection of the Forms container and print out the name of each form in the database.
Just as you can create custom properties and custom methods, you can also create custom collections. Custom collections are very similar to arrays, but they offer several advantages:
Defining a Custom collection is very easy. It is simply a matter of using the Dim keyword to create an object of the type Collection. Here's an example:
Dim colSports As New Collection
The Dim statement indicates to the compiler that you want to declare a variable. The As New keywords indicate that you are creating a new instance of something. Specifically, you are creating a new instance of a Collection object. The new keyword is covered in more detail later in the chapter. For now, let's take a look at how you can add items to, and remove items from, the Custom collection.
The Add method is used to add 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. The Key is a string name that you can use to uniquely identify an element. The Before and After arguments enable 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. It 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).
Unlike almost every other array or collection in VBA, custom collections are 1-based rather than zero-based. This is a big gotcha if you are used to relying on the fact that arrays and collections are zero-based.
Removing objects from the custom collection is just as easy as adding them. The Remove method accomplishes the task. It 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 Sub
This 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 within the collection. This is done so that different types of values can be stored in each object within the collection. Access refreshes any collection when you first open a database and specifically refer to it.
The New keyword is used to create a new instance of an existing object. For example, you might want to display information about several customers, each customer on its own form. You would like each instance of the customer form to contain the same objects, variables, and code.
For example, you might want to create multiple instances of a form called frmSales. Each instance would show information about a different customer.
There are a couple ways that you can implement 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 visibility of the new instance to True.
The second method involves creating an object variable of the specific type of your form. You 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. You then point the object variable to a new instance of frmCustomers. The object variables being 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 is incremented each time that the command button is clicked. A Local form variable is declared. The form variable, frm is set equal to a new instance of the form called frmMultiInstance. The new instance is added to the Private collection. Its caption is set and its Visible property is set equal to True. This example illustrates two very important points:
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 a property of the object within a routine, you are changing the property in the original example. 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 form frmPassedToSub and the module basExamples:
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
In writing generic code, it might be necessary for you to determine the type of a control. For example, you might want to loop through all of the controls on a form and flip the enabled property of all of the command buttons. To accomplish this, you use the TypeOf keyword. The TypeOf keyword is actually part of the If statement. Here is an example of how it is used. It is found 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 Sub
It 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 the control as parameters. It begins by enabling the command button it was passed, and setting focus to it. The FlipEnabled routine then utilizes the VBA construct called For..Each to loop through all of 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 or not it is a command button. If it is, and it is not the command button that the routine was passed, the routine flips the enabled property of the control. 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 | |
|
ListBox |
ToggleButton |
VBA offers the convenience of performing actions on the active control, the active form, and other specially recognized objects. The following is a listing of special properties. All of these properties reference objects within the Access object model:
The following example of the use of 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 Sub
This subroutine modifies the caption of the active form, appending the current user onto the end of the existing caption.
Objects are used throughout the Time and Billing application. Almost every aspect of the application utilizes the skills discussed in this chapter. The examples that follow specifically apply the techniques that you learned to open multiple instances of the Projects form, and to enable and disable command buttons in response to the user making changes to the data on the frmClients form.
While viewing a project, a user might want to view information from another project. This can be accomplished by creating multiple instances of the project form. The cmdViewOtherProjects Command Button on the frmProjects form calls a routine called CreateInstances.
Private Sub cmdViewOtherProjects_Click()
Call CreateInstances(Me)
End Sub
The CreateInstances routine is a generic routine that can create multiple instances of any form. It is 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 DoCmd.MoveSize (mintCounter + 5) * 100, (mintCounter + 5) * 300 frm.Filter = frmAny.Filter frm.Visible = True End Sub
The CreateInstances routine receives a reference to the form it was passed. 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 new form is moved to cascade it down the screen. 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 only shows 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 is really no need for them to access 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 the keystrokes. 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 Sub
The KeyDown event automatically receives the code of the key that was pressed and whether or not Shift, Alt, or Ctrl was pressed along with that key. The event routine checks to determine if the Save button is already enabled. If it is, there is no reason to continue; the Enabled property of the command buttons has already been flipped. If Save is not already enabled, the ImportantKey function is called. It receives the key that was pressed and 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 Function
This generic function, contained within basUtils, sets its default return value to False. It tests to see if the Alt key was pressed. If so, the user was accessing a menu or accelerator key and there is no reason to flip the command buttons, so the function is exited. If Alt was not 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 Sub
This generic routine, also contained within basUtils, flips the Enabled property of every command button in the form that it was passed. The FlipEnabled routine receives a form and a control as parameters. It begins by creating a control object variable. It tests to see if the type of the control it was passed is a command button. If so, it enables the control and sets the focus to it. The routine then loops through every control on the form that it was passed. It tests to see if each control is a Command Button. If it finds a command button, and the name of the command button is not the same as the name of the control that it was passed, it flips the enabled property of the command button. The idea is this: when the user clicks Save, you cannot immediately disable the Save button because it still has the focus. You must first enable a selected control (the one that the routine was passed), and set focus to the enabled control. After the control is enabled, you do not 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 contains the following code:
Private Sub cmdSave_Click() DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 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 to 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 the values of those properties in response to events that occur at runtime. Further enhancing your ability to work with objects is the ability to create custom properties and methods for your form and report modules. These custom properties can be manipulated, and the custom methods executed, from outside of the form or report within which they are contained. Finally, the ability to create multiple form instances, and to pass forms and other objects to subroutines and functions, make the VBA language extremely robust and flexible.