Although versions of Access after 2007 support multivalue fields and provide some mechanisms for maintaining them, they cause a number of problems both in displaying them and in querying them. They break the principle of relational database design that a single field [column] holds only one datum, and so are inherently difficult to work with in a relational database application like Access.
The best way to store multiple items in a single field is not to do it at all. Instead, use multiple records in a related table to represent these items. There are several ways to present these records on a form in a way that users find easy to work with.
The obvious way to present multiple related items is to use a subform. An Access subform is designed to display and edit multiple records from a related table [these records being related to the record currently displayed on the main form], and does it with no code at all. However, the subform approach is a bit cumbersome to work with when all you want to do is let the user quickly choose multiple items from a list.
For small "pick-lists" I prefer the multiselect list box. However, list boxes in multiselect mode can't be bound directly to the data, so you need to use code to read the related records for each new main record and select the appropriate items in the list box. Then whenever the list box is updated you need to use code to update the set of records in the related table.
Here's code from a sample form that represents "family members", with a list box named "lstHobbies" that represents, for each family member, that person's hobbies from the list of all possible hobbies. As you can see, this will involve tables called "tblFamilyMembers" [all family members], "tblHobbies" [all the hobbies that anyone could have], and "tblFamilyMembersHobbies" [each record linking one family member to one hobby]. The list box shows all hobbies, with the selected items in the list being the hobbies held by the current family member.
code: click in the frame, Select All, then Paste into your code editor
As you see, there's a fair amount of code involved, because we're using the list box to do something it wasn't built to do, but it works quite nicely.
Relevance: Microsoft Access Versions: Access 95 to 2010 Categories: VBA, How To, Forms
Date: 24 April 2012
If you are using a Microsoft Access listbox on your form, you can set properties to allow the database user options as to how they make selections within the list box.
The MultiSelect property uses the following settings:
| None | 0 | [Default] Multiple selection isn't allowed. |
| Simple | 1 | Multiple items are selected or deselected by clicking them with the mouse or pressing the SPACEBAR. |
| Extended | 2 | Multiple items are selected by holding down SHIFT and clicking them with the mouse or by holding down SHIFT and pressing an arrow key to extend the selection from the previously selected item to the current item. You can also select items by dragging with the mouse. Holding down CTRL and clicking an item selects or deselects that item. |
There may be occasions where you wish to clear the listbox selections, and if the list is large would like to complete this exercise automatically.
To add this functionality to a command button on your form you can use the following code [replace YourCommandButtonName and YourListBoxName with the names of your button and list box]:
Private Sub YourCommandButtonName_Click[] Dim varItm As Variant With YourListBoxName For Each varItm In .ItemsSelected .Selected[varItm] = False Next varItm End With End SubClicking the command button will now clear the listbox of any selected items.
The list box control does something the combo box can't do. You can select multiple items in a list box. By default, the list box control allows only one selected item. By setting the control's Multi Select property to Simple or Extended, you allow the users to select more than one item from the list.
More than likely, you'll set this property when you create the control, but you can use VBA to modify the property using the form
listbox.MultiSelect = setting
where setting is one of the three values listed in Table 12.2.
None | The default setting, which doesn't allow multiple selections. | 0 |
Simple | Select or deselect multiple items by clicking or pressing the spacebar. | 1 |
Extended | Select or deselect multiple items by holding down the Shift key and using the down arrow to extend the selection. Or, hold down the Ctrl key and click specific items for a noncontiguous selection. | 2 |
Determining What's Selected and What's Not
The value of a combo box or a list box set to a single selection is easy to get by referring to the control's Value property in the form
control.Value
In fact, the Value property is the object's default and you don't even have to include the Value keyword. However, you probably should include it, because the code's much easier to read with the property.
Getting the value of a multiselect list box takes more work because there's more than one value to handle, which makes it a good candidate for a For Each statement. Using this statement, you can cycle through all the control's selected items.
Let's look at a quick example of a multiselect list box that uses the For Each statement to print the selected items to the Immediate window. Open the unbound example form and insert a list box. Name the control lstCustomers. Set the Row Source property to the following SQL statement:
SELECT Client FROM Clients
Then, set the Multi Select property to Simple.
Use any of the methods you've learned to open the form's module and enter the following event procedure:
Private Sub lstCustomers_LostFocus[] Dim varItem As Variant Dim lst As Access.ListBox Set lst = lstCustomers 'check for at least one selected item If lst.ItemsSelected.Count = 0 Then MsgBox "Please select a customer", _ vbOKOnly, "Error" Exit Sub End If 'cycle through selected items 'deselect selected items For Each varItem In lst.ItemsSelected Debug.Print lst.ItemData[varItem] lst.Selected[varItem] = 0 Next End Sub
Return to the form and view it in Form view. The event procedure uses the Lost Focus event, which occurs when you leave the list box. First, let's see what happens when there are no items selected. Press Tab three times to both give focus to and then move it from the list box [don't select anything in the list box]. Access displays the message box shown in Figure 12.16. Click OK to clear it.
Before trying to retrieve the selected items, you want to make sure that there is at least one selected item. The If statement checks the number of items in the ItemsSelected collection. If it's 0, that means there are no items selected.
Tab back to the list box and click the first and third items, as shown in Figure 12.17. Then, select either of the combo boxes from the prior examples to trigger the control's Lost Focus event.
After determining that there are selected items, the For Each statement loops through the ItemsSelected collection. The ItemData property equals the item text, which the Debug.Print statement prints to the Immediate window, as shown in Figure 12.18. Then, the corresponding Selected property is set to 0, which has the effect of deselecting the item.
Page 2
A value list control is easy to create, but it has one limitation you should know about. When you set the control's Row Source Type property to Value List, the Row Source property the actual list of items is limited to 2,045 characters or less. Most of the time, this limit will probably be adequate, but when it isn't, you need another solution such as a callback function to populate the list.
For Access to display a list box, certain parameters about that list box must be known first. For instance, Access must know how many rows and columns of data are in the control. The callback function passes these values to Access. A callback function is similar to any other function, but the difference is that you reference the function in the control's Row Source Type property. In addition, the callback function uses DAO, so you must reference that library for this populating method to work. That library is already referenced in the sample database, TimeTrack.mdb.
Let's walk through an example that uses a callback function to populate a list box control with all the forms in the current database. To do so, follow these steps:
Insert a list box in either of the example forms and name it lstCallback.
Enter CallbackList as the control's Row Source Type property. Just write right over the default Table/Query setting. The CallbackList function doesn't exist yet; that's the next step.
Click the Code button to launch the VBE and insert a standard module. [Or use the Chapter 12 module.]
Enter the following function and then save the module:
Function CallbackList[ctrl As Control, id As Variant, _ row As Variant, col As Variant, code As Variant] As Variant Select Case code Case acLBInitialize CallbackList = 1 Case acLBOpen CallbackList = 1 Case acLBGetRowCount CallbackList = CurrentProject.AllForms.Count Case acLBGetColumnCount CallbackList = 1 Case acLBGetColumnWidth CallbackList = -1 Case acLBGetValue CallbackList = CurrentProject.AllForms[row].Name Case acLBGetFormat CallbackList = -1 End Select End Function
View the form in Form view. Figure 12.19 shows the form and the list control, which displays a list of all the forms.
Opening the form in Form view forces the list box to call the CallbackList function. There's a lot going on behind the scenes, and you'll want to use the same structure: the arguments passed and the intrinsic constants used in the Select Case statement.
The CurrentProject.AllForms.Count statement determines the number of rows in the control by counting the number of documents in the Forms collection. Then, the CurrentProject.AllForms[row].Name statement determines each item by retrieving the names of all the documents in the Forms collection.
Callback functions are an advanced but powerful technique. They let you take complete control of the data displayed by a list control. Access calls your code each time it wants to retrieve a row for the list, and you can decide dynamically what to supply for the row.
| You've seen a few common uses for list controls in this chapter. A combo box makes a good filtering control. In addition, a list box is a good place for listing database objects, such as reports and forms, so users can choose what they need. Now, let's look at an unconventional use for a list control. Let's suppose you want to use the Employees form to view project information for individual employees. Now, you can use datasheet forms dropped in as subforms or opened with the click of a command button, but list controls make an interesting alternative. In this context, it's easy to apply what's known as the "drill-down" effect to a list control [not so easy with a datasheet form or subform]. This term refers to clicking or double-clicking data to display additional data about the clicked or double-clicked item. The first step is to create the query shown in Figure 12.20 and name it HoursWorkedByProject. Notice that the query is a Totals View so be sure to select the appropriate aggregate functions in each Total cell. In addition, enter the following reference in the EmployeeID column's Criteria cell: [Forms]![Employees]![EmployeeID] |
The list box shown on the left in the form shown in Figure 12.21 shows the results of the query, but those records are specific to the current employee. Refer to Table 12.3 to add the additional list box controls. Then, launch the form's module, enter the event procedures shown here, and save the form:
Private Sub Form_Current[] lstProjects.Requery lstDetails.RowSource = "" End Sub Private Sub lstProjects_DblClick[Cancel As Integer] Dim strSQL As String strSQL = "SELECT Projects.ProjectID, Tasks.TaskName, " _ & "Tasks.HourlyRate, Timeslips.DateWorked " _ & "FROM [Projects INNER JOIN Tasks ON Projects.ProjectID=Tasks .ProjectID] " _ & "INNER JOIN Timeslips ON Tasks.TaskID=Timeslips.TaskID " _ & "WHERE EmployeeID = " & Forms!Employees!EmployeeID _ & " AND ProjectName = '" & lstProjects.Column[2] & "'" _ & "ORDER BY TaskName, ProjectName, DateWorked ASC" Debug.Print strSQL lstDetails.RowSource = strSQL End Sub
list box | Name Row Source Column Count Column Widths | lstProjects HoursWorkedByProject 4 0";1.5";1.5";0.5" |
list box | Name Column Count Column Widths | lstDetails 4 0";1.5";0.5";0.5" |
You can use the form's navigation buttons or the filtering combo box control in the header to browse through the records. The form's Current event re-queries the projects control [on the left] to update it with only records for the current employee. This event also sets the detail displaying the control's Row Source property to "", so the list displays nothing.
To display more detailed information about an item in the left list box, double-click an item. The control's Dbl Click event sets the blank control's Row Source property to a SQL statement that reflects the current employee and the task you double-clicked in the first list box.
View the form in Form view. The first employee is Larry Schaff. Double-click the third item in the projects control Inventory Manager with a total of 68.75 hours. The double-click event updates the contents of the second list box, as shown in Figure 12.22.
You can see that Larry has clocked hours in project management on the inventory manager project for Bill's Auto Glass on three different days. He has spent part of four days on the project in support, and three days in testing. Each task has its own hourly rate, and you can even see the dates on which Larry performed the specific tasks.
Although this approach for displaying data isn't as common as some others, you might find it an approachable and easy alternative after you're familiar with list controls, their properties, and their unique behaviors.
Page 3
| IN THIS CHAPTER Working with Text Boxes 195 Using Controls in Option Groups 200 Working with Subforms 202 Working with the Tag Property 202 |
Page 4
Text boxes are the second most common control on most forms [the most common, labels, are pretty uninteresting from the point of view of automation]. You saw in Chapter 11's case study how you can use the events of text boxes to prevent users from entering invalid data, but there's much more that you can do with text boxes and VBA. This section of the chapter reviews some of the key properties of text box controls, and then shows you some useful techniques.
Key Properties of Text Boxes
You probably already know most of the properties of text boxes they're visible in the Properties window when you select a text box in Design mode in the Access user interface. Although you can work with most of these properties in code, some are more useful in code than others. Table 13.1 lists some of the more useful properties.
BackColor | The background color of the text box |
BorderColor | The border color of the text box |
ControlSource | The field in the record source where the text box gets its data |
Enabled | True if the text box can accept the focus, False if it cannot |
FontBold | True if the font is bold, False if it is not |
FontItalic | True if the font is italic, False if it is not |
FontName | The font in the text box |
FontSize | The size of the font in the text box |
ForeColor | The foreground color of the text box |
Locked | True if the text box cannot accept input, False if it can |
OldValue | The original value of a text box that's being edited |
SelText | The text that's selected in a text box |
Tag | A property that's not used by Access |
Text | The current text in the text box |
Value | The edited text in a text box |
Visible | True if the text box is visible, False if it's invisible |
TIP
Remember, when you refer to things in VBA, there are no spaces in the name. So the TextBox object has a ValidationText property, which corresponds exactly to the Validation Text property of a text box control on the Properties window.
You can use these properties to adjust the appearance and behavior of a text box at runtime [while the user is working with the form]. You might note that there are four properties dealing with the text in a text box. Of these, the SelText, OldValue, and Text properties are valid only for the text box that currently has the focus. The Text property is available for any text box on the form. If you try to retrieve, for example, the Text property of a text box that doesn't have the focus, VBA reports an error.
The other property that deserves some extra comment is the Tag property. This is a property that Access doesn't use. It exists simply to give you a place to store data that's associated with the text box. You see how to use the Tag property later in the chapter, in the "Working with the Tag Property" section.
NOTE
Forms, reports, and all types of controls have a Tag property, so you can store arbitrary data with just about everything.
Tracking the Focus
Users of your database might have many different levels of experience. Inexperienced users sometimes have trouble with concepts that you might take for granted. For example, the notion that there is a focus, and that only the control with the focus accepts data, can be challenging when you're first starting with Access. Here's a technique that can help new users understand exactly which part of a form is active.
To start, you need a pair of procedures in a standalone module. One of these procedures sets the BackColor property of a control to the code for yellow, and the other sets it to white:
Sub HighlightControl[ctl As Control] ' Set the background color of the ' specified control to yellow, if possible On Error Resume Next ctl.BackColor = 65535 End Sub Sub UnhighlightControl[ctl As Control] ' Set the background color of the ' specified control to white, if possible On Error Resume Next ctl.BackColor = 16777215 End Sub
As you can see, these procedures accept a single argument whose type is Control. Control is a generic object type that you can use to represent any type of Access control: text boxes, combo boxes, labels, and so on. Access enables you to pass any control to these procedures without raising an error.
However, there's no guarantee that every control you pass in supports a BackColor property. That's why the On Error Resume Next lines are in these procedures. If the procedure can set the property, great; if not, it exits without any fuss.
To use these procedures, call the HighlightControl procedure from the GotFocus event of each control on your form, and the UnhighlightControl procedure from the LostFocus event. For example, here's code to use these procedures with the Timeslips form:
Private Sub DateWorked_GotFocus[] HighlightControl DateWorked End Sub Private Sub DateWorked_LostFocus[] UnhighlightControl DateWorked End Sub Private Sub EmployeeID_GotFocus[] HighlightControl EmployeeID End Sub Private Sub EmployeeID_LostFocus[] UnhighlightControl EmployeeID End Sub Private Sub Hours_GotFocus[] HighlightControl Hours End Sub Private Sub Hours_LostFocus[] UnhighlightControl Hours End Sub Private Sub TaskID_GotFocus[] HighlightControl TaskID End Sub Private Sub TaskID_LostFocus[] UnhighlightControl TaskID End Sub
Save everything and open the form. You'll find that a yellow highlight follows the focus as you tab around the form. Figure 13.1 shows the Timeslips form after clicking in the Date Worked field.
CAUTION
If you use this technique with one form, be sure to use it consistently across your entire application. Inconsistent applications are extremely difficult to use.
Working with Unbound Text Boxes
An unbound text box is one that is not connected to a particular field in a database table. Unbound text boxes are useful when you want to allow the users to enter data that's used transiently. For instance, the Billing Report Setup form in the TimeTrack database allows the users to select a client, start date, and end date, and open a report in preview mode. But it doesn't let users filter the results any more specifically than by date. Here's how you might handle that requirement:
Open the BillingReportSetup form in Design mode.
Place the mouse at the bottom of the design area of the form [indicated by the grid of dots], and then click and drag to make the form taller.
Add a new text box to the form. Because the form itself is unbound, the text box is automatically unbound. Name the new text box txtWhere. Set the attached label to WHERE clause.
Add a new command button to the form. Name the new button cmdAdvancedReport and set its caption to Advanced Report.
Open the form's module and add this code to handle the Click event of the new button:
Private Sub cmdAdvancedReport_Click[] On Error GoTo HandleErr DoCmd.OpenReport "BillingReport", acViewPreview, _ WhereCondition:=txtWhere.Value ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdAdvancedReport_Click" Resume ExitHere End SubSave the form.
To test the new controls, open the form in Form view. Select Bill's Auto Glass as the client, 5/1/2004 as the start date, and 6/1/2004 as the end date. Then enter Hours=7 as the WHERE clause, and click the Advanced Report button. As you can see in Figure 13.2, the resulting report displays only the timeslips on which exactly seven hours was reported.
Note that the code uses the Value property to retrieve information from a text box that does not have the focus.
Page 5
Option groups, of course, are controls that can contain other controls. Specifically, you can put any of these controls inside of an option group by dropping the control on the option group when you're designing the form:
Check box
Option button
Toggle button
Only one control within an option group can be selected at a time. When you click on one option button, for example, it becomes selected and all the other controls in the option group become deselected. Each control in the option group has its own Option Value property, and the Value property of the option group is equal to the Option Value of the selected control.
TIP
Because only one control in an option group can be selected at a time, they don't work well when you have a series of check boxes and want to allow the users to check more than one at the same time. In that case, you can construct a fake option group by positioning the check boxes inside of a rectangle control.
None of the fields in the TimeTrack database is especially suited for representation by an option group, so we've constructed the Chapter13OptionGroup form to show you some useful code. Figure 13.3 shows this form.
The code behind this form shows you how to perform three tasks:
Report the value of the option group in an unbound text box
Set the value of the option group by entering a value in the text box
Disable all the option buttons in the option group as a single operation
To handle the first of these tasks, you have to catch the AfterUpdate event of the option group, which is fired whenever the users click one of the option buttons:
Private Sub grpOption_AfterUpdate[] ' Show the option group value ' in the text box txtValue = grpOption End Sub
The line of code that does the assignment from the option group to the text box uses the fact that each control has a default property. When you specify a control name in a context where only a property makes sense, VBA uses this property. For both the option group and the text box, the default property is the Value property, so this bit of code assigns the value of one to the value of the other.
To handle the second requirement, you need code attached to the Change event of the text box, so that it takes effect whenever the users type a character:
Private Sub txtValue_Change[] ' Update the option group ' from the text box On Error Resume Next grpOption = CInt[txtValue.Text] End Sub
The On Error Resume Next statement takes care of the case where the users type something nonsensical, such as q, in the text box. In that case, the code simply leaves the option group alone.
Finally, disabling all the option buttons at once introduces several new concepts:
Private Sub cmdDisable_Click[] ' Disable the entire option group Dim ctl As Control For Each ctl In grpOption.Controls If ctl.ControlType = acOptionButton Then ctl.Enabled = False End If Next ctl End Sub
Controls that contain other controls [like the option group] have their own Controls collection. By using a For Each loop, the VBA code visits each control in this collection in turn when the users click the button. But if you look at Figure 13.3, you'll see that the option group contains both label controls and option button controls. Trying to disable a label control raises a runtime error. Rather than simply suppress the errors, this code takes a more elegant approach. Every control on an Access form or report has a ControlType property that returns a constant indicating the type of the control. So this loop steps through all the controls, tests each one to determine whether it is an option button, and then disables the control only if it is an option button.
Page 6
Subforms are a peculiar sort of control: they contain an entire form. This form, of course, can contain its own controls, including other subforms [although you can't go deeper than sub-subforms in Access].
To work with subforms, you must first know how to refer to the controls that they contain. The general form of control reference for subforms is as follows:
Forms![top-levelformname].[subformcontrolname].Form![controlname]
For example, the Projects form in the TimeTrack database contains a subform control named Tasks. To display the value of the TaskName field in the current row in this subform, you can type this expression into the Immediate window:
?Forms!Projects.Tasks.Form!TaskName
Of course, if you're working in code behind the Projects form, you can shorten this reference by using the Me shortcut, perhaps assigning the result to a variable:
varTaskName = Me.Tasks.Form!TaskName
After you understand how to refer to subform controls, working with subforms is exactly like working with forms. The key is the .Form property. This property lets you "look inside" the subform to refer to the controls that it contains.
Page 7
Beginning VBA developers are sometimes confused by the Tag property, wondering what good it is to have a property that Access never uses. The answer is that it's very useful indeed to have a property that you can use yourself for whatever you want. Every control on an Access form has a Tag property, and it's up to you to decide what to put there and how to use it.
For an example of how to use the Tag property, let's modify the HighlightControl procedure that you saw earlier in the chapter. As it stands now, this procedure sets controls to a yellow background when you enter them. But what if you don't always want to use that color?
There's no built-in property for Access controls that says "use this color to highlight the control." Fortunately, there is the Tag property. Here's how to make the changes:
Open the Timeslips form in Design mode. Set the Tag property of the EmployeeID and TaskID controls to 65535. Set the Tag property of the DateWorked and Hours controls to 16777088.
Open the Chapter13 code module and modify the HighlightControl procedure as follows:
Sub HighlightControl[ctl As Control] ' Set the background color of the ' specified control to yellow, if possible On Error Resume Next If Not IsNull[ctl.Tag] Then ctl.BackColor = ctl.Tag Else ctl.BackColor = 65535 End If End Sub
Save the form. Switch the form back to Form view and tab through the controls. You'll see that the first two controls turn yellow when they have the focus, which corresponds to a BackColor value of 65535. The second two controls turn a light blue, color 16777088.
CAUTION
Don't confuse the Tag property with the SmartTags property.
Note that the procedure has been written to do something useful [We hope!] whether or not there's a value in the Tag property. If the tag is filled in, the procedure uses that number for the highlighted background color. If there's no tag value, it falls back to the original yellow that was coded in the first version of HighlightControl.
| This chapter's case study explores an alternative user interface for displaying client, employee, and project information. The idea is to give users a single central form that they can toggle between these three types of information. You also add control highlighting to aid in data entry. To begin with, you need to build three new forms to act as subforms on the main form. These forms, ClientSub, EmployeeSub, and ProjectSub, are each two inches high and three inches wide. The Tag property of each text box on these forms is set to 65535, and the Tag property of the combo box on the ProjectSub form is set to 12615935 to remind users that it draws its data from another table. Figure 13.4 shows these three forms open in Design mode. |
Of course, each of the subforms has the code necessary to handle control highlighting. Here's the code from the ClientSub form:
Option Compare Database Option Explicit Private Sub Address_GotFocus[] HighlightControl Address End Sub Private Sub Address_LostFocus[] UnhighlightControl Address End Sub Private Sub City_GotFocus[] HighlightControl City End Sub Private Sub City_LostFocus[] UnhighlightControl City End Sub Private Sub Client_GotFocus[] HighlightControl Client End Sub Private Sub Client_LostFocus[] UnhighlightControl Client End Sub Private Sub Contact_GotFocus[] HighlightControl Contact End Sub Private Sub Contact_LostFocus[] UnhighlightControl Contact End Sub Private Sub Phone_GotFocus[] HighlightControl Phone End Sub Private Sub Phone_LostFocus[] UnhighlightControl Phone End Sub Private Sub State_GotFocus[] HighlightControl State End Sub Private Sub State_LostFocus[] UnhighlightControl State End Sub Private Sub Zip_GotFocus[] HighlightControl Zip End Sub Private Sub Zip_LostFocus[] UnhighlightControl Zip End Sub
Here's the corresponding code for the EmployeeSub form:
Option Compare Database Option Explicit Private Sub EmployeeID_GotFocus[] HighlightControl EmployeeID End Sub Private Sub EmployeeID_LostFocus[] UnhighlightControl EmployeeID End Sub Private Sub FirstName_GotFocus[] HighlightControl FirstName End Sub Private Sub FirstName_LostFocus[] UnhighlightControl FirstName End Sub Private Sub LastName_GotFocus[] HighlightControl LastName End Sub Private Sub LastName_LostFocus[] UnhighlightControl LastName End Sub
And finally, the code for the ProjectSub form:
Option Compare Database Option Explicit Private Sub ClientID_GotFocus[] HighlightControl ClientID End Sub Private Sub ClientID_LostFocus[] UnhighlightControl ClientID End Sub Private Sub EstimatedEndDate_GotFocus[] HighlightControl EstimatedEndDate End Sub Private Sub EstimatedEndDate_LostFocus[] UnhighlightControl EstimatedEndDate End Sub Private Sub ProjectName_GotFocus[] HighlightControl ProjectName End Sub Private Sub ProjectName_LostFocus[] UnhighlightControl ProjectName End Sub Private Sub StartDate_GotFocus[] HighlightControl StartDate End Sub Private Sub StartDate_LostFocus[] UnhighlightControl StartDate End Sub
The next step is to build the master form. This is an unbound form that hosts the three subforms. Follow these steps to create this form:
Create a new form in Design mode.
Add an option group to the form and name it grpSub. Set the default value of grpSub to 1.
Add three option buttons to the option group. Set their captions to Clients, Employees, and Projects, and set their Option Value properties to 1, 2, and 3.
Drag the ClientSub form from the Database window and drop it on the master form. This is the easiest way to get a subform control sized properly to hold the form.
Delete the label that Access creates for the subform.
Name the subform control SwitchForm. Figure 13.5 shows the master form in Design view.
Add code behind the form to handle switching the contents of the subform:
Private Sub grpSub_AfterUpdate[] ' Switch subforms as buttons as clicked Select Case grpSub.Value Case 1 ' Clients Me!SwitchForm.SourceObject = "ClientSub" Case 2 ' Employees Me!SwitchForm.SourceObject = "EmployeeSub" Case 3 ' Projects Me!SwitchForm.SourceObject = "ProjectSub" End Select End Sub
Save the form and open it in regular Form view. You'll find that you can use the toggle buttons to switch the embedded subform, and that highlighting follows the cursor through the data-entry fields. Figure 13.6 shows the final form.
Page 8
| IN THIS CHAPTER Introducing the Report Module and Events 209 Opening and Closing Reports 210 Passing Arguments Using OpenArgs 212 Populating the Report 213 Handling Report-Level Errors 215 Using VBA to Determine Group Properties 218 |
Page 9
| For the most part, VBA code is stored in one of two module types: standard or object. The object module is a special module attached to a report or a form. The module is saved with the object and supports events and properties that are unique to the object. Most of the code you enter and store in a report module [or a form module for that matter] comes in the form of event procedures code that's executed when some action occurs. For instance, code added to the report's Open event is executed when you open the report. Let's look at a quick example that maximizes the report:
Read about report events and their sequence in "The Event Sequence for Reports on page 170. |
Page 10
One of the ways you can lead users through the work process is to open objects for them as the objects are needed and then close them when the users are done with them. Of course, most users can be taught to open and close objects for themselves, but automating the process goes a long way toward keeping users on the right road.
Opening a Report
In the case study found in Chapter 8, "Understanding Objects," you converted the macro that opens BillingReport to an event procedure using the following code:
Private Sub cmdOpenReport_Click[] On Error GoTo HandleErr DoCmd.OpenReport "BillingReport", acViewPreview ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdBillingReport_Click" Resume ExitHere End Sub
For now, you're interested in the OpenReport method. It's very similar to the OpenForm method you learned about in Chapter 10, "Working with Forms." Both are methods of the DoCmd object. To open a report, use the OpenReport method in the following form:
DoCmd.OpenReport reportname [, view] [, filtername] [, wherecondition] [, windowmode] [, openargs]
where reportname is a string expression that identifies the report you're opening by name. Table 14.1 lists the optional arguments. Tables 14.2 and 14.3 give additional details about the syntax of OpenReport.
view | Constant | One of the intrinsic constants listed in Table 14.2; determines the report's view. |
filtername | Variant | A string expression that equals the valid name of a fixed query. |
wherecondition | Variant | A string expression that equals a valid SQL statement WHERE clause, without the WHERE keyword. |
windowmode | Constant | One of the intrinsic constants listed in Table 14.3; determines the report's mode. |
openargs | Variant | A string expression or value that's passed to the report's OpenArgs property. |
acViewNormal | 0 | Prints the report. |
acViewDesign | 1 | Opens the report in Design view. |
acViewPreview | 2 | Opens the report in Print Preview. |
7acWindowNormal | 0 | Relies on the report's properties. |
acHidden | 1 | Opens, but hides, the report. |
acIcon | 2 | Opens, but minimizes, the report in the Windows taskbar. |
acDialog | 3 | Opens the report as a dialog box when the Modal and PopUp properties are set to Yes. |
The OpenReport method that you added to the application in Chapter 8
DoCmd.OpenReport "BillingReport", acViewPreview
opens the BillingReport report in Print Preview mode. Although there are several arguments, this particular statement needs only two to get the job done.
The report itself is based on the BillingReportSource query, which grabs the values you choose in the form. You can pass the data source in the filtername argument, or you can build a WHERE clause and use the wherecondition argument to limit the resulting report. There are often many ways to accomplish the same task. Normally, you'll find one that accommodates the task a bit better than any other.
Closing a Report
To close a report, use the DoCmd object's Close method in the form
DoCmd.Close [objecttype] [, objectname] [, save]
where all the arguments are optional. When all are omitted, VBA closes the object that has the focus. When you're explicitly referencing the report by name, use the objectname argument to identify the report and identify the object by type using the acReport intrinsic constants. The save argument has three constants:
acSavePrompt The integer value is 0 for this constant. It displays the Save prompt so the users can decide.
acSaveYes This constant saves changes to the report and equals the integer value of 1.
acSaveNo This default setting doesn't save changes and equals the integer value of 2.
Page 11
You learned how to pass values to a procedure using arguments. In a similar way, you can pass values to a report, but outside of the normal procedure structure, using the OpenArgs argument and property.
The OpenArgs argument belongs to the OpenReport method, and handles the actual passing of the value to the report. The property belongs to the report itself and receives and stores the passed value.
You can learn how to use the OpenArgs method and property in forms by reviewing "Passing Arguments Using OpenArgs page 151.
Let's suppose you don't always need to see all the detail records in the billing report; you want just a summary instead. Using the OpenArgs method, you can pass a value to the report that hides the detail records when you want. To add this functionality to TimeTrack, do the following:
Open the BillingReportSetup form in Design view and add a check box just below the Open Report command button. [Note that this example is working with the original report opening button and not the Advanced Report button that you added in Chapter 13.] Name the new control chkSummary. Set the caption of the check box to Summary.
Set the new control's Default Value property to 1 so it's selected by default.
Modify the cmdOpenReport_Click[] event procedure by adding the OpenArgs argument to the OpenReport method, as follows:
DoCmd.OpenReport "BillingReport", acViewPreview, , , , chkSummary
The OpenArgs argument passes chkSummary's value [checked or unchecked] to the report's OpenArgs property.Save and close the form.
Add the following line to the BillingReport report's Open event, immediately following the DoCmd.Maximize statement:
Reports["BillingReport"].Section[acDetail].Visible = Me.OpenArgsSave and close the report.
Return to the modified form. Select Bill's Auto Glass and enter 10/1/04 and 10/31/04.
Next, uncheck the new check box control as shown in Figure 14.1. Then, click the Open Report button. Figure 14.2 displays the summarized values without the detail records. Close the report and the form when you're done.
Opening the report with the check box checked passes the value 1 or True, which is the section's default setting. That's why you made checked the default. Unchecked, the OpenArgs argument passes 0, or False. When this is the case, the Visible property is set to False and the Detail section is hidden in the previewed report.
Page 12
The existing billing report shows one way of limiting the records that populate the report. The report's query grabs values from the setup form. It's a simple technique and requires no VBA code other than the short procedure needed to launch the report from the form.
VBA has an alternative to this technique. As mentioned earlier, you can use a couple of the OpenReport method's arguments to specify a particular query or even a WHERE clause. If you choose this route, you need to remove the form references from the report's underlying query and then replace the report opening command button's Click event with the following procedure:
Private Sub cmdOpenReport_Click[] Dim strCriteria As String Dim frm As Form Set frm = Forms!BillingReportSetup On Error GoTo HandleErr strCriteria = "Clients.ClientID = " & frm!cboClient.Value & _ " AND Timeslips.DateWorked Between #" & frm!txtStartDate & _ "# AND #" & frm!txtEndDate & "#" Debug.Print strCriteria DoCmd.OpenReport "BillingReport", acViewPreview, , strCriteria ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description Resume ExitHere End Sub
As you can see, this method appears a bit more complex because you must build the WHERE string. There's no right or wrong to the solution you choose. With time, you'll find that each method has its pros and cons and choose according to the requirements.
Applying a Filter and Sort Order
At this point, you've already seen a lot of expressions and literal strings used to limit the records that make it to TimeTrack's billing report. But there's one filtering property you haven't seen yet. Both forms and reports have properties that enable you to apply filters to the underlying recordset:
Filter A string expression that identifies the actual filter as a WHERE clause without the WHERE keyword.
FilterOn Enables the filter specified in the Filter property.
OrderBy Determines how the records are sorted, but the report's native sort order[s] take precedence over this property.
OrderByOn Enables the sort specified in the OrderBy property.
Setting the Filter and FilterOn properties through VBA is just one way to automate this process and allow enough flexibility so that you can use the same report to display different recordsets.
To set a filter string, use the following syntax:
Me.Filter = filterstring
where filterstring represents the actual filtering criteria by which you want to filter the report. For instance, if you wanted to filter the billing report by a particular client, you might use the following statement
Me.Filter = "ClientID = 1"
You would need to follow that statement with the FilterOn property in the form
Me.FilterOn = True
Setting the FilterOn property to False would disable the current filter.
The sorting properties are similar to the filtering properties in that you specify a SQL ORDER BY clause, without using the ORDER BY keywords, and then you enable the sort using the following syntax
Me.OrderBy = "ClientID, ProjectID DESC" Me.OrderByOn = True
Page 13
Like forms, you can handle report-specific errors using a special event the report's Error event when an error occurs at the Jet database engine level. The event has two arguments for passing error values to the event:
DataErr Stores error code returned by the Err object.
Response Determines whether an error message is displayed using one of the following intrinsic constants: acDataErrContinue ignores the error and acDataErrDisplay displays the default error message.
The benefit of the Error event is that you can glean an error's actual number and then add that error to your list of errors that you need to handle if a generic handler isn't appropriate. Let's look at a quick example using BillingReport:
Open BillingReport in Design view and change the Record Source property from BillingReportSource to test.
Save the report and close it.
Open the BillingReportSetup form, choose a client, and then click the Open Report button. Viewing the report in Print Preview generates the error shown in Figure 14.3.
Click OK to clear the message.
Open the report's module and enter the following code in the report's Error event:
Private Sub Report_Error[DataErr As Integer, Response As Integer] Debug.Print DataErr End Sub
View the report in Print Preview to generate the error again.
View the Immediate window in the VBE. It contains the Jet error code, as shown in Figure 14.4.
Now that you know the error code, replace the Debug.Print statement in the error handler with the following code:
Private Sub Report_Error[DataErr As Integer, Response As Integer] If DataErr = 2580 Then MsgBox "The report's data source has been changed or is " & _ "missing", vbOKOnly, "Error" Response = acDataErrContinue End If End Sub
View the report in Print Preview one more time. The same error occurs, but this time, instead of displaying the default message, the Error event displays your custom message shown in Figure 14.5. Click OK to clear the message.
Return the report to Design view and reset the Record Source property to BillingReportSource.
In this particular case, the custom message you added to the Error event isn't superior to the internal message, but that isn't really the point of the exercise. Now you know how to determine an error's code so you can include it in your own error-handling routine.
What to Do When There's No Data
Reports have a unique problem what to return when there are no records in the report's underlying record source. That really isn't an error, and Access will still display the report, but most of the time, users won't appreciate or understand what they see. For instance, Figure 14.6 shows the BillingReport report opened from the Database window instead of going through the setup form.
As you can see, the report is full of error values. To avoid this problem, use the report's NoData event to cancel the report or display a custom message. This section uses the billing report to illustrate a simple solution for this problem. First, open BillingReport's module and enter the following code:
Private Sub Report_NoData[Cancel As Integer] MsgBox "There are no records to print", _ vbOKOnly, "No report" Cancel = True End Sub
Preview the report and click OK in response to the parameter prompts. These occur because Access is trying to resolve references to the form values that are missing. After dismissing three parameter prompts, Access displays the message box shown in Figure 14.7. Click OK to clear the message box.
Page 14
You might not realize that a group's header and footer have corresponding events, like other report sections, and that you can use these events to modify reports on the fly even adding new sections as needed.
| Whether working with sections or groups, you need to know how to reference the appropriate area or level. To reference a report section, use the report's name or number. For instance, you can refer to a report's Detail section using the following syntax: Reports[reportname].Detail where reportname is a string that identifies the report by name. Detail is the actual name of the section. Or, you can use the following form: Reports[reportname].Section[0] or even Reports[reportname].Section[acDetail] When referencing group levels, use the same form as sections: Reports[reportname].GroupLevel[index].property A group level is actually an object, so you can use an object variable as follows: Dim glGroup As GroupLevel Set glGroup = Reports["BillingReport"].GroupLevel[0] |
CAUTION
When referencing report sections and group levels, you must always explicitly identify the property being read or set. Neither report sections nor group levels offer a default property.
The GroupLevel object has a number of properties that you should already be familiar with if you've spent anytime building reports in Access:
GroupFooter
GroupHeader
GroupInterval
GroupOn
KeepTogether
SortOrder
ControlSource
Table 14.4 lists the individual property settings for each of these properties.
GroupFooter and GroupHeader | True or False |
GroupOn | Each Value = 0 Prefix Characters = 1 Year = 2 Qtr = 3 Month = 4 Week = 5 Day = 6 Hour = 7 Minute = 8 Interval = 9 |
KeepTogether | No = 0 Whole Group = 1 With First Detail = 2 |
SortOrder | Ascending = False Descending = True |
CAUTION
You can create new groups only in Design view, but you can set most group properties from the report's Open event.
| TimeTrack only has one report and it deals with billing. Suppose, for example, that your developers need to see a revised schedule occasionally. A report is probably the best way to present this information, so you need to give users an easy way to print a schedule grouped by the day, the current week, and even the current month. Doing so requires a form that allows the users to choose one of the three possible schedules the Switchboard form is the best place for these options and a new report.
Create the new query named Schedule shown in Figure 14.9. |
Save and close the query.
Use the AutoReports: Tabular wizard to create a tabular report based on the Schedule query, and name it Schedule.
Open the new report in Design view and click the Sorting and Grouping tool. Using Figure 14.10 as a guide, set the appropriate group properties. Grouping the schedule by each day [shown as Each Value in the user interface] will be the report's default group. The first and third rows' properties are all defaults. Set the second row's Group Header property to Yes so you can visually tell where one group ends and the next begins.
In the report's module, enter the following code:
Private Sub Report_Open[Cancel As Integer] If IsNull[Me.OpenArgs] Then Exit Sub End If Me.GroupLevel[1].GroupOn = CInt[Me.OpenArgs] End Sub
Save and close the report.
Open the switchboard and click the new Daily Schedule button to view the schedule grouped by the EstimatedEndDate field, as shown in Figure 14.11. [The figure shows only a portion of the report.] The report is grouped by each value [that is, each day gets its own group], which is the same as the default you set earlier. It isn't even necessary to pass the daily value as the sample does, but it doesn't hurt to include it in case you modify the first group level, which is currently based on the Client field [0].
Close the report and click the Monthly Schedule button to see the report shown in Figure 14.12. This report displays the same records as the first, but groups the EstimatedEndDate values by the month. As you can see, the records in December are grouped differently for each report. Because of the current dates, the weekly schedule looks the same as the daily schedule, but that won't always be the case.
Page 15
| IN THIS CHAPTER Investigating the Application Collections 233 Retrieving Lists of Objects 225 Working with Object Properties 226 Programmatically Determining Dependencies 229 |
Page 16
You know, of course, that Access has all sorts of objects in each database: tables, forms, macros, and so on. And you've already worked with some of those objects in VBA. For example, you know how to open a form or preview a report from VBA by now. But there are other ways to work with Access objects by using the built-in application collections. Figure 15.1 shows the portion of the Access object model that includes these collections.
The Access Application object supplies two main ways to navigate through the object model to individual objects. The CurrentProject object leads to collections for each of the user interface objects: AllDataAccessPages, AllForms, and so on. The CurrentData object leads to collections for each of the data-related objects: AllQueries, AllTables, and so on. Each of these collections in turn contains AccessObject objects that represent the individual items in the collection.
NOTE
Technically, the CurrentProject object gives you the objects maintained by the Access program itself, whereas the CurrentData object leads to the underlying database engine. In regular Access databases, the database engine is Microsoft Jet; in Access projects, it's Microsoft SQL Server. Most of the CurrentData collections [AllDatabaseDiagrams, AllFunctions, AllStoredProcedures, and AllViews] are applicable to Access projects using the SQL Server database engine. We don't discuss those objects in this book.
CAUTION
Don't confuse the AllForms and AllReports collections with the Forms and Reports collections. The former pair contains all their respective objects in the entire database, whereas the latter two contain only open forms and reports.
This chapter shows you what you can do with these objects. They provide ways for your code to work with the information that you see in the Database window when you're interacting directly with Access, and they can be very handy when you're writing tools in VBA. Each of the object collections supports four properties:
Application This property returns the parent Application object.
Count The number of AccessObject objects in the collection.
Item Indexed property to return individual AccessObject objects.
Parent Pointer to the parent object [such as CurrentData or CurrentProject].
NOTE
In some cases, the Parent property can return a CodeProject or CodeData object. These objects are similar to the CurrentProject and CurrentData objects, but refer to databases loaded in code. You don't run into these objects when just working with normal databases through the user interface; they're important for add-in developers.
Page 17
| 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:
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 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. |
Page 18
As you've seen, each of the objects in any of the object collections is represented by an AccessObject object. This object has a set of built-in properties that tell you a few things about the object:
CurrentView A constant that tells you the current view of an object [for example, Design view or Datasheet view] if the object is open.
DateCreated The date that the object was created.
DateModified The date that the object was last changed.
FullName The full name [including the path] of the object.
IsLoaded True if the object is currently open, False if the object is currently closed.
Name The name of the object.
Parent The collection that contains this object.
Properties A collection of the properties of the object.
Type A constant representing the type of the object, such as form, report, or table.
NOTE
The FullName property applies only to data access pages, which are stored as external files. It's empty for all other objects.
The AccessObject object offers an additional, advanced capability: you can create your own properties for these objects. This is useful in much the same way as the Tag property is on a form; you can use your own properties to store custom information that is used in your code.
Take another look at the FormList form. One problem with it as you initially built it is that it lists all the forms, even ones that you probably don't want to open all by themselves. For example, showing the various sample forms, switchboards, and subforms in the list is more confusing than anything else. To deal with this problem, you can use a custom property to tell the FormList form whether to display a particular form.
To start, you need a way to add your own custom property to the forms that you want to display. Typically, this is the sort of thing that you want to do when you're designing a database; there's no need to let the users adjust this property. So you can add a procedure to the sample database to handle the task, like so:
Public Sub ShowInFormList[strFormName As String] ' Mark the specified form so that it ' will be displayed on the FormList ' form ' Get the AccessObject corresponding to ' the specified form Dim AO As AccessObject Set AO = CurrentProject.AllForms[strFormName] ' Create a new property on the object AO.Properties.Add "ShowInFormList", True Debug.Print "Property set" End Sub
Each AccessObject object has its own collection of custom properties named [not surprisingly] Properties. This code starts by retrieving the AccessObject object that corresponds to the specified form. It then uses the Add method of the Properties collection to add a new custom property to the AccessObject. The two arguments to this method are the name of the property [which must be a string] and the initial value of the property [which can be any variant]. So the code as shown adds a new property named ShowInFormList and sets its initial value to True. Figure 15.3 shows how you might call this procedure from the Immediate window to add the ShowInFormList property to the Clients form.
TIP
The Properties collection of an AccessObject object contains only the custom properties that you've added to the object. It doesn't contain any of the standard properties such as Name or DateCreated.
In the sample database, we used the ShowInFormList procedure to add the custom property to the Clients, Employees, Projects, and Timeslips forms.
The next step is to modify the code behind FormList to display only the forms with the custom property present and set to True. Here's the revised code:
Private Sub Form_Load[] ' Stock the listbox with the names of ' all marked forms in the database Dim AO As AccessObject ' Keep going if the custom property is missing On Error Resume Next For Each AO In CurrentProject.AllForms If AO.Properties["ShowInFormList"].Value = True Then If Err = 0 Then ' Only add the form if the property is ' actually present and set to True lbForms.AddItem [AO.Name] End If Err.Clear End If Next AO End Sub
You need to exercise some care when working with custom properties. If you try to retrieve a value for a property that doesn't exist, VBA will raise an error. This code shows one way to deal with the situation. First, it sets the error handler to On Error Resume Next to make sure that any errors are not fatal. Next, for each form, it tries to retrieve the value of the custom property. There are three possibilities for what happens here:
If the property doesn't exist, the code proceeds to the next line, but the built-in Err variable [which holds the number of the most recent error] is set to some number other than zero. In this case, the line to add the form name is skipped.
If the property exists and its value is False, the check for True fails and the line to add the name to the form is also skipped.
If the property exists and its value is True, the check for an error returns zero and the form's name is added to the listbox.
Figure 15.4 shows the FormList form after making these code changes. As you can see, it only displays the forms that have the custom ShowInFormList property set to True.
Page 19
Access objects can depend on other objects. For example, a form might use a query as its data source, and the query in turn might draw its information from two or more tables. In this case, the form is directly dependent on the query, and indirectly dependent on the tables. Starting with Access 2003, this information is available through the Access user interface, and also programmatically.
If you haven't looked at this information in the Access user interface, it's easy enough to find. Right-click on any object in the Database window and select Object Dependencies. Doing so opens the Object Dependencies task pane, as shown in Figure 15.5. You can switch between displaying the objects that depend on this object, and the object that this object depends on, by using the radio buttons at the top of the task pane.
CAUTION
For the object dependencies feature to work, Name AutoCorrect must be on for Access. You can turn on this option using the General tab of the Tools, Options dialog box. You must save and close your objects before Access can generate their dependency information.
To discover object dependencies in VBA, you go through the AccessObject object to the DependencyInfo object. Here's some code that shows you how this works:
Public Sub ShowDependencies[intType As AcObjectType, _ strName As String] ' Show dependency information for the specified object Dim AO As AccessObject Dim AO2 As AccessObject Dim DI As DependencyInfo On Error GoTo HandleErr ' Get the AccessObject Select Case intType Case acTable Set AO = CurrentData.AllTables[strName] Debug.Print "Table: "; Case acQuery Set AO = CurrentData.AllQueries[strName] Debug.Print "Query: "; Case acForm Set AO = CurrentProject.AllForms[strName] Debug.Print "Form: "; Case acReport Set AO = CurrentProject.AllReports[strName] Debug.Print "Report: "; End Select Debug.Print strName ' Get the dependency info Set DI = AO.GetDependencyInfo[] ' Print results If DI.Dependencies.Count = 0 Then Debug.Print "This object does not depend on any objects" Else Debug.Print "This object depends on these objects:" For Each AO2 In DI.Dependencies Select Case AO2.Type Case acTable Debug.Print " Table: "; Case acQuery Debug.Print " Query: "; Case acForm Debug.Print " Form: "; Case acReport Debug.Print " Report: "; End Select Debug.Print AO2.Name Next AO2 End If If DI.Dependants.Count = 0 Then Debug.Print "No objects depend on this object" Else Debug.Print "These objects depend on this object:" For Each AO2 In DI.Dependants Select Case AO2.Type Case acTable Debug.Print " Table: "; Case acQuery Debug.Print " Query: "; Case acForm Debug.Print " Form: "; Case acReport Debug.Print " Report: "; End Select Debug.Print AO2.Name Next AO2 End If ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description, vbCritical Resume ExitHere End Sub
That's a big chunk of code, but if you take it one step at a time it will be pretty simple to understand by now. The first order of business is to retrieve the AccessObject about which the user requested information. The procedure requires two arguments: the type of object and the name of the object. Because Access already provides an enumeration [acObjectType] for the possible object types, you can use that same enumeration here; that's simpler than defining your own. The first Select Case statement does two things with this constant. First, it determines which object collection to use to return the correct AccessObject object. Second, it prints a message to the Immediate window with the object's name and type.
The next step is to retrieve the DependencyInfo object, which you do with the GetDependencyInfo method of the AccessObject. The DependencyInfo object in turn has two collections of its own, each of which also contains AccessObject objects. The Dependencies collection contains one AccessObject object for each object that the current object depends on. The Dependants collection contains one AccessObject object for each object that depends on the current object.
The remaining code in the procedure simply loops through these two collections and prints their contents to the Immediate window. Here's what the result is for one object in the TimeTrack database:
ShowDependencies acQuery, "Schedule" Query: Schedule This object depends on these objects: Table: Clients Table: Projects Table: Tasks These objects depend on this object: Report: Schedule
| To demonstrate how you might use the object collections in a user interface, let's extend the MasterForm form assembled in Chapter 13 to include reports. You'll add a listbox to the form to list all the reports in the database, and a button to display the selected report. The tricky thing about this is that some reports can't be displayed without further information. In the TimeTrack sample database, the BillingReport report requires the BillingReportSetup form to be open. You can handle this requirement by attaching a custom property to the report. Because there's only one report that needs the custom property, it's hardly worth writing a procedure to set it. There's no reason not to do this directly from the Immediate window. This example calls the property NeedsForm, and sets it to the name of the required form name. So, to set up BillingReport with this property, you execute this code in the Immediate window: CurrentProject.AllReports["BillingReport"]. _ Properties.Add "NeedsForm", "BillingReportSetup" The next step is to add the appropriate controls to the MasterForm form: a listbox named lstReports and a command button named cmdOpenReport. We chose to make the form a bit taller and to add these controls at the bottom of the form. Set the Row Source Type of the listbox control to Value List. Next comes the code behind the form to populate the listbox and to hook up the command button: Private Sub Form_Load[] ' Stock the listbox with the names of ' all reports in the database Dim AO As AccessObject For Each AO In CurrentProject.AllReports lstReports.AddItem [AO.Name] Next AO End Sub Private Sub cmdOpenReport_Click[] ' Open the selected report or the ' form required to launch it Dim AO As AccessObject Dim strForm As String On Error Resume Next If Not IsNull[lstReports.Value] Then ' Retrieve the appropriate AccessObject Set AO = CurrentProject.AllReports[lstReports.Value] ' Check for the custom property strForm = AO.Properties["NeedsForm"] If Err = 0 Then ' Got back a property value, open that form DoCmd.OpenForm strForm Else ' Property doesn't exist, open the report DoCmd.OpenReport AO.Name, acViewPreview End If End If End Sub When you load the form, it iterates through the AllReports collection, adding every report's name to the listbox on the form. You haven't made any provision for selectively hiding reports from this form, but you can use the same technique that you saw earlier on the FormList form to do so. When the user clicks the button to open the report, the code retrieves the AccessObject representing the report. It then tries to retrieve a value for the NeedsForm property. If it gets a value back, it opens the form with that name. Otherwise, it opens the report in Print Preview view. Figure 15.6 shows the finished form. If you open the Schedule report, it opens in Print Preview view directly. If you open the Billing report, it opens the appropriate form to prompt you for data instead. |
Page 20
|
Page 21
| IN THIS CHAPTER What's ADO and Why Do You Need It? 237 Using the ADO Connection Object 238 Working with Command Objects 242 Understanding the Different Types of Recordsets 244 Creating and Opening a Recordset 245 Filtering Recordsets 247 Using the Recordset Property 248 |
Page 22
The data you enter into an Access database isn't actually stored in the database objects. Forms, tables, reports, and even queries are just interface objects by which you input, view, and retrieve data. Fortunately, Access handles the data exchange behind the scenes you don't need to know what's going on or even that it is going on. You benefit from the process, regardless.
Things get a bit more complicated when you start using code to interact with your data. That's where the ActiveX Data Objects [ADO] library comes into the picture. In a nutshell, ADO is an object model that provides a few specialized objects for retrieving data. ADO is your ticket to accessing all types of data, not just Access data.
You don't have to do anything special to get ADO it's already there. Starting with Windows 2000, ADO comes with and is installed with the operating system. In addition, ADO has been the Access data access library default since Access 2000, so you don't even have to reference the library to use the objects.
The ADO Object Model
Like other object models, the ADO object model is a hierarchy of objects. Figure 16.1 is an illustration of the ADO object model. The model consists of collections of specific object types. King of the ADO hill is the Connection object, which represents a single connection to an OLE DB data source. This chapter reviews the ADO Connection, Command, and Recordset objects.
NOTE
ADO and OLE DB work together. OLE DB interfaces with the data and translates the data processing components from one format to another using two types of components: providers and consumers.
Providers are programs that talk to one another [expose data]. Consumers then use the exposed data.
ADO is a consumer. In other words, OLE DB connections let you actually connect to the data and ADO objects let you retrieve the data after you're connected.
Page 23
You need a connection to a data source and an object in which to store the data you retrieve from the data source. Technically, an ADO Connection object is a single connection to an OLE DB data source. What that means is that you can use the Connection object to connect to a data source.
You can connect implicitly or explicitly and neither method is more correct than the other. However, if you're going to use the same connection more than once, use an explicit Connection object. An implicit connection is created whenever you retrieve data without first declaring a Connection object. You might find it less confusing to use explicit Connection objects until you feel comfortable with the ADO object model.
Opening the Connection
An explicit connection actually declares and instantiates a Connection object as follows:
Dim cnn as ADODB.Connection Set cnn = New ADODB.Connection
We can't really show you an implicit connection because there's no declaration.
The Connection object comes with a number of properties that control the object's behavior:
ConnectionString Specifies the data source.
ConnectionTimeout Determines how many seconds to wait for a data source to respond. The default is 15, which might not be long enough if you're on a busy network or pulling data across the Internet.
Mode Sets the permission mode. See Table 16.1 for the intrinsic constants for this property.
Constant
Explanation
adModeRead
Read-only connection
adModeReadWrite
Read-write connection
adModeWrite
Write-only connection
adModeShareDenyRead
Other applications can't open a read connection
adModeShareDenyWrite
Other applications can't open a write connection
adModeShareDenyNone
All applications can open a connection with any permission
adModeShareExclusive
Other applications can't open a connection
adModeRecursive
Subrecords inherit permissions of current record
CursorLocation Determines the location of the OLE DB provided cursor. There are two constants: adUseServer sets a server-side cursor and adUseClient sets a client-side cursor. You can think of a cursor as a set of rows from a table plus an indicator of the current row.
DefaultDatabase Specifies a specific database on a server to use for all data processes with this particular Connection object.
IsolationLevel Controls how database operations on different connections affect one another. See Table 16.2 for a list of intrinsic constants. You don't need to worry about this unless you're writing code that works with multiple users logged into the database at the same time.
Constant
Explanation
adXactUnspecified
Returned when provider can't determine the isolation level
adXactChaos
adXactBrowse
Default setting that protects pending changes from being overwritten
adXactReadUncommitted
adXactCursorStability
Enables you to view but not change uncommitted changes from other transactions
adXactReadCommitted
Enables you to view changes from other transactions only after they're committed
adXactRepeatableRead
adXacIsolated
Enables requerying a Recordset to include changes pending from other transactions
adXactSerializable
Isolates all transactions from all other transactions
Provider Specifies an OLE DB provider before opening the connection.
CommandTimeout Specifies how long to wait before terminating an attempt to connect while executing a command.
Just remember to set the properties before actually opening the connection. For instance, to set a server side cursor, you use the following code:
Dim cnn as ADODB.Connection Set cnn = New ADODB.Connection cnn.CursorLocation = adUseServer
To open the connection, use the Connection object's Open method in the form
Connection.Open [connectionstring][, userID][, password][, options]
All the arguments are optional. The options argument is one of two intrinsic constants: adConnectUnspecified opens a synchronous connection [one on which only a single operation can proceed at one time] and is the default; adAsyncConnect opens an asynchronous connection. Using the default synchronous connection is typically fine for any operation that doesn't involve an extremely large amount of data.
About Connection Strings
There are two opportunities to specify the connection string:
Use the Open method's connectionstring argument after creating the Connection object.
Use the Connection object's connectionstring argument after creating the Connection object, but before opening the actual connection.
Either way, certain information can be passed in the form of five arguments that are concatenated together in the connection string:
Provider Specifies the name of the OLE DB provider; check provider documentation for the exact string. See Table 16.3 for a list of common provider strings.
Application
String
Microsoft Jet 3.51
Microsoft.Jet.OLEDB.3.5.1
Microsoft Jet 4.0
Microsoft.Jet.OLEDB.4.0
ODBC Drivers
MSDASQL
Oracle
MSDAORA
SQL Server
SQLOLEDB
Data Source Identifies the file to which you're connecting.
Remote Provider Specifies the server provider when opening a client-side connection.
Remote Server Identifies the server.
URL Identifies the connection string as a URL.
TIP
If you need help constructing a connection string, a visit to //www.connectionstrings.com/ can likely solve all your problems.
The following connection string connects to the Northwind sample database that comes with Access on your local system from any database, assuming that it's installed in the default location:
Private Sub MakeConnection[] Dim cnn As ADODB.Connection Dim strConn As String Set cnn = New ADODB.Connection strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\" & _ "Office\Samples\Northwind.mdb;" cnn.Open strConn MsgBox "Connection Made" cnn.Close Set cnn = Nothing End Sub
Just open a standard module and enter the procedure. Then, press F5. If the connection is made, Access displays the message shown in Figure 16.2. Click OK to clear the message box. This example establishes the data source as it opens the actual connection. [You can use any database as the Data Source argument, just be sure to type the complete and correct path.]
Closing a Connection
The previous example doesn't do anything but connect to a data source. No data is retrieved. But you might have noticed the Close method at the end of the procedure. It's best to always disconnect the Connection object when you're done by executing the Close method. You don't have to destroy the object, because you might want to use it again.
The Close method takes the form
connection.Close
where connection represents a Connection object.
To reuse a closed Connection object, just execute the Open method. When you're actually done with the Connection object, set it to Nothing after closing the object as follows:
Set connection = Nothing
TIP
More often than not, you can use a simple connection shortcut. If the data you need is in the current database, use the CurrentProject object to define the connection as follows:
Dim cnn As ADODB.Connection Dim cnn As New ADODB.Connection Set cnn = CurrentProject.Connection
Using this method, you'll share exactly the connection to the data that Access itself is using.
Page 24
You can use the Connection or the Command object to retrieve and manipulate data. However, the Command object has a few advantages:
Using a Command object, you can avoid a Recordset object and update data directly, which can be faster with some databases because the changes are made on the server instead of dragging the records across the network.
The Command object supports parameters; the Connection object doesn't.
The Command object has more properties than the Connection object, which enables you to fine-tune its behavior.
By way of a definition, you can compare the Command object to a stored procedure or other data access object that returns data because the Command object executes the process that actually returns the data. The object does so by executing code against the OLE DB data source or by retrieving data from the OLE DB data source. This makes the Command object a flexible tool to have around.
Creating a Command Object
Creating a Command object is similar to creating a Connection object: declare it and then define it. But you don't open it, which makes sense given the difference between the two objects. Use the following structure to declare and define a Command object:
Dim strConn As String Dim cmd As ADODB.Command strConn = connectionstring Set cmd = New ADODB.Command cmd.ActiveConnection = strConn
The Command's connectionstring argument is identical to that used by the Connection object.
Executing the Command Object
The Command object executes code against the data source that manipulates that data in some way either changing it at the source or retrieving it. Usually, you'll use a Recordset object to retrieve data, you'll read about that object a bit later.
To execute code that updates or retrieves data, use the Command object's CommandText property, which contains the actual instructions executed against the data [technically, the provider]. This property is a string expression or value that contains a provider command, which can be a SQL statement, the name of a table, a URL, or a call to a stored procedure.
The following procedure automates a simple update query that increases each of the hourly rate values [in the Timetrack.mdb Tasks table] by three percent using a SQL UPDATE command directly against the data:
Private Sub EditCA[] 'Update hourly rate values by 3 percent. Dim strConn As String Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd .ActiveConnection = CurrentProject.Connection .CommandText = "UPDATE Tasks " & _ "SET HourlyRate = HourlyRate + [HourlyRate * .03]" .Execute End With Set cmd = Nothing End Sub
Enter the procedure into a standard procedure [or use Chapter 16's example module]. With the cursor inside the procedure, press F5. Nothing seems to happen, but open the Tasks table and you can see that each value has been updated, as shown in Figure 16.3. The first value, $154.50, was $150.00. All the values have been increased.
Page 25
Modifying data at the source is quick and efficient if you don't need to continually review the data. Sometimes you need to actually retrieve the data. For instance, you might want to evaluate specific values before making a change.
Typically, you retrieve data using the Recordset object. You can think of a Recordset object as a simple data container, which you can execute via the Connection or Command object. If you could see it, a Recordset would look very much like an Access table opened in Datasheet view.
On the technical side of things, a Recordset object represents a cursor. A cursor is often described as the pointer to the current record, but it's really more. It's all the retrieved data, with a pointer to the current record. There are four types of cursors [Recordset objects]:
A dynamic cursor enables you to see changes made by other users. In addition, the cursor [or pointer] can move in all directions. Jet doesn't actually support this type of cursor and defaults to a keyset cursor if you specify a dynamic cursor.
A keyset cursor is dynamic, but you can't see records added or deleted by other users, although you can see changes made to existing data. You can move the cursor in all directions. If only one user opens the database at a time, a keyset cursor and a dynamic cursor are equivalent.
A static cursor creates a copy of the data. You can't change the data or see changes made by other users. You can move the cursor in all directions.
A forward-only cursor is a static cursor that restricts movement. You can only move forward through the records. This is the default ADO cursor.
How you need to use the data determines the type of cursor you choose. The dynamic cursor is the most flexible but requires the most resources [and isn't available in Access]. Dynamic cursors can be slow to perform. On the other hand, the forward-only cursor usually responds quickly and requires fewer resources, but it's the most restrictive of the four you can't change data and you can't move backward through the records.
CAUTION
Microsoft Jet doesn't support dynamic cursors, even though, through ADO, you can request one from Jet. When you do, Jet returns a keyset cursor, which does not show you records added by other users that would appear in a true dynamic Recordset. The best workaround is to request your Recordset often so you can work with the most up-to-date data.
Page 26
Create a Recordset by using the Dim keyword to declare it in the form
Dim recordset As ADODB.Recordset Set recordset = New ADODB.Recordset
Populating a Recordset is as simple as opening it. To do so, use the Open method in the form
recordset.Open source[, activeconnection][, cursortype][, locktype][, options]
where recordset represents a Recordset object. Table 16.4 lists the method's arguments.
source | Identifies the data ADO uses to populate the Recordset object. This setting can be the name of a Command object, a SQL statement, the name of a table, a stored procedure, a persisted Recordset, a Stream object or a URL. |
activeconnection | Specifies the connection to use to grab the data identified by source. Use a Connection object or a connection string. |
cursortype | Specifies the type of cursor and is one of four intrinsic constants: adOpenDynamic, adOpenKeyset, adOpenStatic, adOpenForwardOnly, which are similar to the cursor types. |
locktype | Specifies the record-locking behavior and is one of the intrinsic constants listed in Table 16.5. |
options | Supplies additional information that the provider might need. Table 16.6 lists the intrinsic constants for this argument. |
adLockReadOnly | This default option creates a read-only Recordset. |
adLockPessimistic | Records are locked while being edited. |
adLockOptimistic | Records are locked only when updated. |
adLockBatchOptimistic | Used with the UpdateBatch method to update multiple records with a single operation. |
adCmdUnknown | The default option; supplies no additional information. |
adCmdText | Identifies the CommandText property as a stored procedure [the actual commands, not the name of the procedure]. |
adCmdTable | Identifies the CommandText property as the name of a table. |
adCmdStoredProc | Identifies the CommandText property as the name of a stored procedure. |
adCmdFile | Identifies the CommandText property as the name of a file. |
adCmdTableDirect | Identifies the CommandText property as the name of a table. The resulting Recordset is the only type that can support the Seek method. |
adAsyncExecute | Executes the command asynchronously. |
adAsyncFetch | Specifies that the cache should be filled synchronously and then additional rows fetched asynchronously. |
adAsyncFetchNonBlocking | Retrieves records asynchronously if possible without blocking the main thread. |
As you can see, there can be a lot to consider when opening a Recordset object. However, the good news is that most of the time the operation is really very simple. For instance, the following procedure populates a default Recordset object with the contents of the Clients table:
Private Sub ClientsRst[] Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.Open "Clients", CurrentProject.Connection MsgBox "Connection Made" rst.Close Set rst = Nothing End Sub
If you decide to run it from a standard module, you'll find that it really doesn't do anything. It creates the Recordset object, but it doesn't do anything with the data that the Recordset object contains. However, this particular method is probably the most succinct for creating a Recordset object.
TIP
If you're just learning ADO, be careful about setting the Recordset type. By default, ADO opens a forward-only Recordset type, which is different from DAO. DAO defaults to a table, and then a dynaset, and then a snapshot, and finally a forward-only type. ADO retains the forward-only type only for compatibility.
One problem you might run into when trusting defaults is counting records. The forward-only Recordset type doesn't support the RecordCount property. If you need to use this property, be sure to set a static or keyset cursor.