problem clearing a selectbox in VBA.

Function ClearList(lst) As Boolean
    Dim varItem As Variant

    With lst
        For Each varItem In .ItemsSelected
            varItem.Selected = False
        Next
    End With

    ClearList = True

End Function

I want to use the above function to clear the selections made in a selectbox.
However, I get the following error:

error# 424
Object required
(message was in dutch, so I translated).

I used the correct reference to the object while calling the function:
ClearList (Me!filter_CL_contract_status)

I have a `sister`-function that will get the selected items and convert that to a string that can be used in a where-condition. That one works just fine.

What am I doing wrong??
LVL 6
DoppyNLAsked:
Who is Participating?
 
jefftwilleyCommented:
on the form

ClearListBox me.name, "yourcontrolname"

The function

Function ClearListBox(strForm As String, strControl As String)
Dim I As Long
Dim f As Form
Dim c As Control
With Forms(strForm).Form(strControl)
    For I = 0 To .ListCount - 1
        .Selected(I) = False
    Next I
End With
End Function

0
 
dqmqCommented:
Try this:

With lst
        For Each varItem In .ItemsSelected
           .Itemdata(varItem).Selected = False
        Next
    End With
0
 
DoppyNLAuthor Commented:
no effect.

note: the actual error message occurs on the line with for.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dqmqCommented:
On second thought, the above for each construct may have the problem of progressively diminishing the .itemsselected collection at the same time it is iterating through it.  You may get undesirable results. If soi, try a for/next loop.

dim i as integer
with lst
   for i = 0 to .listcount -1
    .itemdata(i).selected = False
   next
 end with
0
 
DoppyNLAuthor Commented:
Same result; again `an object is required` on the line with for.

The sister function:

Function MultiWherePart(FormField, TableField As String, Optional Delimiter As String = "")
    ' Variables for multi-select
        Dim MultiSelected As Variant      'Selected items
        Dim MultiLength As Long          'Length of string
    '
    ' Generate part
        With FormField
            For Each MultiSelected In .ItemsSelected
                If Not IsNull(MultiSelected) Then
                    MultiWherePart = MultiWherePart & Delimiter & .ItemData(MultiSelected) & Delimiter & ","
                End If
            Next
        End With
       
        MultiLength = Len(MultiWherePart) - 1
        If (MultiLength > 0) Then
            MultiWherePart = TableField & " IN (" & Left$(MultiWherePart, MultiLength) & ")"
        End If
    '
End Function

Here the variable is called FormField
Both functions are called with exactly the same reference to exact the same object.
But MultiWherePart works fine...
0
 
dqmqCommented:
You are passing a listbox control, right?  Try this:

Function ClearList(lst as control) As Boolean

Also, put a message box inside the for loop to see if you are failing the first time through or after you have de-selected some of the rows.
0
 
DoppyNLAuthor Commented:
Using this:
Function ClearList(lst as control) As Boolean

The same error moves to the function call.
So it seems that the object I'm passing is not what it supposed to be.

However, the function cal to the other function works fine. Also when I also add "control" there.
Both function calls look like this.

ClearList (Me!filter_CL_contract_status)
Add = MultiWherePart(Me!filter_CL_contract_status, "[CL_contract_status]")
0
 
DoppyNLAuthor Commented:
Jeftwilley's suggestion works perfectly :)

All items are deselected as required.

Accepting that solution.

A big thanks to everyone for their help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.