MS Access Determine the listbox item clicked on click event

Question

I have a ListBox within MS Access and would like to find the best way to get the list item which was selected/deselected at the time of the on-click event.

Its a little more complicated than looping through the selected items, since the listbox is already loaded with some items selected. I am trying to find the single item which was affected at the time of the on-click event.

So, If the user clicks "Col2-How" in the example above, how would I determine that was the record clicked, Alternatively, if one deselects the first record, I would need to know. Any clues?

The only thing I can think of is to use an in-memory object to maintain alist of highlighted rows and track back to the selected items at the time of the click to determine the deltas?

Octoroon

The only thing you can think of is probably pretty close. Another listbox with visible=false, and the same rowsource is probably easier: You can then just use a loop in listbox_click (for the visible one) to determine the difference between the two, and to update the state of the changed item in the invisible one. – Eduardo

Answer 1

you could use AfterUpdate event, for example the name of the listbox is 'aListbox', so try this :

Private Sub aListBox_AfterUpdate()
  Dim rowIndex As Integer
  Dim rowValue As String
  Dim rowIsSelected As Integer
  Dim result As String

  ' ListBox row index clicked
  rowIndex = Me.aListBox.ListIndex

  ' Row value clicked
  rowValue = Me.aListBox.Column(0)

  ' If row is selected return value is -1, if unselected return value 0
  rowIsSelected = Me.aListBox.Selected(rowIndex)

  If (rowIsSelected = -1) Then
    result = rowValue & " selected"
  Else
    result = rowValue & " unselected"
  End If

  MsgBox (result)

End Sub

Entrance

Thanks!, this is really elegant! I don't understand why i couldn't think of that! – Octoroon

Answer 2

Many people asked this question but failed to realise that they are also running other codes in sequence and hence fail to select the item on the listbox and execute the "click" event at times.

A good test is to bring your VBA codes to the end of your codes for a particular routine instead of at the beginning of your listbox, assumming there are other listboxes too or you can simply isolate to just one listbox for testing purposes.

Assumming your listbox is named "Listbox_One" you only need to two lines of codes:

'**************************** Private Sub Form_Load() ' Best to put the two codes below LAST so that it ' would not be affected by earlier codes"

Me.Listbox_One.SetFocus

Me.Listbox_One.ListIndex = 0
'1 if you wish to select the second item etc

End Sub

'*******************************

Ensure your Listbox_One_Click routine have some codes. For example,

Private Sub Listbox_One_Click

'Do something here

Msgbox "The 1st Item value is: " & Me.Listbox_One.Column(1)

'Do something else here too.

End sub

The message box will appear proving that Listbox_One item has been "clicked".

Hope this helps.

Michael Yee

Copperplate