[Solved] Create a Form with the List Box and Subcategories

[Thegame23]

Ciao a tutti,
I need to filter the results between subcategories.

Filter List Box Results:
es. I select Animals in the first List Box >> in the second I can choose, Dogs, Cats... and If I choose Dogs in the second List Box >> in the Third I can choose Alaskan, Bulldog etc...
Look at the attached file...

Can someone help me to create this in base?

[MTP]

I think this is what you are looking for: [Example #1] Filter/Search with Forms (leveraging SubForms)

[Thegame23]

Thanks MTP,
Please take a look at my TestDB.odb file to get an Idea... can someone help me?

[Villeroy]

I know of 4 methods:
1) Cascading list boxes with macros (for programmers)
2) "Power filtering" as hyperlinked by MTP (for power fiddlers). Possibly enhanced by a one-line refresh macro.
3) Using a form and subform with records in a grid control instead of list boxes (when there are dozends but not thousands of categories). Works well with ABC registers where you pick items by first letter.
4) Concatenating abbreviated categories and sub-categories in one list box so you type "SW 23456" into the focussed list box in order to get item "23456" from main category "SW" (very simple and efficient).

[Thegame23]

Hi Villeroy,
thanks again for the help!

I prefer the method 4: Concatenating abbreviated categories and sub-categories in one list box so you type "SW 23456" into the focussed list box in order to get item "23456" from main category "SW" (very simple and efficient).

Have you any example for method 4?

[Villeroy]

Source Type: SQL
Source:

Code: Select all   Expand viewCollapse view

SELECT "C"."Name"||' '||"SC"."Name" AS "Visible", "SC"."ID"
FROM "Categories" AS "C", "Subcategories" AS "SC"
WHERE "C"."ID" = "SC"."ParentID"
ORDER BY "Visible"

Bound field: 1 (which is the second one with "SC"."ID")
The first field labeled "Visible" is the category name concatenated with a space and the subcategory name.

It can not apply to your example db because you don't have a list of countries and a list of cities.
Tools>SQL...

Code: Select all   Expand viewCollapse view

CREATE TABLE "Cities"(
"N" VARCHAR(32) NOT NULL,
"CID" INT NOT NULL,
"ID" INT GENERATED BY DEFAULT AS IDENTITY,
FOREIGN KEY ("CID") REFERENCES "Country"("country_id"));
DROP TABLE "ItalyCity";
DROP TABLE "GermanyCity";
DROP TABLE "FranceCity";

View>RefreshTables

And with this structure in place:
Linked field: the CityID in your form
Source Type: SQL
Source:

Code: Select all   Expand viewCollapse view

SELECT "C"."Name"||' '||"SC"."N AS "Visible", "SC"."ID"
FROM "Country" AS "C", "Cities" AS "SC"
WHERE "C"."ID" = "SC"."CID"
ORDER BY "Visible"

Bound field: 1 (which is the second one with "SC"."ID")
The first field labeled "Visible" is the category name concatenated with a space and the subcategory name.

[DACM]

Here's a (#2) power filtering example without macros. But it's not as elegant or as easy as (#4) List Content concatenation.

[Villeroy]

Yet another extension of the same. Added a field of short country names to the country table, a form to map cities to countries, relations (I love relations) and another subform with a grid and a single list box.
[event]1<--n[event_city]n-->1[city] is a many-to-many relation so we should allow for many cities per event.

[Thegame23]

@DACM
Wow, really well done! Thank you very much!

@Villeroy
I don't know how to thank you! You are a great person! Thank you very much!

@DACM - @Villeroy
Now I've infinite options to complete my database!! XD Thanks guys!