Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

problem clearing a selectbox in VBA.

Posted on 2007-03-30
8
286 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

838 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