Here is the ffil97v2.txt "readme" text file with full details on FFil 2.0:
**** Use Edit-WordWrap for best viewing in Notepad ****
-----------------------
FFil 2.0 for Access 97
-----------------------
Steve Nyberg - October 12, 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 FFil Version 2.0:
-----------------------------
FFil Version 2.0 is a free utility for filtering forms in Microsoft Access 97 databases.
It contains objects which can be imported into your database to filter forms using
dropdown lists for fields, expressions, and values. There are many options for creating,
running, and managing filters.
What's New In Version 2.0:
--------------------------
Overall, FFil 2.0 is now very similar to the report filtering utility RFil 5.0, borrowing
much of the code, the screens, and techniques for filtering.
The main form found in FFil 1.0 has been enhanced. You can now create full AND/OR logic on
the 'Custom Form Filtering' menu, and the filter SQL can be viewed. Checks have been added
to prompt when changed filters are about to be closed without saving.
A new form and table are used to control the value dropdown lists. In addition to being
dynamic as in the first version, value dropdown lists can now be customized to show
hard-coded value lists, SQL strings, or saved queries for better performance.
A new report documents all of the saved form filters in your database.
Most programming code has been split out into a separate module, with several bug fixes
and enhancements. Its also DAO-specific and uses SQL Server friendly recordset
manipulation.
The form setup menu has a new option for AutoExpand behavior and the recordsource type.
Upgrade Note To Users Of FFil 1.0:
----------------------------------
This version will NOT alter the previous installation of FFil 1.0 you may have in your
database - they can be used simultaneously. None of the code or tables are shared between
versions, so you must run setup again to prepare FFil 2.0.
Setup:
------
To use FFil 2.0 in your own Access 97 database, follow these steps:
Unzip the .zip file, or double-click the .exe file for FFil 2. Extract the files and note
the new location.
From within your database, import every object named like "z_FFil2.." from the
FFil database (.mdb file). This includes 4 tables, 3 forms, 1 report, 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_FFil2_frmSetup" and click the red 'Pop' button in the upper
right. This will prepare the FFil form information automatically.
In the Setup form, edit the Custom Form Names in the leftmost column to any descriptions
you'd like. If FFil has included a form which you don't want to make available for
filtering, delete that record (click in the record and choose Edit-DeleteRecord from the
menubar). Set the checkboxes for allowing dynamic value dropdowns and the autoexpand
behavior when users start typing a value. Also, the dropdown for views allowed lets you
limit or default how the users can see the forms.
FFil 2.0 is now ready to use in your database!
Quick User-Guide:
-----------------
Once FFil 2.0 is setup in your database, here are the basics for filtering forms.
Custom Form Filtering Menu:
The "z_FFil2_frmCustom" form is the primary filtering menu in FFil 2.0.
Features:
Form 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)
Filter/Count buttons for applying filters to forms, or returning record counts.
Save/SaveAs/New/Delete/Rename buttons for managing filters.
View dropdown for displaying forms in Datasheet or standard Form view.
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_FFil2_frmCustom". Choose a form 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 see the filtered form, click the Funnel-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 form. 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 form. 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.
Advanced Features and Technical Issues:
---------------------------------------
This section is intended to explain some of the advanced features and issues in FFil 2,
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 form. 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 form 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_FFil2_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. (RFil 5 users note: FFil
2's form and table for this function is separate from RFil 5's.)
IMPORTANT NOTE: Settings in this form OVER-RIDE the "Fill Value Dropdowns"
checkbox on the Setup form and on the filtering form. For example, you can set up a form
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 forms that use the exact
field name in the recordsource; you don't have to set this for each form 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 form will use this value list. Also, the opposite is true. You can have the
form 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:
FFil 2.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 FFil 2.0 uses the DAO (Data Access Object) model and references. If
you import FFil 2's objects into a Access 2000 database without a DAO 3.6 reference (such
as a newly created mdb), the main FFil module will not compile. However, if you add a
reference to Microsoft DAO 3.6 or similar, it should work. See my website
(http://www.stevenyberg.com/access/rfil/) for an overview of this process.
Second, this version is not designed to work with Access Data Project files (.adp/.ade),
the new alternate file format in A2K. You may be able to get the Custom Form Filtering
menu to work adequately, but this is doubtful.
Details on How FFil 2.0 Works:
The Setup form "Pop" button uses code to loop through all forms, opening them in
hidden 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, FFil has to skip the form. It can only filter on forms with
saved tables or queries for a recordsource. Similar code runs for the "<-Get"
button to retrieve individual form information. The form dropdown list uses the
MSysObjects table to get a list of forms. No easier way!
The Custom Menu uses the "z_FFil2_tblForm" table to get a dropdown list of forms
available for filtering, table "z_FFil2_tblFormFilters" has a list of all saved
Custom filters for each form, and "z_FFil2_tblFormFiltersContents" 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 form records in the Setup form. Cascade deletes
will delete all of your filters! If you don't have cascade deletes, and you clear and
repopulate your forms on the Setup form, you will have orphaned filters!
The last table, "z_FFil2_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 ff2FillPicklists
function refers to this table to populate the Custom menu dropdowns.
The SQL generation engine in FFil has changed little since FFil 1 and RFil 3. I decided
against the BuildCriteria method during extensive testing of RFil 5 - it failed on too
many sample criteria, with bad error handling! FFil and RFil both use 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 FFil and 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 FFil 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, FFil 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.
I did not include an equivalent to the "Query-Based Filtering Menu" found in
RFil 5.0. An ambitious developer could modify it to work with FFil, but I'm slowly moving
away from the saved-query technique for filtering, and I didn't include it in FFil 2.0.
The new FFil report should be helpful if you want to see all of your filters and their
criteria. The Custom filter report ("z_FFil2_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.
Security Recommendations For FFil:
If your Access 97 database has standard security implemented, here are a few tips. First,
all users should have permission to open the Custom Filtering form. 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_FFil2_tblValueDropdownContents" and "z_FFil2_tblForm". 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 the Custom filtering menu.
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 forms: 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 form 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 form.
Why Isn't FFil an Add-In?
After an in-depth review of what would be required to make FFil and RFil into reliable
Access add-ins, I decided to keep them simple. The Access 97 Developer's Handbook shows
exactly how to do an add-in, but I decided that it's easy enough to import the objects and
run the setup. This way, you have complete control over installing the utility.
Why doesn't FFil include sorting/ordering?
I looked into adding this capability, and decided not to include it in this release. It's
possible it will be added to a future release. Sorting is easy to do once a form is open,
by right clicking, etc. Plus, there may be performance hits when users are sorting on
non-indexed fields on big databases or client-server back-ends. Since the usefulness is
limited, and the performance issues could be big, I decided not to add this feature to
FFil 2.0.
Where's the sample data?
To make FFil smaller and to make the object importing simpler, I removed all sample
filters and forms. See FFil 1.0 for examples that will also work in FFil 2.
Why peach?
Besides visually distinguishing FFil from its big brother RFil 5, I like the color peach!
:) If you don't, then you can always set it back to gray!
FFil 2.0 Objects:
-----------------
Tables:
z_FFil2_tblForm
z_FFil2_tblFormFilters
z_FFil2_tblFormFiltersContents
z_FFil2_tblValueDropdownContents
Forms:
z_FFil2_frmCustom
z_FFil2_frmSetup
z_FFil2_frmValueDropdownContents
Report:
z_FFil2_rptCustom
Module:
z_FFil2_basMain
Licensing:
----------
FFil 2.0 for Access 97 is distributed as freeware.
Please let me know if you have any questions, suggestions, or spot any bugs in FFil 2. You
can e-mail me at stevenyberg@yahoo.com or stevenyberg@earthlink.net.