Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Recipe 3.13 Print Only Records Matching a Form's Filter



3.13.1 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?


3.13.2 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:

  1. 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.

  2. 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.

  3. 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 code for the cmdPrint button on frmAlbums is shown
    here:

    Private Sub cmdPrint_Click( )
    DoCmd.OpenReport "rptAlbums", View:=acPreview
    End Sub

    Change "rptAlbums" to the name of
    the report created in Step 2. Save the form and close it.

  4. Switch back to the report and create an event procedure attached to
    the report's Open event. Add code similar to that
    shown here for rptAlbums:

    Private Sub Report_Open(Cancel As Integer)
    Dim frmFilter As Form
    Const acbcFilterFrm = "frmAlbums"
    Const acbcFilterSubFrmCtl = "subAlbums"
    ' Is the the report's filtering form open?
    If SysCmd(acSysCmdGetObjectState, acForm, acbcFilterFrm) <> 0 Then
    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
    End If
    End Sub
  5. Change the value of the acbcFilterFrm constant to
    the name of the form and the acbcFilterSubFrmCtl
    constant to the name of the subform control created in Step 1. If
    your form

    doesn't include an
    embedded subform, either delete
    "(acbcFilterSubFrmCtl).Form" from
    the 11th line of code or completely delete this line of code and the
    two comment lines that follow and uncomment (remove the leading
    single quote from) the following line of code:

    ' Me.Filter = frmFilter.Filter

    You should also delete the following line of code if you
    aren't using a subform (although leaving it in
    won't hurt):

    Const acbcFilterSubFrmCtl = "subAlbums"
  6. If you wish to display the filter value on the report whenever the
    report is based on a filtered subset of records, add a text box
    control to the page footer (or any other section you prefer) and name
    this control txtFilter. Next, add the following code to an event
    procedure attached to the section's Format event:

    Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    ' If this report is filtered, make the txtFilter control visible
    ' and set its value to the Filter property of the report.
    If Me.FilterOn Then
    Me.txtFilter = Me.Filter
    Me.txtFilter.Visible = True
    Else
    Me.txtFilter.Visible = False
    End If
    End Sub
  7. Save the report and close it. You can test the report by opening the
    filtering form, choosing various filters, and then pressing the Print
    Records button on the form.



3.13.3 Discussion


This solution works by setting the
report's Filter property to the value of the
form's Filter property. The form's
and report's Filter properties contain the last
filter created for the object. Because the last filter hangs around
even after you've turned it off (by using the
Records Remove Filter/Sort command or the equivalent
toolbar button), the code in Step 4 first checks the status of the
FilterOn property. This property is set to True
when a filter is active and False when there is no
filter or when the existing filter isn't currently
active.

At the beginning of the
report's Open event procedure, the code checks to
see if the form associated with this report is open, using the
following code:

If SysCmd(acSysCmdGetObjectState, acForm, acbcFilterFrm) <> 0 Then

SysCmd is a function that handles a number
of different chores including the following:

  • Displaying a progress meter or text in the status bar.

  • Returning status information about Access (such as the Access
    directory, whether the runtime or retail product is running, and so
    on).

  • Returning the state of a database object to indicate whether it is
    open, is a new object, or has been changed but not saved.


You indicate to Access which flavor of

SysCmd
you want by passing it an enumerated value as the first parameter.
(See the online help topic for the SysCmd function for more
information on the possible parameter values.) The code in the Open
event procedure passes

SysCmd the
acSysCmdGetObjectState constant, which tells

SysCmd that you would like information on the
open status of the frmAlbums form.

SysCmd
obliges by returning one of the values listed in Table 3-11 (the value 3 is skipped so that any
combination of values added together will result in a unique number).
In this case, you care only if the

SysCmd return
value is nonzero.

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
optionalit provides a nice added touch.

The optional code in Step
5which we added to the page footer's Format
event in the sample reportdocuments 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).


/ 232