Multiple listbox's

[cowboy1611]

This question was started in another topic, which was tagged as solved, so I decided to start a new one.

Currently I have a FORM, with a dropdown/list box which has several predefined choices.
Eventually I'll need to add more, so I was thinking the list would be quite long and if I could divide it into 2-3 separate lists that would be convienient.

So what I'm thinking is when I click on my listbox, it would present 2-3 choices which would also be listbox's.
e.g. say the original listbox had the alphabet a-z, now what I would like is to have the 1st listbox present listbox1
which would contain A thru H, and listbox2 which would have I thru Z.

[r4zoli]

It can be solved, add three listbox to your form.

Open property browser of listbox, by double click, select "Data" tab.

Bound these listboxes to same data field "Field1".

Use "Type of list content" property: sql, in all cases.

In "List content" property add query to listbox, like this:

Code: Select all

SELECT "Field1" FROM "Table1"'

to listbox1

Code: Select all

SELECT "Field1" FROM "Table1" WHERE "Field1" <= 'H''

to listbox2:

Code: Select all

SELECT "Field1" FROM "Table1" WHERE "Field1" >= 'I'

[DACM]

Very nice r4zoli

Yes, there's no such Form Control that embeds List Boxes within List Boxes. But something similar can be done with separate List Boxes using SQL as r4zoli demonstrates.

Here's another variation using SQL to populate two, cascading List Boxes. This example is derived from a 'macro-driven' example Form but the macros are only used to eliminate 'refresh' Push Buttons. Otherwise, there's no other macro-code associated with the Form whether List Box population or filtering.

See the 'List contents' of the individual List Boxes...

Range (A-Z)...List Box 1 > List Content:

Code: Select all

SELECT MIN(LEFT("LASTNAME",1)) || '-' || MAX(LEFT("LASTNAME",1)) AS "A-Z" FROM "Original" WHERE LEFT("LASTNAME",1) BETWEEN 'A' AND 'F' 
UNION 
SELECT MIN(LEFT("LASTNAME",1)) || '-' || MAX(LEFT("LASTNAME",1)) AS "A-Z" FROM "Original" WHERE LEFT("LASTNAME",1) BETWEEN 'G' AND 'R' 
UNION 
SELECT MIN(LEFT("LASTNAME",1)) || '-' || MAX(LEFT("LASTNAME",1)) AS "A-Z" FROM "Original" WHERE LEFT("LASTNAME",1) BETWEEN 'S' AND 'Z'

Last Name...List Box 2 > List Content:
Note: "CITY" is used (below) to store the user selection of List Box 1 ("A-F" or "G-R" or "S-Z") within the filter-record (WHERE ID=0). That's not intuitive, but when using a filter-record to store random List Box selections, sometimes you've just got to pick an unused column with a compatible data-type. Here's where a dedicated filter-table might be nicer overall. But then again, this is just a quick demo...

Code: Select all

SELECT  DISTINCT "LASTNAME", "LASTNAME" FROM "Original" 
WHERE (( 
LEFT( "LASTNAME", 1 ) BETWEEN ( SELECT LEFT( "CITY", 1 ) FROM "Original" WHERE "ID" = '0' ) 
AND ( SELECT RIGHT( "CITY", 1 ) FROM "Original" WHERE "ID" = '0' )
))

The results:
Note: "S-W" is not a typo in the List Box 1 selections below. This value is derived on-the-fly by the SQL, indicating that there's no last-name starting with "X, Y, or Z" in the existing table data.

SQL UNION, string-functions, and BETWEEN qualifiers combine to generate the contents of these 2 List Boxes on-the-fly

See the Form named 'Students (group list filter)' among the example Forms below:

[donintosh]

Good that you add the attachment.