More Information on RFil 5.0 for Access 97:
Here is the "readme" text file included in the RFil 5.0 download.
**** Use Edit-WordWrap for best viewing in Notepad ****
----------------------
RFil 5.0 for Access 97
----------------------
Steve Nyberg - 9/21/1999
email: stevenyberg@earthlink.net
or email: stevenyberg@yahoo.com
web page: http://www.stevenyberg.com
or web: http://home.earthlink.net/~stevenyberg/access/
Overview of RFil Version 5.0:
-----------------------------
RFil Version 5.0 is a free utility for filtering reports in Microsoft Access 97 databases.
It contains objects which can be imported into your database to filter reports using two
different techniques. Custom Filtering uses dropdown lists for fields, expressions, and
values. Query-Based Filtering uses standard Access 97 queries for filtering reports. Both
techniques include many options for creating, running, and managing filters.
What's New In Version 5.0:
--------------------------
The multi-purpose form with separate tabs (RFil 4) has been split apart into separate
forms - one for Custom Filtering, one for Query-Based Filtering, and one for Setup. The
majority of code is now in a standalone module instead of CBF. Overall, because of its
added functionality, RFil 5.0 contains more objects than version 4.0.
Most changes are related to the new Custom Filtering Menu (formerly the Basic Report
Filtering Tab). You can now save and manage unlimited filters from this menu, 'OR' logic
is now supported with up to 4 levels, and the filter SQL can be viewed. In addition to
being dynamic, value dropdown lists can now be customized to show hard-coded value lists,
SQL strings, or saved queries for better performance.
Setup has been improved with a new menu for managing the reports available for filtering.
You can now specify 'user-friendly' names to refer to your reports, and other report
settings are much easier to add or update.
Documentation of your filters is now possible with two new reports: one shows details
about your Custom filters, and the other shows your Query-Based filters. Both show report
names, filter names, and SQL criteria.
Upgrade Note To Users Of RFil 4.0:
----------------------------------
This version will NOT alter any previous installation of RFil you may have in your
database - they can be used simultaneously. Because of enhancements to report info setup,
the version 4 report info table is not used, so you need to run setup for version 5 to
begin using it. However, Query-Based filters in RFil 5 are shared with the Advanced Tab of
RFil 4, so you don't have to rebuild them.
Setup:
------
To use RFil 5.0 in your own Access 97 database, follow these steps:
Unzip the .zip file, or double-click the .exe file for RFil 5. Extract the files and note
the new location.
From within your database, import every object named like "z_RFil5.." from the
RFil database (.mdb file). This includes 4 tables, 4 forms, 2 reports, and 1 module. (To
import objects, choose File-GetExternalData-Import, then browse to the .mdb file, then
highlight the objects, and click OK to import.)
Open the form "z_RFil5_frmSetup" and click the red 'Pop' button in the upper
right. This will prepare the RFil report information automatically.
(Note: For very large numbers of reports, over 40 or so, your PC could run low on memory,
partly because of the poor memory usage of Access reports. If this occurs, you may receive
error messages, and might need to reboot and/or finish the RFil setup process manually by
entering report info on this form.)
In the Setup form, edit the Custom Report Names in the leftmost column to any report
descriptions you'd like. If RFil has setup a report which you don't want to make available
for filtering, click in the record and choose Edit-DeleteRecord from the menubar.
RFil 5.0 is now ready to use in your database!
Quick User-Guide:
-----------------
Once RFil 5.0 is setup in your database, here are the basics for filtering reports.
Custom Filtering Menu:
The easiest and most powerful filtering tool is the Custom Filtering Menu - the
"z_RFil5_frmCustom" form.
Features:
Report dropdown list.
Saved filter dropdown list.
Four tab pages for filter criteria, each with 6 rows of field, expression, and value
dropdown lists. (some lists are initially hidden)
Print/Preview/Count buttons for running filtered reports.
Save/SaveAs/New/Delete/Rename buttons for managing filters.
FillValues/AutoExpand checkboxes for better performance with filters.
Reset/ResetTab/ResetAll buttons for clearing criteria by row, tab, or all tabs.
RefreshSQL button and textbox for viewing the filter SQL string.
Feature Walk-Through:
Open the form "z_RFil5_frmCustom". Choose a report from the dropdown list. In
the first "Field" box on the first tab page, choose any field, then
"=" for "Expression", then type in or select a "Value" from
its dropdown list, which may have a short delay. To see the record count, click the
"COUNT" button. To preview the filtered report, click the Preview-image button.
To directly print the filtered report, click the Printer-image button. Also, you can see
the SQL by clicking the "Refresh SQL" button in the lower right.
Click "Save" to save your filter, enter a name when prompted. Your filter now
appears in the "Filter" dropdown list which contains all saved custom filters
for the selected report. Click "Save As" to save a second copy of this filter
with a different name. Click "Rename" and enter a new name for this copy of the
filter. Click "Delete" to delete this second filter. Click "New" to
make a new filter for this report. Enter in any criteria on the first row of the first
tab. Add another row of criteria on this same tab (AND logic). Then add a row of criteria
to the 2nd tab (OR logic). Click the "Refresh SQL" button to see what this
filter looks like. While viewing the 2nd tab, click "Reset Tab" to clear this
tab. On the 1st tab, click the small "Reset" button left of your second criteria
row to reset the row. Now click the Close-image button in upper left. You'll receive a
warning message about abandoning your filter. If you say YES, the form will close and your
changes to the filter will be dropped. If you say NO, it will not close the form. You must
either abandon or save a changed filter to move on.
Query-Based Report Filtering:
The second method for filtering reports is the 'Query-Based Filtering Menu", which is
the form named "z_RFil5_frmQueryBased". (Note: filters are not shared between
this method and the Custom filtering method.)
Features:
Report dropdown list.
Saved "query-filter" list box.
Print/Preview/Count buttons for running filtered reports.
Edit/New/Delete/Copy/Rename buttons for managing query-filters.
"On Filter Double-Click" radio buttons for quick editing, previewing, printing,
or counting of filtered reports.
Refresh Filter List to refresh the list of query-filters for the report.
"Use Filter" checkbox for running filtered or unfiltered reports.
"Preview Filter Info Report" button for viewing the documentation report of all
query-filters in the database.
"All Filter Names Start With" textbox with the characters that must start all
query-filter names ("rf" is the default. Changing requires re-coding and is not
recommended).
Feature Walk-Through:
Open the form named "z_RFil5_frmQueryBased". Choose a report from the dropdown
list. Click the "New" button to make a new query-filter for this report. Enter
any valid query name starting with the "rf" characters. (Invalid names will
receive a detailed error message.) The standard query design grid will appear; edit this
query with any criteria and close/save when done. Back at the menu, you'll see this query
in the listbox. Highlight this query-filter, and click the "Count" button to
count the records in the filtered report. Click the Preview-image button to preview it,
and the Print-image button to directly print the filtered report. Click the
"Edit" button to return to the query design. Select View-Datasheet view from the
menubar to get a datasheet preview of the records. Close the query and return to the menu.
Highlight the filter and click "Rename"; enter a new valid name. Highlight the
renamed filter and click "Copy"; enter a new valid name for the copy. Click No
when asked to edit the new filter. Highlight the copied filter and click the
"Delete" button to delete it. In the "On Filter Double-Click radio group,
select Count Only. Now double-click on a filter in the filter listbox to get the filter
record count.
Advanced Features and Technical Issues:
---------------------------------------
This section is intended to explain some of the advanced features and issues in RFil 5,
and is meant for an experienced developer or administrator.
Controlling the RowSource of Value Dropdown Lists:
Setup Form:
The two checkbox columns on the right side help to determine the behavior of value
dropdown lists on the Custom menu. "Fill Value Dropdowns" is the default way -
'checked' means the default is to get a dynamic SQL string of sorted, distinct values for
that field from the recordsource of the report. This is very handy to users, but can have
a performance hit on larger databases or on non-indexed fields. 'Unchecked' means the
default for the report is to not show any values in the dropdown list - the user has to
type in the value. This has the best peformance, but users might not like it.
The "AutoExpand" checkbox is another usability/performance tradeoff - when
checked, the value list (if populated) will find matches as they type. When unchecked,
performance may be much better, but the user has to mouse-click to get the matching value.
Value Dropdown Contents Form:
To give you more options in this usability/performance tradeoff, I've added the form
"z_RFil5_frmValueDropdownContents". Here you can enter field names and specify
if they get filled dynamically, filled by a hard-coded list of values
(North;South;East;West; etc), filled by an SQL Select statement, or filled by a saved
query name. (The Test dropdown will preview your list.) The priority goes left to right;
if dynamic is checked, it ignores anything in the values or SQL textboxes and makes a
dynamic SQL of distinct values. If the dynamic is not checked, but the value list is
entered, it will ignore the SQL textbox and use the value list.
IMPORTANT NOTE: Settings in this form OVER-RIDE the "Fill Value Dropdowns"
checkbox on the Setup form and on the Custom form. For example, you can set up a report
with that field unchecked, but have a few of its fields with a value list or SQL string as
a lookup. Also note the field will behave this way across all reports that use the exact
field name in the recordsource; you don't have to set this for each report the field is a
part of. For example, make one record with field "Region" and with value list
"North;South;East;West". Any Custom filtering done on the "Region"
field for any report will use this value list. Also, the opposite is true. You can have
the report checked for dynamic, but enter a few field names here with nothing - no check,
value list, or SQL string. The field will never show a list of values. This may be helpful
for timestamp, currency, memo, or other field types where a dynamic list is slow and not
especially useful.
Upgrading To Access 2000:
RFil 5.0 for Access 97 has been designed for Access 97. However, it can be upgraded to
Access 2000 for most applications. There are a few issues, however.
First, this version of RFil 5.0 uses the DAO (Data Access Object) model and references. If
you import RFil 5's objects into a Access 2000 database without a DAO 3.6 reference (such
as a newly created mdb), the main RFil module will not compile. However, if you add a
reference to Microsoft DAO 3.6 or similar, it should work.
First, this version is not designed to work with Access Data Project files (.adp/.ade),
the new alternate file format in A2K. The Query-Based menu will certainly fail, because
ADP's use Views instead of queries. In my experience, you can filter reports based on
views if you manually enter in "Table" for the recordsourcetype in the RFil
setup table. I don't know why this works. You may be able to get the Custom menu to work
adequately, but this is doubtful. Wildcards like * are different in ADP. You will have to
build or upsize all 4 tables in your data backend (SQL Server 7 or MSDE) preferably with
cascade deleting. The table and field names as they are now should be acceptable to SQL
Server. I can't test Oracle. You will have to tweak with permissions for the end-users on
any upgraded back-end.
Details on How RFil 5.0 Works:
The Setup form "Pop" button uses code to loop through all reports, opening them
in design view and reading the recordsource. This is the only way to get this property
(that I know of). If the recordsource is a SELECT statement, Null, or is an object name
which isn't a table or query, RFil has to skip the report. It can only filter on reports
with saved tables or queries for a recordsource. Similar code runs for the
"<-Get" button to retrieve individual report information. The report dropdown
list uses the MSysObjects table to get a list of reports. No easier way!
Both the Custom Menu and the Query-Based menu use the "z_RFil5_tblReport" table
to get a dropdown list of reports available for filtering. The Custom Menu also uses the
other 2 tables: "z_RFil5_tblReportFilter" has a list of all saved Custom filters
for each report, and "z_RFil5_tblReportFiltersContents" contains the criteria
for each filter. These three are all related 1 to many, with cascade deletes (not
critical). Use GREAT caution if you delete report records in the Setup form. Cascade
deletes will delete all of your Custom filters! If you don't have cascade deletes, and you
clear and repopulate your reports on the Setup form, you will have orphaned filters!
The last table, "z_RFil5_tblValueDropdownContents", has special settings for
populating the value dropdowns on the Custom menu for certain fields. The form for editing
this table has some code of its own to make test dropdown lists. The rf5CFillPicklists
function refers to this table to populate the Custom menu dropdowns.
The SQL generation engine in RFil has changed little since RFil 3. I decided against the
BuildCriteria method after extensive testing - it failed on too many sample criteria, with
bad error handling! RFil uses a proven technique of concatenating SQL pieces with valid
delimiters (# for date, " for text), and expression syntax (eg:
In("NY","NJ","CA") or Between #1/1/1999# and #12/31/1999# )
and combining multiple AND and OR pieces correctly. Much of the code came from an old
Access 2.0 utility for creating form and report filter menus - I don't know the author,
but if I did I'd give lots of credit to him or her!
One problem still outstanding in RFil is handling non-system-default currencies from
dropdown lists. For now, it appears the user has to edit out the foreign currency symbol
manually so RFil can convert it to a numeric value. Otherwise the SQL is invalid. Related
to this is entering decimal criteria in systems where the decimal character is a comma and
periods are the thousands separator. I couldn't find a good fix for these problems - email
me if you have ideas.
Another problem is with Long Date fields, such as January 1, 2000. When the field has this
format, and you select this text from the value dropdown, RFil places # symbols around it,
but Access cannot recognize it as a date. I recommend entering in formats like 1/1/2000 or
1-Jan-2000 etc.
The Query-Based Filtering Menu works quite differently from the Custom menu. This menu has
changed little since RFil version 3. (I can't think of many ways to change it!) When you
select a report, it performs a quick search of the queries in your database to find those
which are based on the recordsource (column 2 of the report picklist). By definition, a
filter is directly based on the main object's recordsource. Because of this, a
query-filter does not have to be unique to 1 report - it can be used on any report with
the correct recordsource. Combined with the recommended first few characters of RFil
query-filters (default is "rf"), it can fill the listbox with the available
filters very quickly. When creating a new filter, it will make a new query based on the
recordsource of the current report, and editing is done in the standard Access QBE grid.
The drawbacks include lack of expression and value lists for users to select from, runtime
users cannot edit these filters, and each filter is its own query object in your database.
Advantages include easy parameter filters and very complex logic or function calls.
The new RFil reports should be helpful if you want to see all of your filters and their
criteria. They use very different techniques however! The Custom filter report
("z_RFil5_rptCustom") pulls in all 3 main tables and uses some SQL If Thens and
other tricks to display the filter criteria and the AND/OR grouping levels. The
Query-Based filter report ("z_RFil5_rptQueryBased") uses code similar to that in
its menu for finding all RFil query-filters and their WHERE SQL string. Not a pretty
report, but its functional.
Security Recommendations For RFil:
If your Access 97 database has standard security implemented, here are a few tips. First,
all users should have permission to view 2 forms - the Custom and Query-Based forms. Only
the database owner should be able to view the Setup or Value Dropdown forms, or to modify
design of all forms and tables. All users should have read-only on tables
"z_RFil5_tblValueDropdownContents" and "z_RFil5_tblReport". When they
add new reports, they should request its record being added to the Setup form by the
administrator. Its up to you if they should be read-only, edit,insert, or delete on the
other two tables, but a user's permissions should end up being the same for both tables.
Runtimers can have full use of Custom, but limited use of Query-Based (they can't edit or
create new filters). You can lock down individual query-filters through permissions, but
you need to give ReadDesign/ModifyDesign to New Queries if they are to make new
query-filters. Also, I recommend giving only Open/Run permissions on your existing
reports.
The above permissions are for Access storage of the 4 tables. If you intend to store these
in another database format, such as SQL Server or Oracle, please contact your system
administrator or DBA to help setup permissions.
Another important consideration if you have sensitive data visible in reports: the Custom
menu can have real data visible in the dropdown lists. By scanning through fields and
values, it is possible for a user to see all the values for all of the fields! In effect,
they don't need to see the report to see the data it contains! To prevent this, either
limit the value dropdown lists as described earlier, or rethink your permissions on the
query behind the report.
Why Isn't RFil an Add-In?
After an in-depth review of what would be required to make RFil a reliable Access add-in,
I decided to keep it simple. The Access 97 Developer's Handbook shows exactly how to do an
add-in, but the more I looked into it, the more complicated it would be to make a
bulletproof add-in. Plus, the add-in concept is great for tools used all the time, but
it's not essential for tools used once per database, like RFil. I prefer to spend time
designing and debugging important components, rather than installation code, which I
consider a 'bell and whistle'. Access has enough bells and whistles already, right?
:)
RFil 5.0 Objects:
-----------------
Tables:
z_RFil5_tblReport
z_RFil5_tblReportFilters
z_RFil5_tblReportFiltersContents
z_RFil5_tblValueDropdownContents
Forms:
z_RFil5_frmCustom
z_RFil5_frmQueryBased
z_RFil5_frmSetup
z_RFil5_frmValueDropdownContents
Reports:
z_RFil5_rptCustom
z_RFil5_rptQueryBased
Module:
z_RFil5_basMain
Licensing:
----------
RFil 5.0 for Access 97 is distributed as freeware.
Please let me know if you have any questions, suggestions, or spot any bugs in RFil 5. You
can e-mail me at stevenyberg@yahoo.com or stevenyberg@earthlink.net.