By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Listbox Order

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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.

--
Wayne Morgan
"Bill" <zi*****@hotmail.com> wrote in message news:vo************@corp.supernews.com...
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

Nov 12 '05 #2

P: n/a
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
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:PY******************@newssvr31.news.prodigy.c om...
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.

--
Wayne Morgan
"Bill" <zi*****@hotmail.com> wrote in message

news:vo************@corp.supernews.com...
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


Nov 12 '05 #3

P: n/a

"Bill" <zi*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
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


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
Nov 12 '05 #4

P: n/a
Here is a link to some VB sort routines.

http://www.mvps.org/vbnet/code/sort/qscompare.htm
http://www.mvps.org/vbnet/code/callb...rtcallback.htm
http://www.mvps.org/vbnet/code/comctl/lvdemo3.htm

--
Wayne Morgan
"Bill" <zi*****@hotmail.com> wrote in message news:vo************@corp.supernews.com...
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.

Nov 12 '05 #5

P: n/a
-----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/AwUBP4rmTYechKqOuFEgEQL5XACgsL2XrSL9cfjaVad6Wvti0f nkqHAAoNJ6
YVbh1f6Qkt3790mH+dKM3qAh
=sg9x
-----END PGP SIGNATURE-----

Randy Harris wrote:
"Bill" <zi*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
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

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


Nov 12 '05 #6

P: n/a
Bill,

Isn't it a simple matter of having an "Order By" clause in the SQL query
that serves as your listbox row source?
--
Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com

"Bill" <zi*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
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

Nov 12 '05 #7

P: n/a
BK
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.

Peter De Baets wrote:
Bill,

Isn't it a simple matter of having an "Order By" clause in the SQL query
that serves as your listbox row source?


Nov 12 '05 #8

P: n/a
"BK" wrote
. . . I'll have about 100 items so
I'm not sure if this is too large for
an array or collection. . . .


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

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.