Listbox Order

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

re: Listbox Order

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" <zinck74@hotmail.com> wrote in message news:vojv9sml2cnt8a@corp.supernews.com...[color=blue]
> 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
>
>[/color]

re: Listbox Order

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" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:PYoib.16193$yX1.3486@newssvr31.news.prodigy.c om...[color=blue]
> I think you'll run into another problem as well. Since you are storing the[/color]
changes[color=blue]
> anywhere, the next time you open the form the listboxes will be back the[/color]
way they started.[color=blue]
> A table is probably the better way to go. You should be able to reduce it[/color]
to one table per[color=blue]
> two listboxes.
>
> If you don't mind them resetting each time you open the form, then (in[/color]
code) you would[color=blue]
> have to add the item to the item list, run a sort routine on the list,[/color]
then place the list[color=blue]
> in the listbox.
>
> --
> Wayne Morgan
>
>
> "Bill" <zinck74@hotmail.com> wrote in message[/color]
news:vojv9sml2cnt8a@corp.supernews.com...[color=blue][color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > it "moves" it to the other. I used to approach it with a table and[/color][/color]
within[color=blue][color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > then refreshing the listboxes. This doesn't seem to scale very well as[/color][/color]
I[color=blue][color=green]
> > 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[/color][/color]
add[color=blue][color=green]
> > and subtract items from a list box. Well now I've run into another[/color][/color]
problem.[color=blue][color=green]
> > When I open the form the list is in alphabetical order because I[/color][/color]
initially[color=blue][color=green]
> > populate the box with a SQL statement. This is what I want. But if I[/color][/color]
move[color=blue][color=green]
> > the item to the "selected" listbox and then decide to move it back, it[/color][/color]
puts[color=blue][color=green]
> > it at the end of the listbox order and I'd like it to be back at where[/color][/color]
it[color=blue][color=green]
> > was, in alphabetical order. It seems the add and remove item just adds[/color][/color]
or[color=blue][color=green]
> > removes an item from a value list. So I guess my question is[/color][/color]
(finally!),[color=blue][color=green]
> > how does one reorder this so that it's back in alphabetical order? Is[/color][/color]
there[color=blue][color=green]
> > 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[/color][/color]
are[color=blue][color=green]
> > 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
> >
> >[/color]
>
>[/color]

re: Listbox Order

"Bill" <zinck74@hotmail.com> wrote in message
news:vojv9sml2cnt8a@corp.supernews.com...[color=blue]
> 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[/color]
and[color=blue]
> 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[/color]
and[color=blue]
> 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[/color]
add[color=blue]
> and subtract items from a list box. Well now I've run into another[/color]
problem.[color=blue]
> 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[/color]
move[color=blue]
> the item to the "selected" listbox and then decide to move it back, it[/color]
puts[color=blue]
> 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[/color]
there[color=blue]
> 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[/color]

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

re: Listbox Order

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" <zinck74@hotmail.com> wrote in message news:vok8vo9l5a6g86@corp.supernews.com...[color=blue]
> 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.[/color]

re: Listbox Order

-----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:[color=blue]
> "Bill" <zinck74@hotmail.com> wrote in message
> news:vojv9sml2cnt8a@corp.supernews.com...
>[color=green]
>>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[/color]
>
> and
>[color=green]
>>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[/color]
>
> and
>[color=green]
>>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[/color]
>
> add
>[color=green]
>>and subtract items from a list box. Well now I've run into another[/color]
>
> problem.
>[color=green]
>>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[/color]
>
> move
>[color=green]
>>the item to the "selected" listbox and then decide to move it back, it[/color]
>
> puts
>[color=green]
>>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[/color]
>
> there
>[color=green]
>>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[/color]
>
>
> 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
>
>[/color]

re: Listbox Order

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" <zinck74@hotmail.com> wrote in message
news:vojv9sml2cnt8a@corp.supernews.com...[color=blue]
> 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[/color]
and[color=blue]
> 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[/color]
and[color=blue]
> 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[/color]
add[color=blue]
> and subtract items from a list box. Well now I've run into another[/color]
problem.[color=blue]
> 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[/color]
move[color=blue]
> the item to the "selected" listbox and then decide to move it back, it[/color]
puts[color=blue]
> 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[/color]
there[color=blue]
> 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
>
>[/color]

re: Listbox Order

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:[color=blue]
> Bill,
>
> Isn't it a simple matter of having an "Order By" clause in the SQL query
> that serves as your listbox row source?
>
>[/color]

re: Listbox Order

"BK" wrote
[color=blue]
> . . . I'll have about 100 items so
> I'm not sure if this is too large for
> an array or collection. . . .[/color]

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