Listbox Order

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I'm trying to create a wizardlike interface using a couple listboxes. I
know you've seen it before. You double click on an item in one listbox and
it "moves" it to the other. I used to approach it with a table and within
the table a yes/no field would determine which box it would be in. An
Update statement would be fired changing it from yes to no or vice versa and
then refreshing the listboxes. This doesn't seem to scale very well as I
would have to create a table for each instance of this technique. Just
seems ugly too. So I started using the AddItem and RemoveItem method to add
and subtract items from a list box. Well now I've run into another problem.
When I open the form the list is in alphabetical order because I initially
populate the box with a SQL statement. This is what I want. But if I move
the item to the "selected" listbox and then decide to move it back, it puts
it at the end of the listbox order and I'd like it to be back at where it
was, in alphabetical order. It seems the add and remove item just adds or
removes an item from a value list. So I guess my question is (finally!),
how does one reorder this so that it's back in alphabetical order? Is there
a way? I guess I could reissue a SQL statement with each move back and
forth and throw some code in there so that it excludes the items that are
already selected, but that seems like it could be terribly inefficient.
Should I be approaching this in another way? FYI, I have AccessXP

TIA,
Bill
 
I think you'll run into another problem as well. Since you are storing the changes
anywhere, the next time you open the form the listboxes will be back the way they started.
A table is probably the better way to go. You should be able to reduce it to one table per
two listboxes.

If you don't mind them resetting each time you open the form, then (in code) you would
have to add the item to the item list, run a sort routine on the list, then place the list
in the listbox.
 
Actually I don't mind them resetting each time. It's kind of for ad-hoc
queries. Basically the user is choosing a bunch of different parameters to
produce a report.

How would I go about sorting that list without having to requery the db? It
doesn't seem like there's a handy dandy function like
Me!LstBox1.Sort(Ascending) or some such. That would be awfully nice for a
situation like this. FYI, I have gotten it to work with requerying the db
but that still seems like an inelegant way of going about it.

Thanks Wayne,


Bill
 
Bill said:
I'm trying to create a wizardlike interface using a couple listboxes. I
know you've seen it before. You double click on an item in one listbox and
it "moves" it to the other. I used to approach it with a table and within
the table a yes/no field would determine which box it would be in. An
Update statement would be fired changing it from yes to no or vice versa and
then refreshing the listboxes. This doesn't seem to scale very well as I
would have to create a table for each instance of this technique. Just
seems ugly too. So I started using the AddItem and RemoveItem method to add
and subtract items from a list box. Well now I've run into another problem.
When I open the form the list is in alphabetical order because I initially
populate the box with a SQL statement. This is what I want. But if I move
the item to the "selected" listbox and then decide to move it back, it puts
it at the end of the listbox order and I'd like it to be back at where it
was, in alphabetical order. It seems the add and remove item just adds or
removes an item from a value list. So I guess my question is (finally!),
how does one reorder this so that it's back in alphabetical order? Is there
a way? I guess I could reissue a SQL statement with each move back and
forth and throw some code in there so that it excludes the items that are
already selected, but that seems like it could be terribly inefficient.
Should I be approaching this in another way? FYI, I have AccessXP

TIA,
Bill
Click to expand...

Interesting, Bill. I'm working on essentially the identical project at the
moment, an Ad Hoc query builder. I began keeping both lists in arrays, but
switched to a table after a bit. The table approach is working extremely
well. I have a Yes/No field in the table (Included) that I simply check or
uncheck to move a selection from Available to Included or vice versa. I'm
using a sequence number to allow ordering in the Included list. Available
always stays alphabetized because it's RowSource is a sorted query.

The two lists are for the output fields only. I have a continuous subform
in which the user can specify any number of "WHERE" criteria. I balked
originally at using the table for the same reason you cite, it seems
inelegant. But it is working out well, none the less. In addition to
making the coding quite simple, it makes it very easy to add additional
Fields to the Available list.

I gave a working prototype of the Query Builder to my boss on Friday. Just
got an email from him (he's pleased).

Randy
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I have a demo app in Acc97 that shows how to transfer items back &
forth between 2 list boxes. It also shows how to sort the items. I
used collections and callback routines as the row source for each list
box & sorted the collections. This was just an experiment to see if I
could sort collections. For a small number of items the sort is
lickety-split. The ideal sort is probably an array sort. Here is the
URL to the zip file that holds the demo app:

http://home.earthlink.net/~mgf00/SelectDemo.zip

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4rmTYechKqOuFEgEQL5XACgsL2XrSL9cfjaVad6Wvti0fnkqHAAoNJ6
YVbh1f6Qkt3790mH+dKM3qAh
=sg9x
-----END PGP SIGNATURE-----
 
Bill,

Isn't it a simple matter of having an "Order By" clause in the SQL query
that serves as your listbox row source?
 
If I were to use a table behind it it would be. But since I'm not, I
need to use a Value List as the row source. Much like MGFoster (Thanks
for the link, I'll check it out) this is a bit of an experiment. I was
thinking about using a collection or an array and that may be the way I
go. I am curious mostly because if I get into programming VB (which I
most likely will soon) I won't have tables to fall back on. Plus I just
like knowing different ways of doing things and seeing the results with
my own eyes. :) I'll have about 100 items so I'm not sure if this is
too large for an array or collection. You know, it's the whole
bandwidth vs. computing power conundrum.
 
. . . I'll have about 100 items so
I'm not sure if this is too large for
an array or collection. . . .
Click to expand...

Certainly, a hundred-member array isn't likely to overburden Access VBA.
Have you looked in Help and in other references at the Callback Function
approach to supplying values to a List or Combo box. That will allow you to
use your array or collection without using a Value List.

And, it shouldn't be too many entries for a Collection. I suspect the
overhead will be somewhat higher for a Collection than for an Array, though.

Larry Linson
Microsoft Access MVP
 
Back
Top