Access VBA restore listbox selection after requery based on a non-bound column

Question

So, I inherited MS Access 2010 (and 2013 now) code that has a listbox bound to a column that is not unique. However, that bound value is used in code, so, I cannot change which column is bound. But, I have a subform that needs to requery the listbox on the parent form. What I am struggling with is how to restore the selection in the parent's listbox to what item was selected before the listbox was requeried.

I have tried storing the Parent.List0 value before the requery and then using Parent.List0.Selected and Parent.List0.ItemData, but these do not seem to work because, presumably, the listbox is bound to a column that is not unique.

There is a column in the listbox that is unique. The 4th column (column(3)) is unique, but I'm not finding a way to select a listbox item based on a column other than the bound column.

I am confident someone has some ideas that will sort this out, but I've not been able to locate any solutions in my search of the web.

DHW DHW · Accepted Answer · 2014-08-25T02:51:34

I am in version 2010 and while testing, I was unable to replicate a situation where the list box loses its value. Neither on a bound form or an unbound form.

In any case however, it seems that column 3 has a unique key - so you can use that.

' Assuming this code runs from within the sub-form
KeyID = Me.Parent.Controls("MyListBox").Column(3)
With Me.Parent.Controls("MyListBox")
  .Requery ' Or do whatever you do that causes it to requery

  ' Find the previously selected item and select
  For i = 0 To (.ListCount - 1)
    If .Column(3, i) = KeyID Then
      .Selected(i) = True
      Exit For
    End If
  Next
End With

That should do the trick.

Access VBA restore listbox selection after requery based on a non-bound column

1 Answers