Multi Select Listbox selecting multiple entries with a single user click

Question

I have been working on drag and drop functionality that simulates the effect of moving records in one listbox (which we'll call List A for brevity) to another listbox (List B) and back again. Listboxes are configured to simple select to allow users to move as many records as they highlight in a single motion. The listboxes derive their data from access local tables and they are requeried after each transfer to update the listbox.

Here's the problem: After I move data from List A to List B back to List A, etc., There comes a point after a variable number (2 or more) of transactions where selecting a single entry in either list (i.e. making a single click) appears to highlight more than one record at a time. Oftentimes the record that is highlighted is not even a neighboring record to the one being selected. Furthermore, Access appears to occasionally lose the highlight on a selected record when a user makes another selection, or not highlight a users selection when they click on it.

Testing has determined that sometimes these glitches are purely cosmetic, and sometimes they influence the behavior of the transfer process. The end result is that sometimes just the graphic display is wrong, and sometimes entries that the user did not select are transferred. I'm not sure if this is purely an Access glitch or if I could do something in the VB to correct for this problem, but I'm hoping that one of you has an idea that could help me.

I have attached the code behind the sub responsible for moving data from the ListYard to ListContainer. The opposite direction has a sub that mirrors this one. If you need more of the code I can provide it. As this is a prototype, the database itself is not split and there is only test data within it. Thank you for your assistance, I am really unsure of what is causing this problem.

Private Sub objDD_BeforeDrop(ctlSource As ListBox, ctlTarget As Control, strMoveIDs As String)   
    Dim arrMoveIDs() As String
    Dim i As Integer
    Dim dbs As DAO.Database
    Dim qdfYTD As DAO.QueryDef
    Dim qdfCTA As DAO.QueryDef

    Me.txtTargetList1 = ""
    Me.txtTargetList2 = ""
    If TypeOf ctlTarget Is Access.TextBox Then
        ctlTarget.Value = strMoveIDs
    Else
        Me.txtTargetList2 = strMoveIDs
    End If

    Set dbs = CurrentDb
    Set qdfYTD = dbs.QueryDefs("qryYardTempDelete")
    Set qdfCTA = dbs.QueryDefs("qryContainerTempAppend")

    If InStr(strMoveIDs, ",") > 0 Then
        arrMoveIDs = Split(strMoveIDs, ",")

        For i = 0 To UBound(arrMoveIDs)
            qdfYTD.Parameters(0).Value = CSng(arrMoveIDs(i))
            qdfYTD.Execute
            qdfYTD.Close
            qdfCTA.Parameters(0).Value = CSng(arrMoveIDs(i))
            qdfCTA.Execute
            qdfCTA.Close
        Next

    Else
            qdfYTD.Parameters(0).Value = CSng(strMoveIDs)
            qdfYTD.Execute
            qdfYTD.Close
            qdfCTA.Parameters(0).Value = CSng(strMoveIDs)
            qdfCTA.Execute
            qdfCTA.Close
    End If

    'Me!ctlListYard = ""
    'Me!ctlListContainer = ""

    Me!ctlListYard.Requery
    Me!ctlListContainer.Requery

    Me!ctlListYard.SetFocus
    Me!ctlListYard.ListIndex = -1
    Me!ctlListContainer.SetFocus
    Me!ctlListContainer.ListIndex = -1

    Set qdfYTD = Nothing
    Set qdfCTA = Nothing
    Set dbs = Nothing

End Sub

Answer 1

Took some investigation to figure this one out, but I finally deciphered the problem here. The .ItemsSelected property of my listboxes was not being cleared after each transaction. These two commands did not successfully resolve this problem:

Me!lstYard.ListIdex = -1 Me!lstYard = ""

I was able to successfully correct the problem by calling the following sub on my listboxes after each transaction:

Public Sub ClearListbox(lst As Access.ListBox)

    Dim lngx As Long

    With lst
    For lngx = (.ItemsSelected.Count - 1) To 0 Step -1
    .Selected(.ItemsSelected(lngx)) = False
    Next lngx
    End With

End Sub

I hope this helps someone else having issues with listboxes in Access...