Method or member not found - RemoveItem

[cutestuff]

hi,

I can't figure this out. I've added the Microsoft Office 8.0 Object Library (just like microsoft suggested) and I am still getting this error: Method or data member not found.

I am trying to use the .RemoveItem method on my listbox.
I would like to remove the selected items from the list.

 If lst2.ListIndex >= 0 Then
        lst2.RemoveItem lst2.ListIndex
 End If

 

[missinglinq]

AddItem and RemoveItem work differently in VBA than they do in straight Visual Basic. In straight VB, if I remember correctly, you can apply them to any combobox/listbox. But in VBA, they can only be used with comboboxes from a CommandBar combobox control. In other words, they cannot be used in a free standing, programmer defined combobox.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[cutestuff]

That makes sense then.

Any suggestions on how I can remove the selected item on the list without using RemoveItem?

Thanks in advance...

 

[PHV]

Rebuild the RowSource dynamically ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cutestuff]

hi PHV,

Here is my code:

Private Sub cmdAddItemstoList2_Click()
Dim lst1 As ListBox, lst2 As ListBox
    Dim itm As Variant

    Set lst1 = Me!List1
    Set lst2 = Me!List2
    ' Check selected items.
    'Check whether there are more than 5 systems that have been copied
    If lst2.ListCount > 4 Then
        MsgBox "You cannot add more than 5 Systems.", vbOKOnly, "System Not Added"
    Else
       For Each itm In lst1.ItemsSelected
             ' Set RowSource property for first selected item.
                If lst2.RowSource = "" Then
                    lst2.RowSource = lst1.ItemData(itm)
                Else
            ' Check whether item has already been copied.
                If Not InStr(lst2.RowSource, lst1.ItemData(itm)) > 0 Then
                    lst2.RowSource = lst2.RowSource & ";" & lst1.ItemData(itm)
                    End If
                End If
    Next itm
    End If
    
End Sub

-----------
Dim lst1 As ListBox, lst2 As ListBox
Dim strSQL As String, strMsg As String

Set lst1 = Me!List1
Set lst2 = Me!List2

' Initialize SQL string.
strSQL = "SELECT System, Description FROM tblSystems ORDER By System, Description;"

' Fill List1.
With lst1
    .RowSourceType = "Table/Query"
    .RowSource = strSQL
    .ColumnCount = 2
End With
' Specify that List2 is a value list.
lst2.RowSourceType = "Value List"

End Sub

I have 2 listboxes where list1 comes from a query and any item(s) the user selects form list1 gets copied to list2. I can't figure out how to remove an item the user selects on list 2.
I have the following code for removing all items in list2:

Private Sub cmdClearAllList2_Click()
    'Clear Selections
    
        List2.RowSource = ""        
End Sub

But how do I clear one at a time?

Thanks in advance...

 

[PHV]

Typed, untested:
Dim a
If lst2.ListIndex >= 0 Then
a = Split(lst2.RowSource, ";")
a(lst2.ListIndex) = ""
lst2.RowSource = Replace(Join(a, ";"), ";;", ";")
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

I seem to remember gnawing at this problem a while back, and the problem is that while you can change the RowSource dynamically in code, you can only do so for that particular session. When the form is closed then reopened, the original RowSource is reinstated. Apparently the RowSource can only be changed permanently in Design View.

Anyone know how to go into Design View and back out from code?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

Missinglinq - wouldn't you just make the listbox's RowSource blank in design mode, then set it appropriately when the form loads?

 

[missinglinq]

You're stuck with the same problem! If you load the RowSource in the Form_Load event with, for instance

Apples
Cantaloupes
Grapes

then, in code, you add

Bananas
Peaches

the next time the form loads, the RowSource is once again going to be

Apples
Cantaloupes
Grapes

Unless someone knows of a way, in code, to go into Design View before adding or deleting an item, the only way to permanently change the data in the combobox is to have it feed off of a table/query and add/delete from the underlying
table.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

The OP doesn't say he wants to persist the state of the listbox between sessions, in which case I think PHV's code handles the problem elegantly.

If the OP does want to persist the data, and for whatever reason wants to handle the saving himself (for example, maybe to a text file), then he would load the data on the form load event, building the delimited string for the RowSource as the data is read in.

During the session, items will be added and removed (with PHV's code). When the user closes the form, the current state of the listbox will be saved back to the same source it came from.

It's really no different then how you would do it with a VB6 listbox.

 

[maxhugen]

Is there any reason why you can't create a lookup table which stores the combo's values? That way you can freely add/remove records and they'll persist.

Max Hugen

http://www.hugen.com.au

Australia

 

[MajP]

I do what maxhugen recommends. I make a table called tblSelected. The only thing in the table is the ID of fields that were selected from list one to show in list two. This allows me to easily move from the main list to the "selected" list.

Option Compare Database

Public rsSelected As DAO.Recordset
Public strQryMode As String


Private Sub Form_Open(Cancel As Integer)
  Set rsSelected = CurrentDb.OpenRecordset("tblSelected", dbOpenDynaset)
End Sub

Private Sub lstSelectedNames_AfterUpdate()
  'If you click on a selected name it removes it from the selected list
  rsSelected.FindFirst ("fkIntPersonID = " & lstSelectedNames)
  rsSelected.Delete
  lstSelectFrom.RowSource = getNewQuery
  lstSelectedNames.Requery

End Sub

Private Sub lstSelectFrom_AfterUpdate()
  'if you select a name it adds the ID to tblSelected
  rsSelected.AddNew
  rsSelected.Fields("fkIntPersonID") = lstSelectFrom.Value
  rsSelected.Update
  If strQryMode = "FullWord" Then
    lstSelectFrom.RowSource = getNewQueryFullWord
  Else
     lstSelectFrom.RowSource = getNewQuery
  End If
  lstSelectedNames.Requery
  
End Sub

Public Function getNewQuery() As String
  'The query checks what is in the tblSelected
  Dim strSql As String
  strSql = "SELECT ID, strLastName FROM tblNames "
  strSql = strSql & " WHERE ID Not In (select fkIntPersonID from tblSelected) "
  strSql = strSql & " ORDER BY strLastName"
  getNewQuery = strSql
End Function