Filter table taking a date range, 2 integers and a text with max. 20 characters:
Tools>SQL...
Code: Select all
CREATE TABLE "Filter"(D1 DATE, D2 DATE, INT1 INTEGER, INT2 INTEGER, TXT VARCHAR(20), FID TINYINT PRIMARY KEY);
INSERT INTO "Filter" VALUES(NULL,NULL,NULL,NULL, NULL,0);
[Execute], [Close]
menu:View>Refresh Tables.
Now you should see a new table "Filter" with one record with primary FID=0 and 5 blank criteria values.
Create a new form linked to source type SQL with source: SELECT * FROM "Filter" WHERE "FID"=0
The following form properties are important too:
Allow modification: YES (we want to edit the distinct record)
Allow insertion: NO (otherwise the user can tab into a new record below the distinct record which might have happened to you)
Allow deletion: NO (we want to keep the distinct record)
Navigation toolbar: NO (we only have one distinct record)
Let's start with one list box for a single integer value and disregard the other 4 criteria fields.
Linked field: INT1
Source type: SQL
Source: SELECT "SomeText" AS "Visible", "PrimaryKey" FROM "CustomerTable" ORDER BY "Visible" (assuming a "CustomerTable" with some text and primary key)
Add 2 push buttons with actions "Save record" and "Undo data entry"
Now you should be able to enter a listbox value at record #0, field "INT1" of your filter table.
Add a subform with no master/slave field:
Source type: SQL
Source: SELECT * FROM "YourTable","Filter" WHERE ("YourTable"."CustomerID"="Filter"."INT1" OR "Filter"."INT1" IS NULL) AND "Filter"."FID"=0
This selects all the records of your table where some aleged "CustomerID" in "YourTable" equals the filter INT1 at the record with FID #0. In case of a missing value (when "Filter"."INT1" IS NULL) the expression returns True anyway and all records will be returned.
Add one refresh button or use the one on the navigation tool bar.
Test the form. Since the fitered subform's refresh button takes the focus away from the filtering parent form, you may omit the save button. Taking away the focus stores a modified record anyway.
You will notice that your data form is read-only. This is because the source involves 2 merged tables, "YourTable" and "Filter". Record sets are writable if they include all mandatory fields, including the primary key, of a single table. If you need to edit the filtered data, edit the data source of the subform:
SELECT * FROM "YourTable" WHERE ("YourTable"."CustomerID"= :paramCustomerID OR :paramCustomerID IS NULL)
Same as above but instead of referring to the filter record, it introduce a parameter :paramCustomerID (leading colon, no quotes, no spaces in the name).
We can substitute this variable with a parent form's value:
Master field: INT1
Slave field: parameterCustomerID (name without leading colon)
---------------------
For the next form with filter criteria just add a record with FID=1 to the filter table and use the fields you need or append new fields or use another filter table if you like.
The simple concept is that you first fill out a form with all filter criteria before you hit a push button to refresh the filtered result set. Any auto-refresh macro may be disturbing when you have multiple criteria and a large table (many thousands of rows) since every refresh may take a second or two.
--------------------
P.S.2: Why is this so complicated? Because it is not a built-in feature. When I saw all the awful variants of macro solutions some 10 years ago, I realized that the given tool set of form controls, listboxes and subforms provide everything you need but you have to provide some data storage because subforms only work with parent form values that are stored in some table. Simply give to your criteria values a home in a table and they will serve as filter criteria. If you do it right, this is a fool proof concept that does not require any macro coding.