list boxes (2000)

Topic

New Reply

Is it possible to display the contents of a list box in more than one column?

Reply | Quote

Replies

Yes, list boxes have columns just like comboboxes. Perhaps if you explain specifically what you’re trying to do, someone can help you with it.

Reply | Quote

Thanks

I have a records of 565 products – I have set up a form with a list box that (thanks to some help from the lounge a while ago) allows more than one product to be selected and then I have a command button which runs a query which runs a report to show details of the products selected.

565 items is quite a lot of rows to scroll through and I was rather hoping I could display them on a form in say 4 columns –

It is not that I want to display more than one field – I want to display one field in more than one column!!!

If anyone has any bright ideas I would be eternally grateful!

Reply | Quote

A list box can display data from different fields in different columns, but not data from one field in different columns.

If there is a way of dividing the products into categories, and if the user will always select one or more products from a single category, you can use cascading list boxes: the user first selects a category from a first list box (or combo box); this makes a second list box display only products from that category.

Reply | Quote

A list box can display data from different fields in different columns, but not data from one field in different columns.

If there is a way of dividing the products into categories, and if the user will always select one or more products from a single category, you can use cascading list boxes: the user first selects a category from a first list box (or combo box); this makes a second list box display only products from that category.

Reply | Quote

Think for a moment. When you make a selection in a list box, you select the item, meaning the row. If you had multiple values on the same row (which isn’t possible, as Hans pointed out), Access would have no way to determine which one you selected.

Reply | Quote

Think for a moment. When you make a selection in a list box, you select the item, meaning the row. If you had multiple values on the same row (which isn’t possible, as Hans pointed out), Access would have no way to determine which one you selected.

Reply | Quote

Could you just place 4 separate list boxes side by side, each set to display roughly a quarter of the records, or a specified letter range.

Have a hidden text box that is used as the real query parameter, and set its value in the after update event each of list box.

Reply | Quote

Could you just place 4 separate list boxes side by side, each set to display roughly a quarter of the records, or a specified letter range.

Have a hidden text box that is used as the real query parameter, and set its value in the after update event each of list box.

Reply | Quote

(Edited by MarkD on 23-Nov-04 05:19. Replaced attachment – fixed minor bug in code.)

About the only way you might be able to do this is, is to split the records listed in listbox into separate lists, based on some logical (or illogical) criteria. You could then generate the filter string for report by concatenating the selected items from each list. I usually do this for separate fields but no law says you can’t do it for same field. If interested see attached demo database (A2K format). Demo uses Northwind Products table. Open Form1, select multiple items, then preview or print report (in demo, a crude wizard-generated report). Report will be filtered by selected products from each listbox. Clear the listboxes to view all items in report. In this example I simply broke the products into four groups based on alphabet. You could also use categories or some other criteria. The two textboxes on bottom of form show first, the actual “Where” string used to filter report, the second displays a more user-friendly list of the selected items. In actual use you would not display the first textbox, it would remain hidden to users. See form module for code used to generate the strings. Note the ProductID (number) is used to filter report, not actual product name.

You may be able to adapt this technique for your own project.

HTH

Reply | Quote

Nice

How would change the queries to dynamically use a variable to load say 25 items in each query?

Thanks, John

Reply | Quote

If you have about 550 products don’t you need about 140 per list – ie. 25% rather than 25.

The easy solution is to make fixed breakup based on the name of the product. It doesn’t really matter if the four lists contain different numbers of products.

Select ProductID, ProductName from tblProducts where (productName < 'f") ORDER BY productName;
Select ProductID, ProductName from tblProducts where (productName Between "f" And "M") ORDER BY productName;

etc

Do do it dynamically would take a lot more work.

This will get the first list

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName;

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName Desc;
would get the bottom 25% in reverse order, so you could then have a second query to sort these back into the right order.

To get the middle groups, I am not sure. One possibility is to use subqueries..

SELECT TOP 34 PERCENT ProductID, ProductName FROM tblProducts
WHERE (PruductID Not In (select ProductID from qrytop))
ORDER BY productName;

where qrytop is the the query that fives the top 25%.

It does not really matter if there is some overlap between the lists, but it does matter if there is a gap between them.

Reply | Quote

If you have about 550 products don’t you need about 140 per list – ie. 25% rather than 25.

The easy solution is to make fixed breakup based on the name of the product. It doesn’t really matter if the four lists contain different numbers of products.

Select ProductID, ProductName from tblProducts where (productName < 'f") ORDER BY productName;
Select ProductID, ProductName from tblProducts where (productName Between "f" And "M") ORDER BY productName;

etc

Do do it dynamically would take a lot more work.

This will get the first list

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName;

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName Desc;
would get the bottom 25% in reverse order, so you could then have a second query to sort these back into the right order.

To get the middle groups, I am not sure. One possibility is to use subqueries..

SELECT TOP 34 PERCENT ProductID, ProductName FROM tblProducts
WHERE (PruductID Not In (select ProductID from qrytop))
ORDER BY productName;

where qrytop is the the query that fives the top 25%.

It does not really matter if there is some overlap between the lists, but it does matter if there is a gap between them.

Reply | Quote

If the number of records to list per listbox is to vary dynamically, one approach would be to create “temporary” queries programatically which would then be used to populate the listboxes on form. As test created some queries “manually” using TOP predicate & subqueries (similar to examples John Hutchinson posted) but this quickly became cumbersome – using NOT IN with more than one subquery resulted in very slow queries even with a small number of records. So instead used DAO methods to create the querydefs to return x number of records, based on user input on form. The function that generates the queries first determines the number of queries to create (based on number of records in table, and on value of x) then runs a loop. Excerpt of code:

For n = 1 To lQueryCount
strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _
"FROM PRODUCTS WHERE ProductID " & _
"In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _
"ProductID FROM PRODUCTS " & _
"ORDER BY ProductName Desc) " & _
"ORDER BY ProductName;"

Set qry = db.CreateQueryDef("Temp" & n, strSQL)

See attached revised demo database (A2K) for full code and details. Note above, to avoid dreaded NOT IN syntax the subquery selects records from the bottom, not top, calculated number of records, by simply sorting products in descending order in subquery. When function called from form to repopulate listboxes, there is little delay, as might be case if using NOT IN. To test, open Form1, enter valid value in the unbound, unlocked textbox, then click “Reset” button to requery listboxes. If number of queries is less than number of listboxes, code clears the “extra” listboxes. The listbox labels are also updated to reflect which records are listed. If actually using this, may want to add code to clean up the “temp” queries when form closes. Or ditch the querydefs & just use SQL strings as RowSource to populate the listboxes.

Hope this gives you some ideas.

Reply | Quote

The thought of searching through that number of records in a list box …

The original enquiry was for a multi select listbox. I wonder whether the better interface would be single select listbox with its contents filtered according to A B C buttons (Hans did a nice one a while ago). Each selection made could then be added to a temporary table and shown in a second list box. Once all selections had been made, the results would be in the temporary table.

Reply | Quote

The thought of searching through that number of records in a list box …

The original enquiry was for a multi select listbox. I wonder whether the better interface would be single select listbox with its contents filtered according to A B C buttons (Hans did a nice one a while ago). Each selection made could then be added to a temporary table and shown in a second list box. Once all selections had been made, the results would be in the temporary table.

Reply | Quote

Sweet!

This gives me some ideas for some other uder interfaces I have been thinking about and haven’t gotten to yet.

Nice job.

Thanks, John

Reply | Quote

Sweet!

This gives me some ideas for some other uder interfaces I have been thinking about and haven’t gotten to yet.

Nice job.

Thanks, John

Reply | Quote

Another idea on this is to have a temp table.
Clear its contents using a Delete query,
Refill with all products using an append query,
delete any records in the top 25% and bottom 25%, (using subqueries]
then select the top and bottom 50% of the remaining records.

All this could use fixed queries that could be saved and reused.

Reply | Quote

Another idea on this is to have a temp table.
Clear its contents using a Delete query,
Refill with all products using an append query,
delete any records in the top 25% and bottom 25%, (using subqueries]
then select the top and bottom 50% of the remaining records.

All this could use fixed queries that could be saved and reused.

Reply | Quote

If the number of records to list per listbox is to vary dynamically, one approach would be to create “temporary” queries programatically which would then be used to populate the listboxes on form. As test created some queries “manually” using TOP predicate & subqueries (similar to examples John Hutchinson posted) but this quickly became cumbersome – using NOT IN with more than one subquery resulted in very slow queries even with a small number of records. So instead used DAO methods to create the querydefs to return x number of records, based on user input on form. The function that generates the queries first determines the number of queries to create (based on number of records in table, and on value of x) then runs a loop. Excerpt of code:

For n = 1 To lQueryCount
strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _
"FROM PRODUCTS WHERE ProductID " & _
"In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _
"ProductID FROM PRODUCTS " & _
"ORDER BY ProductName Desc) " & _
"ORDER BY ProductName;"

Set qry = db.CreateQueryDef("Temp" & n, strSQL)

See attached revised demo database (A2K) for full code and details. Note above, to avoid dreaded NOT IN syntax the subquery selects records from the bottom, not top, calculated number of records, by simply sorting products in descending order in subquery. When function called from form to repopulate listboxes, there is little delay, as might be case if using NOT IN. To test, open Form1, enter valid value in the unbound, unlocked textbox, then click “Reset” button to requery listboxes. If number of queries is less than number of listboxes, code clears the “extra” listboxes. The listbox labels are also updated to reflect which records are listed. If actually using this, may want to add code to clean up the “temp” queries when form closes. Or ditch the querydefs & just use SQL strings as RowSource to populate the listboxes.

Hope this gives you some ideas.

Reply | Quote

Nice

How would change the queries to dynamically use a variable to load say 25 items in each query?

Thanks, John

Reply | Quote

(Edited by MarkD on 23-Nov-04 05:19. Replaced attachment – fixed minor bug in code.)

About the only way you might be able to do this is, is to split the records listed in listbox into separate lists, based on some logical (or illogical) criteria. You could then generate the filter string for report by concatenating the selected items from each list. I usually do this for separate fields but no law says you can’t do it for same field. If interested see attached demo database (A2K format). Demo uses Northwind Products table. Open Form1, select multiple items, then preview or print report (in demo, a crude wizard-generated report). Report will be filtered by selected products from each listbox. Clear the listboxes to view all items in report. In this example I simply broke the products into four groups based on alphabet. You could also use categories or some other criteria. The two textboxes on bottom of form show first, the actual “Where” string used to filter report, the second displays a more user-friendly list of the selected items. In actual use you would not display the first textbox, it would remain hidden to users. See form module for code used to generate the strings. Note the ProductID (number) is used to filter report, not actual product name.

You may be able to adapt this technique for your own project.

HTH

Reply | Quote

Thanks

I have a records of 565 products – I have set up a form with a list box that (thanks to some help from the lounge a while ago) allows more than one product to be selected and then I have a command button which runs a query which runs a report to show details of the products selected.

565 items is quite a lot of rows to scroll through and I was rather hoping I could display them on a form in say 4 columns –

It is not that I want to display more than one field – I want to display one field in more than one column!!!

If anyone has any bright ideas I would be eternally grateful!

Reply | Quote

Yes, list boxes have columns just like comboboxes. Perhaps if you explain specifically what you’re trying to do, someone can help you with it.

Reply | Quote