list box bevavior after requery

[Andy Tag]

Hi all, :banghead:
I have a list box with hundreds of line items sorted by due date. The list box has a dbl click command that opens an update form. Here the user makes changes and hits a command button that send the data to SQL (like a new due date...), it requery the list box, it closes the update form and returns the user to the list box. Hence the problem it follows the record just updated. Therefore it could be way down the list. Then the user has to scroll back up until they locate where they left off. Any help would be much appreciated.

 

[gemma-the-husky]

Use code to set mylistbox =null, in the update code block

 

[Andy Tag]

What code?

 

[gemma-the-husky]

whatever event you are using. In the dbl-click event, probably.

sounds like you need to react to whatever happens after the double click, to refresh the list box as appropriate.

 

[arnelgp]

on the double click event of your listbox, save the value of the listbox before anything else in a variable:

Dim varListValue As Variant
varListValue = yourListBoxName.Value

now after the code that requery of your listbox, assign the value of your variable to the listbox:

yourlistbox.value = varListValue

 

[Andy Tag]

Thanks for your help
mylistbox =null not sure what to do with this?

arnelgp your snippet of code works great but it actually behaves the same way with or without it. Hence the problem, I do not want it to follow the last updated record. I want to return to the list box where I left off.
In other words, if I have 200 line items in my list box and if I double click on the 25th line item from the top, update the due date to 6 months out and hit update this will return me to the list box at that new location, most likely way down the list. Leaving the user scrolling back up to find the 26th line item of the list box. I want to return to the 26th line item.

 

[gemma-the-husky]

I thought you wanted to start at the top again.
arne's thought you wanted to return to the same item.

note that you can't start on the 26th item as such. you start on a particular item with a given reference. if you delete that item, it won't know where to go, and will (probably) start at the top again (as you are finding, it just repositions itself to the same item wherever it now is.

maybe you could store the value of next item down, and reposition yourself to that after the process completes. not sure how to use the itemdata to do that, offhand