Access Cookbook - Ken Getz [76]
Table 3-11. The SysCmd object state return values
SysCmd return value
Access constant
Meaning
0
None
The object either doesn't exist or is closed.
1
acObjStateOpen
The object is open, but not new or dirty.
2
acObjStateDirty
The object is in an unsaved state.
4
acObjStateNew
The object is new and in an unsaved state.
The next stretch of code does all the work:
Set frmFilter = Forms(acbcFilterFrm)
' Is the form currently filtered?
If frmFilter.FilterOn Then
' Set the report's filter to the subform's filter.
Me.Filter = frmFilter(acbcFilterSubFrmCtl).Form.Filter
' If the filter form didn't include a subform, use this
' (simpler) syntax instead:
' Me.Filter = frmFilter.Filter
Me.FilterOn = True
Me.Caption = Me.Caption & " (filtered)"
End If
If the form is currently filtered (i.e., if frmFilter.FilterOn is set to True, which in VBA is the same as just saying frmFilterOn), the report's filter is set to the form's filter. Because the subform control on the form is actually being filtered, we set the report's filter equal to the subform's filter.
Notice that we used "frmFilter(acbcFilterSubFrmCtl).Form.Filter" rather than "frmFilter(acbcFilterSubFrmCtl).Filter". This odd-looking syntax tells Access that you want the Filter property of the subform that the subform control contains, not the Filter property of the subform control itself (which doesn't have such a property).
If no subform is used on the form, you can simplify the statement to this:
Me.Filter = frmFilter.Filter
Next, the code sets the report's FilterOn property to True, which causes the report to be filtered using the previously set Filter property. Finally, the code changes the caption of the report so that "(filtered)" appears in the titlebar when you preview the report. This last statement is optional—it provides a nice added touch.
The optional code in Step 5—which we added to the page footer's Format event in the sample report—documents the filter by displaying it in a text box on the report. The syntax of the filter is the same as that of a SQL Where clause (without the WHERE keyword).
You may also wish to set the report's OrderBy property to the form's OrderBy property. If you do this, you must also check the status of the OrderByOn property, which is analogous to the FilterOn property. The syntax of the OrderBy property is similar to that of the SQL Order By clause (without the ORDER BY keyword).
3.14. Keep a Report from Breaking at an Inappropriate Place
Problem
On some of your reports, you use the Keep Together property to keep a whole group together or to ensure that a group header won't print without at least one detail item. When detail items are long, you may not want to keep an entire detail item together; however, you do want to have a reasonable number of lines under the header so that the header won't be the last line on the report page. How do you make a report start a new page instead of printing the group header with just a single detail line at the bottom of a page?
Solution
You can use an event procedure called from a report's Format event to evaluate the length of a report page before it actually prints and take an action (in this case, activating a page break control) only if certain criteria are met. This technique uses the acbConditionalBreak function and a page break control. This solution demonstrates how to use acbConditionalBreak to force a page break if there is not enough room to print at least one line of text from the detail section under a group header.
Open 03-14.MDB and print the report rptBadBreaks. This typical business-address report, which has its detail section's KeepTogether property set to Yes, occasionally prints a page with the Category group header as the last