ListBox Question

[Guest]

Hi Everyone,
I have a form with a list box that contains two columns. The values for the
listbox are populated from a query. What is happening is this, when a user
selects a value in the listbox the rest of the controls get their values set.
The users can also duplicate an entry via a command button. When the commnad
button is pressed the new record is added and I call refresh on the list box
so that the new record will show up in the list. The problem is that I don't
want the users moving away from the current record that they are on and this
is happening when I call the refresh command. How can I get the value of a
listbox based on what is selected, refresh the listbox and set the selected
record to what was selected, based on the value of column two not the index
as trhe index changes when the new record is added.

TIA

 

[Guest]

In the command button code first find the ItemsSelected value for your
listbox using .column(1) (the second column shown), then after your requery,
set the Selected property for the correct line - Help in ItemsSelected and
Selected properties will help show you how to iterate thru the listbox to
find the selected value and then re-select it again afterwards.

TonyT..

 

[Van T. Dinh]

All you have to do is to store the Value of the ListBox (my guess is that
the ListBox is Single-Select, not a Multi-Select ListBox) in a VBA Variable,
do your new Record, requery the ListBox and then set the ListBox Value to
the saved Value in the
VBA Variable.

Provided that the BoundColumn (which gives the ListBox Value) uniquely
identifies the row in the List, you should get the same row selected.

If it is possible that the currently selected row will not be included after
requery, you need to think (and code) on what to do in this case.

The code should be something like (assuming the BoundColumn is numeric):

' ***Untested***
Dim lngLBValue As Long

lngLBValue = Me.lstMyLB
' Add new record
' Requery ListBox
Me.lstMyLB = lngLBValue