Access Cookbook - Ken Getz [74]
blnShade = Not blnShade
That is, if blnShade was False, now it will be True, and vice versa.
Once the code has decided whether to shade the section, it sets the background color to the color value of gray or white, based on the value of blnShade, using the following If...Then...Else statement:
If blnShade Then
Me.Detail1.BackColor = acbcColorGray
Else
Me.Detail1.BackColor = vbWhite
End If
We used the built-in VBA constant for white, but there is no constant for gray, so we defined a value corresponding to the color gray earlier in the procedure, using the built-in VBA function, RGB. An easy way to determine the numeric values for colors is by selecting a section or a control in design view and using the color palette to set the desired color. Then you can read the color value off of the properties sheet. Another option is to use vbGreen, which looks good when previewing the report and also results in a pleasing gray color when printed on a black-and-white printer.
3.13. Print Only Records Matching a Form's Filter
Problem
You have a form that you use to view and edit your collection of record and CD albums. On the form, you've placed a command button that you use to print the records contained in the form's recordset. This works fine, but you'd like to enhance the functionality of the form so that when you filter records on the form and then print the report, only the filtered records will print. Is there any way to do this in Access?
Solution
Access includes properties (Filter and FilterOn) of forms and reports that you can use to manipulate form and report filters programmatically. This solution shows you how to use these properties to print on a report only those records filtered by a form.
Load 03-13.MDB and open the frmAlbums form. When you press the Print Records button, you should see the preview of a report, rptAlbums, which includes all 65 records from qryAlbums. Close the report and go back to frmAlbums, which should still be open. Now create a filter of the form's records using one of the Filter toolbar buttons or the Records → Filter command. For example, you might create a filter by using the Filter by Form facility (see Figure 3-30).
Figure 3-30. Filter by Form is used to filter records on frmAlbums
When you finish creating the filter, apply it. You should see a filtered subset of the records (Figure 3-31).
Figure 3-31. The records have been filtered, resulting in three records
Now press the Print Records button. You should see a preview of the same report, rptAlbums, this time filtered to match the records you filtered using frmAlbums. If you print the filtered report, you should see a report similar to the one shown in Figure 3-32.
Figure 3-32. The report includes only those records from the filtered form
To create your own report that synchronizes its records with those of a form's, follow these steps:
Create a new form or edit an existing one. The sample form, frmAlbums, is an unbound main form with an embedded subform bound to the qryAlbums query, but you can use any style of form you like.
Create a new report or edit an existing one that's based on the same record source as the form (or, if you are using an embedded subform, that's based on the same record source as the subform) from Step 1. Save the report and give it a name. The sample report is named rptAlbums.
Switch back to the form. Add to the form a command button with an event procedure that uses the DoCmd.OpenReport method to open the report from Step 2 in preview view. (For more information on creating event procedures, see the Preface.) The