Solved

problem clearing a selectbox in VBA.

Posted on 2007-03-30
8
281 Views
Last Modified: 2013-11-27
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??
0
Comment
Question by:DoppyNL
  • 4
  • 3
8 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18822293
Try this:

With lst
        For Each varItem In .ItemsSelected
           .Itemdata(varItem).Selected = False
        Next
    End With
0
 
LVL 6

Author Comment

by:DoppyNL
ID: 18822312
no effect.

note: the actual error message occurs on the line with for.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18822314
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
 
LVL 6

Author Comment

by:DoppyNL
ID: 18822323
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 42

Expert Comment

by:dqmq
ID: 18824479
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 125 total points
ID: 18831102
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
 
LVL 6

Author Comment

by:DoppyNL
ID: 18834736
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
 
LVL 6

Author Comment

by:DoppyNL
ID: 18834743
Jeftwilley's suggestion works perfectly :)

All items are deselected as required.

Accepting that solution.

A big thanks to everyone for their help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now