Multiple listbox's

Creating and using forms

Multiple listbox's

Postby cowboy1611 » Wed Nov 02, 2011 4:28 pm

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.
OpenOffice 3.2 on Windows Vista
cowboy1611
 
Posts: 21
Joined: Mon Oct 31, 2011 5:54 pm

Re: Multiple listbox's

Postby r4zoli » Mon Nov 07, 2011 9:50 am

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   Expand viewCollapse view
SELECT "Field1" FROM "Table1"'

to listbox1
Code: Select all   Expand viewCollapse view
SELECT "Field1" FROM "Table1" WHERE "Field1" <= 'H''

to listbox2:
Code: Select all   Expand viewCollapse view
SELECT "Field1" FROM "Table1" WHERE "Field1" >= 'I'
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Multiple listbox's

Postby DACM » Mon Nov 07, 2011 10:23 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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. 8-)
List_Box_group_filter2.jpg
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:
Attachments
Students3 - (group list filter).odb
example Form > macro-driven > Students (group list filter)
(78.81 KiB) Downloaded 226 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Multiple listbox's

Postby donintosh » Wed Nov 23, 2011 8:13 pm

Good that you add the attachment. :super:
Neoteric Ways of Casino Cheating with Micro Earpiece
High-Tech Gadgets are at Work micro earpiece
video about 3g booster
User avatar
donintosh
Banned
 
Posts: 4
Joined: Tue Nov 22, 2011 6:03 pm
Location: X: youtube.com/watch?v=nl12RiYzzhA


Return to Forms

Who is online

Users browsing this forum: No registered users and 0 guests