Clear Multiselect listbox

[alex]

Using Access ‘03

I have a multiselect listbox that transfers values from one listbox to
another (a paired listbox situation left-to-right).

I just finished the code to transfer selected values from one listbox
to another and noticed that I need to clear the values from the source
listbox; i.e., others are now highlighted.

I’ve spent the last few hours using recommended snippets of code to do
just that, but none are consistently working.

Every piece of code that I’ve used works a few times, but when I
select many non-contiguous values (or even many values), I’m left with
black lines where the value used to be. Sometimes it’s one line,
other times it’s blocks of black lines. These black lines are more
than an aesthetic pain; I’m actually getting null values transferred
to my destination table!

Here’s an example of some of the code that I’ve tried; I’ve used
others:

Dim varitm As Variant
'
With Me.lbSource
For Each varitm In .ItemsSelected
.Selected(varitm) = False
Next varitm
End With

‘Or
Dim intI As Integer
With Me.lstMyListBox
For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI
End With
Has anyone experienced this problem?
alex

 

[alex]

Using Access ‘03

I have a multiselect listbox that transfers values from one listbox to
another (a paired listbox situation left-to-right).

I just finished the code to transfer selected values from one listbox
to another and noticed that I need to clear the values from the source
listbox; i.e., others are now highlighted.

I’ve spent the last few hours using recommended snippets of code to do
just that, but none are consistently working.

Every piece of code that I’ve used works a few times, but when I
select many non-contiguous values (or even many values), I’m left with
black lines where the value used to be.  Sometimes it’s one line,
other times it’s blocks of black lines.  These black lines are more
than an aesthetic pain; I’m actually getting null values transferred
to my destination table!

Here’s an example of some of the code that I’ve tried; I’ve used
others:

Dim varitm As Variant
'
With Me.lbSource
    For Each varitm In .ItemsSelected
        .Selected(varitm) = False
    Next varitm
End With

‘Or
Dim intI As Integer
    With Me.lstMyListBox
        For intI = (.ItemsSelected.Count - 1) To 0 Step -1
            .Selected(.ItemsSelected(intI)) = False
        Next intI
    End With
Has anyone experienced this problem?
alex

I’m guessing the problem stems from the Row Source of the listbox,
which is a query.

The query is somehow getting confused after each requery.

alex

 

[Dirk Goldgar]

I use code identical to your second example below, and it has always worked
fine for me. I can't reproduce the problem you report. Unless there's
something wrong with your practical implementation of that code, I suspect
that there may be a problem with your video driver. What happens if you run
the code to clear the selections (and see the black lines you report), then
minimize the form, then restore it? Are the black lines still there?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


in message
Using Access ‘03

I have a multiselect listbox that transfers values from one listbox to
another (a paired listbox situation left-to-right).

I just finished the code to transfer selected values from one listbox
to another and noticed that I need to clear the values from the source
listbox; i.e., others are now highlighted.

I’ve spent the last few hours using recommended snippets of code to do
just that, but none are consistently working.

Every piece of code that I’ve used works a few times, but when I
select many non-contiguous values (or even many values), I’m left with
black lines where the value used to be. Sometimes it’s one line,
other times it’s blocks of black lines. These black lines are more
than an aesthetic pain; I’m actually getting null values transferred
to my destination table!

Here’s an example of some of the code that I’ve tried; I’ve used
others:

Dim varitm As Variant
'
With Me.lbSource
For Each varitm In .ItemsSelected
.Selected(varitm) = False
Next varitm
End With

‘Or
Dim intI As Integer
With Me.lstMyListBox
For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI
End With
Has anyone experienced this problem?

 

[Dirk Goldgar]

I’m guessing the problem stems from the Row Source of the listbox, which
is a query.

The query is somehow getting confused after each requery.

When is this requery taking place? There's nothing in having a query for
the rowsource that is inherently a problem.. Maybe you should post your
real code.

 

[Dirk Goldgar]

I’m guessing the problem stems from the Row Source of the listbox, which
is a query.

The query is somehow getting confused after each requery.

Incidentally, if you're requerying anyway, you can both requery the combo
box and clear its selections in one go by simply reassigning its rowsource:

With Me.lstMyListBox
.RowSource = .RowSource
End With

 

[alex]

Incidentally, if you're requerying anyway, you can both requery the combo
box and clear its selections in one go by simply reassigning its rowsource:

    With Me.lstMyListBox
        .RowSource = .RowSource
    End With

Dirk, thanks for helping. I'm going to review everything you just
said and get right back to you!
alex

 

[alex]

Dirk, thanks for helping.  I'm going to review everything you just
said and get right back to you!
alex- Hide quoted text -

- Show quoted text -

Dirk,

The black lines are still there when I minimize the form (which is a
pop up) and then maximize it. In addition, not only do I have “blank”
black lines/blocks, but sometimes an actual value or values are
selected, meaning the code is not working at all.

As I mentioned before: I’ll select one value (from lbSource) and hit
the command button to transfer the value to lbDestination and
everything works fine.

I’ll select several values and hit the same command button and
everything works fine.

Trying to act like an actual user: I’ll select many random values all
over lbSource and hit the command button. All values move to
lbDestination, but lbSource now has those black lines, usually at the
bottom of lbSource, but not always.

Here’s the code from Allen Browne:

Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
'Purpose: Unselect all items in the listbox.
'Return: True if successful
'Author: Allen Browne. http://allenbrowne.com June, 2006.
Dim varItem As Variant

If lst.MultiSelect = 0 Then
lst = Null
Else
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next
End If

ClearList = True

Exit_ClearList:
Exit Function

Err_ClearList:
MsgBox "Problem with ClearList function!"
Resume Exit_ClearList
End Function

I then call it from the command button that transfers the value from
lbSource to lbDestination:

Private Sub btnSelect_Click()

'call the SelectItem sub
SelectItem

Call ClearList(Me.lbSource)

End Sub

 

[Dirk Goldgar]

==== QUOTED MESSAGE ====

The black lines are still there when I minimize the form (which is a
pop up) and then maximize it. In addition, not only do I have “blank”
black lines/blocks, but sometimes an actual value or values are
selected, meaning the code is not working at all.

As I mentioned before: I’ll select one value (from lbSource) and hit
the command button to transfer the value to lbDestination and
everything works fine.

I’ll select several values and hit the same command button and
everything works fine.

Trying to act like an actual user: I’ll select many random values all
over lbSource and hit the command button. All values move to
lbDestination, but lbSource now has those black lines, usually at the
bottom of lbSource, but not always.

Here’s the code from Allen Browne:

Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
'Purpose: Unselect all items in the listbox.
'Return: True if successful
'Author: Allen Browne. http://allenbrowne.com June, 2006.
Dim varItem As Variant

If lst.MultiSelect = 0 Then
lst = Null
Else
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next
End If

ClearList = True

Exit_ClearList:
Exit Function

Err_ClearList:
MsgBox "Problem with ClearList function!"
Resume Exit_ClearList
End Function

I then call it from the command button that transfers the value from
lbSource to lbDestination:

Private Sub btnSelect_Click()

'call the SelectItem sub
SelectItem

Call ClearList(Me.lbSource)

End Sub

==== END QUOTED MESSAGE ====

Alex -

What's going on in SelectItem? Could you post that code, too? I believe
Allen's code should work, unless the list box is already screwed up somehow.

 

[alex]

Dirk,

The black lines are still there when I minimize the form (which is a
pop up) and then maximize it.  In addition, not only do I have “blank”
black lines/blocks, but sometimes an actual value or values are
selected, meaning the code is not working at all.

As I mentioned before:  I’ll select one value (from lbSource) and hit
the command button to transfer the value to lbDestination and
everything works fine.

I’ll select several values and hit the same command button and
everything works fine.

Trying to act like an actual user:  I’ll select many random values all
over lbSource and hit the command button.  All values move to
lbDestination, but lbSource now has those black lines, usually at the
bottom of lbSource, but not always.

Here’s the code from Allen Browne:

Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
    'Purpose:   Unselect all items in the listbox.
    'Return:    True if successful
    'Author:    Allen Browne.http://allenbrowne.com June, 2006.
    Dim varItem As Variant

    If lst.MultiSelect = 0 Then
        lst = Null
    Else
        For Each varItem In lst.ItemsSelected
            lst.Selected(varItem) = False
        Next
    End If

    ClearList = True

Exit_ClearList:
    Exit Function

Err_ClearList:
    MsgBox "Problem with ClearList function!"
    Resume Exit_ClearList
End Function

I then call it from the command button that transfers the value from
lbSource to lbDestination:

Private Sub btnSelect_Click()

'call the SelectItem sub
SelectItem

Call ClearList(Me.lbSource)

End Sub- Hide quoted text -

- Show quoted text -

Dirk,
I've been messing around with the row source of my listbox...
My listbox rowsource is a query that has an unmatched component!
I cannot reproduce the error (the black lines) when I remove the
unmatched component; i.e., it works perfectly!
Would you like to see the code of the query?

 

[Dirk Goldgar]

I've been messing around with the row source of my listbox...
My listbox rowsource is a query that has an unmatched component! I cannot
reproduce the error (the black lines) when I remove the unmatched
component; i.e., it works perfectly! Would you like to see the code of
the query?

I'm not sure what you mean by "an unmatched component". Yes, I'd like to
see the query (in addition to the code in SelectItem).

 

[alex]

I'm not sure what you mean by "an unmatched component".  Yes, I'd like to
see the query (in addition to the code in SelectItem).

When I say “unmatched component,” I mean the query is technically an
unmatched query. When the user selects a value from lbSource, that
value is then shown in lbDestination. Since it’s now shown in
lbDestination, I need to trick lbSource into not showing that value.
Hope that makes sense.

Here’s the original code behind the row source of lbSource (the bound
column is the key):
SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN tblDestination ON tblSource.CatorKey =
tblDestination.CatorKey
WHERE (((tblDestination.CatorKey) Is Null));

When I remove the join I cannot reproduce the error I’ve been
describing!

Here’s the SelectItem code:
Private Sub SelectItem()

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String
Dim itm As Variant

If Me.lbSource.ItemsSelected.Count = 0 Then
Beep
Exit Sub
End If

Set rsSource = CurrentDb.OpenRecordset("tblSource", _
dbOpenDynaset)
Set rsDestination = _
CurrentDb.OpenRecordset("tblDestination", _
dbOpenDynaset)

For Each itm In Me.lbSource.ItemsSelected
theBug = Me.lbSource.ItemData(0)

rsDestination.AddNew
rsDestination!CatorKey = Me.lbSource.ItemData(itm)
rsDestination!CaseName = Me.CaseName 'textbox on form
rsDestination.Update

rsSource.FindFirst "CatorKey = '" & _
Me.lbSource.ItemData(itm) & "'"
'rsSource.Edit
'rsSource.Delete 'this will delete the value from the source table

Next itm

Me.lbSource.Requery
Me.lbDestination.Requery

End Sub

 

[Dirk Goldgar]

Alex -

I see several things in that code to question that aren't related to the
specific issue. I believe that I understand what's going on, though, so
let's address that specifically.

Tests that I've made suggest that when you have a multiselect list box with
various items selected in it, and you then requery that list box, Access
doesn't fully clear the selections. Or at least, it sets
ItemsSelected.Count to 0, but it doesn't visibly clear the selected "look"
of various rows of the list box.

I suggest that, rather than using Allen Browne's ClearList function in this
case, you do either of two things:

** Alternative 1 **
Reset the .Selected flag of each item as you add it to the destination. You
would do it inside the loop, like this:

With Me.lbSource
For Each itm In .ItemsSelected

rsDestination.AddNew
rsDestination!CatorKey = .ItemData(itm)
rsDestination!CaseName = Me.CaseName 'textbox on form
rsDestination.Update

.Selected(itm) = False

Next itm
End With

** Alternative 2 **
Requery the list box and clear its selections in one go, by reassigning its
RowSource:

With Me.lbSource
For Each itm In .ItemsSelected
rsDestination.AddNew
rsDestination!CatorKey = .ItemData(itm)
rsDestination!CaseName = Me.CaseName 'textbox on form
rsDestination.Update
Next itm

.RowSource = .RowSource

End With

Me.lbDestination.Requery

Either of these approaches ought to fix the problem, as well as relieve you
of the need to call Allen's function.

I see that you have code commented out to delete items from tblSource after
you've moved them to tblDestination. Is this something you want to do, but
have just commented out for now? If so, there's a better way to do it than
the way you've commented out.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


When I say “unmatched component,” I mean the query is technically an
unmatched query. When the user selects a value from lbSource, that
value is then shown in lbDestination. Since it’s now shown in
lbDestination, I need to trick lbSource into not showing that value.
Hope that makes sense.

Here’s the original code behind the row source of lbSource (the bound
column is the key):
SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN tblDestination ON tblSource.CatorKey =
tblDestination.CatorKey
WHERE (((tblDestination.CatorKey) Is Null));

When I remove the join I cannot reproduce the error I’ve been
describing!

Here’s the SelectItem code:
Private Sub SelectItem()

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String
Dim itm As Variant

If Me.lbSource.ItemsSelected.Count = 0 Then
Beep
Exit Sub
End If

Set rsSource = CurrentDb.OpenRecordset("tblSource", _
dbOpenDynaset)
Set rsDestination = _
CurrentDb.OpenRecordset("tblDestination", _
dbOpenDynaset)

For Each itm In Me.lbSource.ItemsSelected
theBug = Me.lbSource.ItemData(0)

rsDestination.AddNew
rsDestination!CatorKey = Me.lbSource.ItemData(itm)
rsDestination!CaseName = Me.CaseName 'textbox on form
rsDestination.Update

rsSource.FindFirst "CatorKey = '" & _
Me.lbSource.ItemData(itm) & "'"
'rsSource.Edit
'rsSource.Delete 'this will delete the value from the source table

Next itm

Me.lbSource.Requery
Me.lbDestination.Requery

End Sub

 

[alex]

Alex -

I see several things in that code to question that aren't related to the
specific issue.  I believe that I understand what's going on, though, so
let's address that specifically.

Tests that I've made suggest that when you have a multiselect list box with
various items selected in it, and you then requery that list box, Access
doesn't fully clear the selections.  Or at least, it sets
ItemsSelected.Count to 0, but it doesn't visibly clear the selected "look"
of various rows of the list box.

I suggest that, rather than using Allen Browne's ClearList function in this
case, you do either of two things:

** Alternative 1 **
Reset the .Selected flag of each item as you add it to the destination.  You
would do it inside the loop, like this:

    With Me.lbSource
        For Each itm In .ItemsSelected

            rsDestination.AddNew
            rsDestination!CatorKey = .ItemData(itm)
            rsDestination!CaseName = Me.CaseName 'textbox on form
            rsDestination.Update

            .Selected(itm) = False

        Next itm
    End With

** Alternative 2 **
Requery the list box and clear its selections in one go, by reassigning its
RowSource:

    With Me.lbSource
        For Each itm In .ItemsSelected
            rsDestination.AddNew
            rsDestination!CatorKey = .ItemData(itm)
            rsDestination!CaseName = Me.CaseName 'textbox on form
            rsDestination.Update
        Next itm

        .RowSource = .RowSource

    End With

    Me.lbDestination.Requery

Either of these approaches ought to fix the problem, as well as relieve you
of the need to call Allen's function.

I see that you have code commented out to delete items from tblSource after
you've moved them to tblDestination.  Is this something you want to do,but
have just commented out for now?  If so, there's a better way to do it than
the way you've commented out.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)



When I say “unmatched component,” I mean the query is technically an
unmatched query.  When the user selects a value from lbSource, that
value is then shown in lbDestination.  Since it’s now shown in
lbDestination, I need to trick lbSource into not showing that value.
Hope that makes sense.

Here’s the original code behind the row source of lbSource (the bound
column is the key):
SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN tblDestination ON tblSource.CatorKey =
tblDestination.CatorKey
WHERE (((tblDestination.CatorKey) Is Null));

When I remove the join I cannot reproduce the error I’ve been
describing!

Here’s the SelectItem code:
Private Sub SelectItem()

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String
Dim itm As Variant

If Me.lbSource.ItemsSelected.Count = 0 Then
   Beep
   Exit Sub
End If

Set rsSource = CurrentDb.OpenRecordset("tblSource", _
       dbOpenDynaset)
Set rsDestination = _
       CurrentDb.OpenRecordset("tblDestination", _
       dbOpenDynaset)

For Each itm In Me.lbSource.ItemsSelected
   theBug = Me.lbSource.ItemData(0)

   rsDestination.AddNew
   rsDestination!CatorKey = Me.lbSource.ItemData(itm)
   rsDestination!CaseName = Me.CaseName 'textbox on form
   rsDestination.Update

   rsSource.FindFirst "CatorKey = '" & _
       Me.lbSource.ItemData(itm) & "'"
   'rsSource.Edit
   'rsSource.Delete 'this will delete the value from the source table

Next itm

Me.lbSource.Requery
Me.lbDestination.Requery

End Sub

Dirk,
That worked (I chose option 1). I cannot reproduce the error, even
with the left join.
I really appreciate the help...the knowledge you have is truly
amazing.
p.s., I don't want to delete the values from the source table; the
code is borrowed and I've kept it intact in case I ever want to do
that sort of thing...
I'll keep pluggin' away. I still have 3 other buttons to code.
alex

 

[Dirk Goldgar]

That worked (I chose option 1). I cannot reproduce the error, even with
the left join.
Great!

p.s., I don't want to delete the values from the source table; the code is
borrowed and I've kept it intact in case I ever want to do

that sort of thing...

I suggest you comment out the "rsSource.FindFirst" line, too, as well as the
line that opens the rsSource recordset. You're not using that recordset at
all, at the moment. Also, you should add a line reight before you exit the
procedure to close rsDestination:

rsDestination.Close

I'll keep pluggin' away. I still have 3 other buttons to code.

Good luck!

 

[alex]

that sort of thing...

I suggest you comment out the "rsSource.FindFirst" line, too, as well as the
line that opens the rsSource recordset.  You're not using that recordset at
all, at the moment.  Also, you should add a line reight before you exitthe
procedure to close rsDestination:

    rsDestination.Close


Good luck!

You're right!
Will do.
Thanks again.