Multi select list box access
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 editorAs 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:
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.
Determining What's Selected and What's NotThe 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 2A 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:
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.
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
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
Page 4Text 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 BoxesYou 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.
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 FocusUsers 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 BoxesAn 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:
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 5Option 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:
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:
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 6Subforms 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 7Beginning 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:
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.
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:
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
Page 9
Page 10One 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 ReportIn 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.
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 ReportTo 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:
Page 11You 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. 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:
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 12The 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 OrderAt 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:
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 13Like 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:
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:
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 DataReports 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 14You 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.
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:
Table 14.4 lists the individual property settings for each of these properties.
CAUTION You can create new groups only in Design view, but you can set most group properties from the report's Open event.
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
Page 16You 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:
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
Page 18As 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:
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:
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 19Access 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
Page 20
Page 21
Page 22The 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 ModelLike 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 23You 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 ConnectionAn 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:
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 StringsThere are two opportunities to specify the connection string:
Either way, certain information can be passed in the form of five arguments that are concatenated together in the connection string:
TIP If you need help constructing a connection string, a visit to http://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 ConnectionThe 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 24You can use the Connection or the Command object to retrieve and manipulate data. However, the Command object has a few advantages:
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 ObjectCreating 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 ObjectThe 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 25Modifying 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):
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 26Create 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.
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. |