selecting in listbox with vba

  • Thread starter Thread starter andreas
  • Start date Start date
A

andreas

hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas
 
Hi Andreas,

I'm not sure why this would crash but it is definately not efficient. Use
queries to update your records - if you want to select everything in a
listbox, then use the rowsource query of the listbox as your starting point
for the query and leave the listbox out of it. Requery the listbox when your
operation is complete. Without knowing what you are doing with these 20000
rows it's hard to be more specific.
 
hello sandra,

well the selected entries in the listbox should be
something like a pre-selection for the user... so i have
to show all data from a table in a listbox, but only
select some of them. but the user should be able to
select other entries, which are not preselected too.

so i guess it is not done, to change the rowsource und
make a requery for it. so i do not change the table
entries when i select/highlight the entries in the
listbox.
but as a matter of fact if i select 4000 out of 20000
entries this lasts very long.

so any help, how to make this faster would be great.

sincerely
andreas
-----Original Message-----
Hi Andreas,

I'm not sure why this would crash but it is definately not efficient. Use
queries to update your records - if you want to select everything in a
listbox, then use the rowsource query of the listbox as your starting point
for the query and leave the listbox out of it. Requery the listbox when your
operation is complete. Without knowing what you are doing with these 20000
rows it's hard to be more specific.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas
Click to expand...

.
Click to expand...
 
Hi Andreas,

20000 records is really too many to show in a list/combo box because it's
really not practical for a user to scroll through that many records at one
time. Instead it is better to provide one or more controls that are used to
filter the list so the user can quickly get to the items he/she is
interested in.

For example, I have a listbox for selecting Sales Orders to include in a
report. There are thousands of sales orders but typically the user is only
interested in those for a particular date range and/or for a particular
customer. The user enters values into one or more of the filtering controls
and the list is requeried to bring back only rows matching the requested
information.

What are you doing with the selected records - it the selection process or
what you are doing with selected records that is taking a long time?

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hello sandra,

well the selected entries in the listbox should be
something like a pre-selection for the user... so i have
to show all data from a table in a listbox, but only
select some of them. but the user should be able to
select other entries, which are not preselected too.

so i guess it is not done, to change the rowsource und
make a requery for it. so i do not change the table
entries when i select/highlight the entries in the
listbox.
but as a matter of fact if i select 4000 out of 20000
entries this lasts very long.

so any help, how to make this faster would be great.

sincerely
andreas
-----Original Message-----
Hi Andreas,

I'm not sure why this would crash but it is definately not
efficient. Use queries to update your records - if you want to
select everything in a listbox, then use the rowsource query of the
listbox as your starting point for the query and leave the listbox
out of it. Requery the listbox when your operation is complete.
Without knowing what you are doing with these 20000 rows it's hard
to be more specific.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas
Click to expand...

.
Click to expand...
Click to expand...
 
hi sandra,

well it is only the selection of these records. after
they are selected and i go through all selected items and
do the things i have to do, things are fast enough.

so this is what i do not understand. only to set the flag
that the item is selected needs so much time. not the
passing through all entries. so i guess, that it is only
the visualization of the listbox what all the time needs.


andreas
-----Original Message-----
Hi Andreas,

20000 records is really too many to show in a list/combo box because it's
really not practical for a user to scroll through that many records at one
time. Instead it is better to provide one or more controls that are used to
filter the list so the user can quickly get to the items he/she is
interested in.

For example, I have a listbox for selecting Sales Orders to include in a
report. There are thousands of sales orders but typically the user is only
interested in those for a particular date range and/or for a particular
customer. The user enters values into one or more of the filtering controls
and the list is requeried to bring back only rows matching the requested
information.

What are you doing with the selected records - it the selection process or
what you are doing with selected records that is taking a long time?

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hello sandra,

well the selected entries in the listbox should be
something like a pre-selection for the user... so i have
to show all data from a table in a listbox, but only
select some of them. but the user should be able to
select other entries, which are not preselected too.

so i guess it is not done, to change the rowsource und
make a requery for it. so i do not change the table
entries when i select/highlight the entries in the
listbox.
but as a matter of fact if i select 4000 out of 20000
entries this lasts very long.

so any help, how to make this faster would be great.

sincerely
andreas
-----Original Message-----
Hi Andreas,

I'm not sure why this would crash but it is definately not
efficient. Use queries to update your records - if you want to
select everything in a listbox, then use the rowsource query of the
listbox as your starting point for the query and leave the listbox
out of it. Requery the listbox when your operation is complete.
Without knowing what you are doing with these 20000 rows it's hard
to be more specific.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

andreas wrote:
hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas

.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi Andreas,

Are you selecting them programmatically? That's the part I don't
understand - if your program knows what to select, why not skip the
selection part and go directly to the action? IOW, the listbox should only
be used as a means for getting input from the user. If you don't need the
user to make selections, then skip the listbox entirely.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hi sandra,

well it is only the selection of these records. after
they are selected and i go through all selected items and
do the things i have to do, things are fast enough.

so this is what i do not understand. only to set the flag
that the item is selected needs so much time. not the
passing through all entries. so i guess, that it is only
the visualization of the listbox what all the time needs.


andreas
-----Original Message-----
Hi Andreas,

20000 records is really too many to show in a list/combo box because
it's really not practical for a user to scroll through that many
records at one time. Instead it is better to provide one or more
controls that are used to filter the list so the user can quickly
get to the items he/she is interested in.

For example, I have a listbox for selecting Sales Orders to include
in a report. There are thousands of sales orders but typically the
user is only interested in those for a particular date range and/or
for a particular customer. The user enters values into one or more
of the filtering controls and the list is requeried to bring back
only rows matching the requested information.

What are you doing with the selected records - it the selection
process or what you are doing with selected records that is taking a
long time?

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hello sandra,

well the selected entries in the listbox should be
something like a pre-selection for the user... so i have
to show all data from a table in a listbox, but only
select some of them. but the user should be able to
select other entries, which are not preselected too.

so i guess it is not done, to change the rowsource und
make a requery for it. so i do not change the table
entries when i select/highlight the entries in the
listbox.
but as a matter of fact if i select 4000 out of 20000
entries this lasts very long.

so any help, how to make this faster would be great.

sincerely
andreas
-----Original Message-----
Hi Andreas,

I'm not sure why this would crash but it is definately not
efficient. Use queries to update your records - if you want to
select everything in a listbox, then use the rowsource query of the
listbox as your starting point for the query and leave the listbox
out of it. Requery the listbox when your operation is complete.
Without knowing what you are doing with these 20000 rows it's hard
to be more specific.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

andreas wrote:
hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas

.
Click to expand...

.
Click to expand...
Click to expand...
 
hi sandra,

well this is only a preselection or the status of the
selection when the application was last closed. the
customer then should be able to select other item or
deselect selected items he wants.
so this selection part has to be done programmatically.
and after some user input other actions with the selected
items have to be done. but this is doing pretty well.
only the selection part is causing me some headaches :-)

andreas
-----Original Message-----
Hi Andreas,

Are you selecting them programmatically? That's the part I don't
understand - if your program knows what to select, why not skip the
selection part and go directly to the action? IOW, the listbox should only
be used as a means for getting input from the user. If you don't need the
user to make selections, then skip the listbox entirely.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hi sandra,

well it is only the selection of these records. after
they are selected and i go through all selected items and
do the things i have to do, things are fast enough.

so this is what i do not understand. only to set the flag
that the item is selected needs so much time. not the
passing through all entries. so i guess, that it is only
the visualization of the listbox what all the time needs.


andreas
-----Original Message-----
Hi Andreas,

20000 records is really too many to show in a list/combo box because
it's really not practical for a user to scroll through that many
records at one time. Instead it is better to provide one or more
controls that are used to filter the list so the user can quickly
get to the items he/she is interested in.

For example, I have a listbox for selecting Sales Orders to include
in a report. There are thousands of sales orders but typically the
user is only interested in those for a particular date range and/or
for a particular customer. The user enters values into one or more
of the filtering controls and the list is requeried to bring back
only rows matching the requested information.

What are you doing with the selected records - it the selection
process or what you are doing with selected records that is taking a
long time?

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


andreas wrote:
hello sandra,

well the selected entries in the listbox should be
something like a pre-selection for the user... so i have
to show all data from a table in a listbox, but only
select some of them. but the user should be able to
select other entries, which are not preselected too.

so i guess it is not done, to change the rowsource und
make a requery for it. so i do not change the table
entries when i select/highlight the entries in the
listbox.
but as a matter of fact if i select 4000 out of 20000
entries this lasts very long.

so any help, how to make this faster would be great.

sincerely
andreas
-----Original Message-----
Hi Andreas,

I'm not sure why this would crash but it is definately not
efficient. Use queries to update your records - if you want to
select everything in a listbox, then use the rowsource query of the
listbox as your starting point for the query and leave the listbox
out of it. Requery the listbox when your operation is complete.
Without knowing what you are doing with these 20000 rows it's hard
to be more specific.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

andreas wrote:
hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas

.

.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi Andreas,

Ok - how about using a pair of listboxes - one for Available items and one
for Selected items. If you are trying to save selections across sessions you
are probably storing either a boolean value on a record or as a new record
in another table. Take a look at my Select Records sample at
http://www.daiglenet.com/MSAccess.htm. Since the listboxes are based on SQL
queries they populate pretty quickly. Perhaps this will give you some new
ideas on how to procede.

I really don't know any other way to speed up programatic selections in a
listbox.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hi sandra,

well this is only a preselection or the status of the
selection when the application was last closed. the
customer then should be able to select other item or
deselect selected items he wants.
so this selection part has to be done programmatically.
and after some user input other actions with the selected
items have to be done. but this is doing pretty well.
only the selection part is causing me some headaches :-)

andreas
-----Original Message-----
Hi Andreas,

Are you selecting them programmatically? That's the part I don't
understand - if your program knows what to select, why not skip the
selection part and go directly to the action? IOW, the listbox
should only be used as a means for getting input from the user. If
you don't need the user to make selections, then skip the listbox
entirely.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

hi sandra,

well it is only the selection of these records. after
they are selected and i go through all selected items and
do the things i have to do, things are fast enough.

so this is what i do not understand. only to set the flag
that the item is selected needs so much time. not the
passing through all entries. so i guess, that it is only
the visualization of the listbox what all the time needs.


andreas

-----Original Message-----
Hi Andreas,

20000 records is really too many to show in a list/combo box
because it's really not practical for a user to scroll through
that many records at one time. Instead it is better to provide one
or more controls that are used to filter the list so the user can
quickly get to the items he/she is interested in.

For example, I have a listbox for selecting Sales Orders to include
in a report. There are thousands of sales orders but typically the
user is only interested in those for a particular date range and/or
for a particular customer. The user enters values into one or more
of the filtering controls and the list is requeried to bring back
only rows matching the requested information.

What are you doing with the selected records - it the selection
process or what you are doing with selected records that is taking
a long time?

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


andreas wrote:
hello sandra,

well the selected entries in the listbox should be
something like a pre-selection for the user... so i have
to show all data from a table in a listbox, but only
select some of them. but the user should be able to
select other entries, which are not preselected too.

so i guess it is not done, to change the rowsource und
make a requery for it. so i do not change the table
entries when i select/highlight the entries in the
listbox.
but as a matter of fact if i select 4000 out of 20000
entries this lasts very long.

so any help, how to make this faster would be great.

sincerely
andreas
-----Original Message-----
Hi Andreas,

I'm not sure why this would crash but it is definately not
efficient. Use queries to update your records - if you want to
select everything in a listbox, then use the rowsource query of
the listbox as your starting point for the query and leave the
listbox out of it. Requery the listbox when your operation is
complete. Without knowing what you are doing with these 20000
rows it's hard to be more specific.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

andreas wrote:
hello,

i want to select a lot of rows (about 20000) in a
listbox. when i go through all entries uf this list box
and select them, then this will last very long or the
application crashs down.
here ist the example:

For i = 1 To Ausgabeliste.ListCount
Ausgabeliste.Selected(i) = True
Next i

if i only go through all entries in the listbox and do
nothing then everything is ok....so i guess, that the
updating of the screen could be a problem....

does anybody know how to select a lot of entries without
this problem or which settings should be set??

thanks a lot and best regards
andreas

.

.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top