Hi!

I have a listbox on a sheet that is populated using an ODBC query to a
SQLserver. The user selects two dates from calendar controls to form the
query. The query is performed, and the listbox listfillrange is set to the
query table resultrange.

Next, the user makes selections from the listbox (MultiSelectMulti,
ListStyleOption),
and clicks a command button. This button goes through the selected items,
and refreshes another ODBC querytable, modifying the where clause based on
the selected item in the listbox.

This all works well, except when the second query returns no rows. In this
case, all of the user's selections in the listbox are cleared. Why? I
stepped through the code - the selections are fine up to the refresh, then
get cleared. If all of the subsequent querytable refreshes return data, the
selections remain intact.

Does anyone have any suggestions as to what I should be looking at in order
to determine what is clearing the listbox selections? Thanks!

Ok, I still don't know why this problem occurs, but a workable workaround is
to move the second querytable to a different sheet. When it returns no rows,
the listbox selections remain intact and I can continue to loop through the
listbox, executing the second qt.refresh based on listbox.selected(n) = true.
The problem was that when the second query cleared the listbox, my loop was
ending prematurely.

"MikeT" wrote:

Hi!

I have a listbox on a sheet that is populated using an ODBC query to a
SQLserver. The user selects two dates from calendar controls to form the
query. The query is performed, and the listbox listfillrange is set to the
query table resultrange.

Next, the user makes selections from the listbox (MultiSelectMulti,
ListStyleOption),
and clicks a command button. This button goes through the selected items,
and refreshes another ODBC querytable, modifying the where clause based on
the selected item in the listbox.

This all works well, except when the second query returns no rows. In this
case, all of the user's selections in the listbox are cleared. Why? I
stepped through the code - the selections are fine up to the refresh, then
get cleared. If all of the subsequent querytable refreshes return data, the
selections remain intact.

Does anyone have any suggestions as to what I should be looking at in order
to determine what is clearing the listbox selections? Thanks!