Solved Listbox AfterUpdate and click when there is no selection

[Luis Fernandez]

I have a problem that, although I haven't thought about it much, I don't think I see an easy solution.
In a multiselect lisbox when I click with the mouse and select an items/row, afterupdate and click are activated, but when I use the down arrow and I only position myself (not selecting) the 2 events are also activated, and although it is correct because I am in another row and afterupdate was activated because there was an update of the listbox, there was no selection.
Additionally, try in the beforeupdate event to see if the items were selected or not to compare it later with the new value of selected, but in the beforeupdate the new value is already present (selected), making it impossible to compare it to see if it changed.
In summary: there is no afterupdate event for a selected items, but one for the listbox and it is updated with anything (change of focus, positioning in another row, selecting it or not
Is there a way to only do things when a row is selected (changes color) and not when I scroll through the list?

 

[June7]

I don't understand issue. Even by cursor movement on list, the OldValue is still what is in field. It isn't changed until record edit is committed to table.

Not sure what you mean by "no afterupdate event for a selected items" - AfterUpdate is triggered for click or cursor movement.

 

[Luis Fernandez]

I had forgotten the OldValue can help me, I thought so
What I'm saying is that the afterUpdate event is triggered by moving from one row to another although there is no update of selected, no row has changed to selected/deselected.
I have to discard the movements or positioning in the rows since I am only interested in the selected/deselected changes
And thanks for responding

 

[Luis Fernandez]

Oldvalue did not work, since the items do not have oldvalue, only the control and the listboxes always return null to value and oldvalue.
I think I will have to do everything manually, that is, I will memorize each selection value of each item and then compare them to see if they have changed and so I know which items were selected or deselected live to be able to do the job I need

 

[CJ_London]

Multi select listboxes do not have an oldvalue. By definition of what they are they need to be unbound. You need to refer to the listbox selecteditems property

 

[Luis Fernandez]

The problem is simple, the multiselect listbox is not used to select anything, because it has no event before selecting or after selecting, therefore it is not useful to be used and know live if something was selected or not.

It's funny that a control that is specifically for selecting lists does not have an event for that, but rather has an event that is activated for selecting and moving.
By the way, the click event is simply activated after BeforeUpdate -->AfterUpdate -->Click unless the beforeupdate event has been canceled. And it does not identify any mouse click since it is also activated with a displacement, that is, positioning itself in another items/row

 

[Luis Fernandez]

I think I have a somewhat improved solution.
If I use the set Listbox1.ItemsSelected.Count and memorize it, I can detect if an item was selected/deselected, since this counter will tell me one more or one less than previously, and if it has not changed then I do not take it into account because it would be a displacement or movement.
I only memorize one piece of information (total of selected ones) and not the entire selection, in any case, I always know where I am (position) in the listbox, to know which one was selected/deselected.
Of course I have which ones are selected and also the array of only the selected ones.
Unless someone knows a better way, I'll use this one.

 

[Luis Fernandez]

Salomonic solution, lol
It works perfect:

Declarations
Dim Totalselect As Integer

- Load Event
    Totalselect=ListBox1.ItemsSelected.Count

-EventBeforeUpdate
   If Totalselect=ListBox1.ItemsSelected.Count Then Cancel=1
    'This cancels the AfterUpdate and click events (unless you need them for something)

-EventAfterUpdate
    ' Now it only enters when there is an effective selection or deselection of an items/Row
    Totalselect=ListBox1.ItemsSelected.Count    'I update the TotalSelected
    'do everything else that has to do with selecting an item and there will be no false select when moving position

 

[Pat Hartman]

It's funny that a control that is specifically for selecting lists does not have an event for that, but rather has an event that is activated for selecting and moving.

The events are not concerned with the RowSource (which is the list). They are concerned with the bound field which is the ControlSource. Multi-select listboxes are not bound.

I can't tell what you are trying to do so I can't offer a solution. Perhaps instead of telling us what doesn't work, you could start with telling us your objective in non-technical language. (for example, I want to select one or more items from a multi-select listbox and then use the selected items to print a report for each selection). I can't help but feel that there is an obvious solution but I don't know what you are trying to do.

Scrolling using the scroll bar doesn't fire the Before/After update events.

 

[Luis Fernandez]

Okay
I want to execute a code when selecting/deselecting in a multiselect listbox
This code will do things live, since I need to know in what order the items were selected, since selecting 4 and 7 will not be the same as selecting 7 and 4. and of course it cannot execute this code if I only move or move through the list with the arrows, you only have to execute this code if I select or deselect an item.
That's all

 

[Pat Hartman]

Use the click event of the control. Ignore the Before/After update events.

I've attached a modified version of my "bad data is bad for business" sample. I added a listbox to a form . I only added logging to three events for the control. You might want to add more event logging (don't use the MouseMove, it will drive you nuts). Open the 5th item from the switcoboard (Form Validation and event Logging). This is the form where all the events are displayed and so must be open or you will get errors from the logging code. Then open frmDefaultEvents_COPY_ME_1. That is the form I added the listbox control to. You can use the two big buttons to clear the log table or to add a purple line to separate tests. If you need more explanation, watch the videos I posted.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[Luis Fernandez]

Use the click event of the control. Ignore the Before/After update events.

It is activated in the same way, both with arrow movement and with the mouse
BeforeUpdate -->AfterUpdate -->Click

 

[Pat Hartman]

Or don't even bother to try the example I made for you which shows what events are firing for the control. If the Before/After update events of the control are running, it is because you clicked on an item, NOT because you scrolled the listbox.

 

[Luis Fernandez]

Reviewing

 

[Luis Fernandez]

Very nice example, thank you very much for your dedication. impressive, and can be very useful, thank you.
However
As I explained before
Whether I use mouse click or down arrow, the system uses
BeforeUpdate -->AfterUpdate -->Click
Therefore I still have the problem (although it has already been solved manually)

 

[June7]

Ooops, I missed the "multiselect" qualification in original post.

 

[Edgar_]

Okay
I want to execute a code when selecting/deselecting in a multiselect listbox
This code will do things live, since I need to know in what order the items were selected, since selecting 4 and 7 will not be the same as selecting 7 and 4. and of course it cannot execute this code if I only move or move through the list with the arrows, you only have to execute this code if I select or deselect an item.
That's all

Based on this, a multiselect listbox is not the best idea, you could use a transfer box design, where you basically select something in listbox 1 and it appears in listbox 2. That way your users will know the order in which things will be executed. There is no built-in way to know in what order things were selected, so, unless you memorize the order or you add a column where the order appears, then it will become tricky.

I was able to figure out how to do this by using a combination of dictionaries and strings and just the AfterUpdate event of the listbox.

Option Compare Database
Option Explicit

Private item As Variant, dict As Object

Private Sub Form_Load()
    Set dict = CreateObject("scripting.dictionary")
End Sub

Private Sub lbx_choice_id_AfterUpdate()
    Dim prevList As String, currentList As String
   
    ' add to existing dictionary
    For Each item In Me.lbx_choice_id.ItemsSelected
        If Not dict.Exists(item) Then
            dict.Add item, Me.lbx_choice_id.Column(1, item)
        End If
        prevList = prevList & "," & item
    Next item

    ' remove from dictionary if no longer selected
    For Each item In dict
        If InStr(prevList, item) = 0 Then
            dict.Remove item
        End If
    Next item
   
    ' build string
    For Each item In dict
        currentList = currentList & "," & dict(item)
    Next item
   
    'cleanup and print list
    Msgbox Mid(currentList, 2)
End Sub

 

[Pat Hartman]

As I explained before
Whether I use mouse click or down arrow, the system uses
BeforeUpdate -->AfterUpdate -->Click

Look again at the events. The ONLY reason the Before and After update events fired was because you clicked on the control. Scrolling does NOT fire any of those three events.

The form I started with logs all form level events except the MouseMove (way too much noise) Then I added the listbox control but I only added logging to the three events you talked about - Before and After update and click for that control. To see what other events might be firing, add the logging code to every of the listbox event EXCEPT the MouseMove. The MouseMove is just annoying and obfuscates everything else due to the volume of log records it generates.

I agree with Edgar_, it would be far less confusing to use a temp table or the dictionary object if sequence is important to you.

 

[Luis Fernandez]

Based on this, a multiselect listbox is not the best idea, you could use a transfer box design, where you basically select something in listbox 1 and it appears in listbox 2. That way your users will know the order in which things will be executed. There is no built-in way to know in what order things were selected, so, unless you memorize the order or you add a column where the order appears, then it will become tricky.

Thanks, I understand, but the user sees the order in a live form/report

 

[Luis Fernandez]

Look again at the events. The ONLY reason the Before and After update events fired was because you clicked on the control. Scrolling does NOT fire any of those three events.

The form I started with logs all form level events except the MouseMove (way too much noise) Then I added the listbox control but I only added logging to the three events you talked about - Before and After update and click for that control. To see what other events might be firing, add the logging code to every of the listbox event EXCEPT the MouseMove. The MouseMove is just annoying and obfuscates everything else due to the volume of log records it generates.

I agree with Edgar_, it would be far less confusing to use a temp table or the dictionary object if sequence is important to you.

Thank you, I may be wrong, but in the video I click initially and then down arrow several times and these are the events that appear