Multi-select listbox loses chosen values when form is refreshed

[paparingos7]

Hi all,

I've got a listbox on a form which is enabled for multiple selection (Multi Select = Extended).

Row Source Type: Table/Query
Row Source: query whose SQL I manipulate in VBA (the idea is to only show the relevant values based on the other selections on the form, namely 5 combo boxes)

The problem I have is that when the form is refreshed when VBA is running (Me.Refresh), the listbox appears to lose any previously selected values.

I've bypassed this by storing the selected values of the listbox in an array and then restoring them to the (now empty) listbox after the form is refreshed.

This seems too much of a fiddle - can you think of a way to force the listbox to hold on to its selected values even if the form is refreshed (please assume that the form needs to get refreshed).

Many thanks.

 

[pbaldy]

I don't think so. I have a couple of apps where I let the users re-sort a multi-select listbox while making selections. As you've experienced, any selections get lost when you reset the row source. I use a string variable rather than an array, but same theory: grab selected values, re-sort listbox, re-select values.

 

[boblarson]

1. Nope, your way is about the only way.

2. Why are you using Me.Refresh? What is that supposed to do? Make sure you know the difference between Refresh and Requery. Refresh is very RARELY needed where Requery is much more necessary (it brings in records that have been added by others, and removes deleted records where refresh does not).

 

[paparingos7]

Thanks guys, much appreciated.

2. Why are you using Me.Refresh? What is that supposed to do? Make sure you know the difference between Refresh and Requery. Refresh is very RARELY needed where Requery is much more necessary (it brings in records that have been added by others, and removes deleted records where refresh does not).

Actually I use both: I found that unless I used both Requery and Refresh my combo boxes wouldn't refresh with the proper SQL (which I'm constructing at run-time with the Combo GotFocus event based on all the selections on the form).
The process goes something like:
- on GotFocus construct the SQL string based on the user selections
- set this SQL as the SQL of the query used as the Combo Row Source
- Me.Requery
- Me.Refresh

 

[boblarson]

Refresh is not necessary. If you actually requery the COMBO boxes:

Me.YourComboName.Requery

instead of the form.

 

[paparingos7]

Sounds good Bob, I'll give it a go. Thanks again.

 

[Footballplayer234]

i think refresh is not necessary , it worked ?

 

[paparingos7]

Affirmative. Good one!

 

[Footballplayer234]

yes agreed , worked