Page 1 of 1
[Solved] Create a Form with the List Box and Subcategories
Posted: Fri May 02, 2014 10:58 pm
by 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?
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 3:32 am
by MTP
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 3:00 pm
by Thegame23
Thanks MTP,
Please take a look at my TestDB.odb file to get an Idea... can someone help me?
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 3:26 pm
by 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).
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 3:44 pm
by 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?
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 4:09 pm
by Villeroy
Source Type: SQL
Source:
Code: Select all
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
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
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.
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 4:36 pm
by DACM
Here's a (#2) power filtering example without macros. But it's not as elegant or as easy as (#4) List Content concatenation.
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 5:47 pm
by 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.
Re: [Help] Create a Form with the List Box and Subcategories
Posted: Sun May 04, 2014 6:28 pm
by 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!