Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    list boxes (2000)

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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list boxes (2000)

    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.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list boxes (2000)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: list boxes (2000)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list boxes (2000)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: list boxes (2000)

    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.
    Regards
    John



  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: list boxes (2000)

    <P ID="edit" class=small>(Edited by MarkD on 23-Nov-04 05:19. Replaced attachment - fixed minor bug in code.)</P>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

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list boxes (2000)

    Nice

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

    Thanks, John

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: list boxes (2000)

    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.
    Regards
    John



  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: list boxes (2000)

    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:

    <code> For n = 1 To lQueryCount</code>
    <code> strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _</code>
    <code> "FROM PRODUCTS WHERE ProductID " & _</code>
    <code> "In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _</code>
    <code> "ProductID FROM PRODUCTS " & _</code>
    <code> "ORDER BY ProductName Desc) " & _</code>
    <code> "ORDER BY ProductName;"</code>
    <code> </code>
    <code> Set qry = db.CreateQueryDef("Temp" & n, strSQL)</code>

    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.

  11. #11
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list boxes (2000)

    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.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list boxes (2000)

    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

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: list boxes (2000)

    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.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •