Solved

Listbox, sort selected items first then... Ms Access

Posted on 2007-04-10
15
292 Views
Last Modified: 2008-02-01
I have a code which selects, say, 20 out of 50 items from a list box. These 50 items are sorted alphabetically (regardless they are selected or not).

I am looking for code to display the selected items first (sorted) followed by the rest (sorted).

How this can be done?

Thanks.
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 75
ID: 18885952
In the same list box, right?

mx
0
 
LVL 75
ID: 18885966
Also, is the list populated from a qry or SQL in the row source?

mx
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 18885997
Yes, the same listbox. The rowsource is populated via a query. Which reads its data yet from another query.

Mike
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 18886421
how many columns Mike?
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 18886847
One column.
0
 
LVL 75
ID: 18886867
And so ... then after you made the selections, you would click a button and that would resort the list in the manner specified?

mx
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 150 total points
ID: 18887122
Mike, we need to see the code that "selects" the 20 out of 50. I think we're either going to have to load the selections into an array, or a temp table, to sort them, then feed them back to the listbox as a field list. Seems the easiest to me.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 350 total points
ID: 18887164
Probably ... if I was going to do this ... the drift would be:

I would have a sort flag in the underlying table
I would read the selected items and then one way or another, using that info (keys, etc) I would:
1) Run an update query and clear the sort flag field
2) run an update query and set the Sort field to True
3) Apply the SQL/query to the RowSource of the list box ... resulting in the desired sort.  

I pretty much do that in a report engine I have ... involving multi-select list boxes for criteria selection.  One of the features is that the user can sort the list by the different columns (if there are more than one).  And the cool thing is ... when you apply the new query ... the same items are still selected!

This works pretty well for maybe 100-200 items max in the multi-select list box.  The multi-select list box has some performance issues when you start loading several 100 items, clearing, selecting All, etc.

You might look at :

http://www.dbi-tech.com/ctList.asp

This company makes some VERY cool stuff.  I've user their tree view control in Access ... and it's 125.83 times better and cooler than the Access tree view control ... not to mention ... it has DOCUMENTATION !!!

So ... you spend a little money ... and you get a LOAD of very cool tools!

mx
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18887242
The "harvest" of the selected items in the listbox, would then have to be queried out of the remaining 50. You have to have a way to keep them separated while you sort them....then simply write them to the table in with the 20 sorted items first, then the remaining 30 below that.

To break down the steps involved?

1) something triggers your code to run that selects the 20 items.
2) as soon as that happens, we transition into the sorting routine
3) the items are put back into the listbox in the order you want
4) Are the 20 items still selected at this point?
5) The user can now select different items than the original 20 if he wants
6) the user pushes a button, or some other event fires to do something with the Newly selected items
7) What happens with the list NOW? Another sort order loop for the top 20 again?
0
 
LVL 75
ID: 18887302
jw ... are you asking me or him?

fwi ... House and Boston (ill)Legal will be on shortly ... so, I will be intermittent, if not incoherent, lol ...
Can you hold it down ok?  Man ... it's like only you and I are on duty today.  Hey ... only 10.8K more pts ... and I hit the jackpot, lol ... and I git to print out another cool 'diploma'  :-)

mx
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 18887588
I will go over your comments tomarrow. But, I want to let you know all items in the list box are sorted to begin with like:

Adam
Ben
Cindy
David
.
.

If user selects Ben and David and then fires the code in question, it is expected to deliver:

Ben         'selected
David      'selected
Adam
Cindy
.
.

In VB, I guess one could read them in array and repopulate the combo box according to their index.  Here we will see what are the possibilities.

Thank you,

Mike
0
 
LVL 75
ID: 18887710
Yes .... I understand what you want ... and it's a cool idea and makes sense.  Sadly, nothing in the list box inherently makes this easy to do.  The concept is simple, but just a bit of work to implement.

mx
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 18894256
How about chaging its setting to a UserDefined function (run-time) where you can populate back the list box in any fashion (order) is is desired?

Mike
0
 
LVL 75
ID: 18894308
"How about chaging its setting to a UserDefined function "

Not sure what you mean?

mx
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 18894381
Basically there are four ways to populate a list box:

RowSource Type
- Table/Query
- Value List
- Field List
- UserDefinedFunction.

With the last one, although not part of options provided, you type:

=fnMyListBox()

for RowSource Type and leave the rowsource itsel blank. From Access help, you could see how a user defined function like this gets to be populated (very powerful, I have use it to maintain 2,500,000 items in a list box).

Mike
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question