MSForms.ListBox Type Mismatch in Access

Question

I have an Access database where I use a Tab control (without tabs) to simulate a wizard. One of the tab pages has an MSForms.ListBox control called lstPorts, and a button named cmdAdd which adds the contents of a textbox to the List Box. I then try to keep the contents of the ListBox sorted. However, the call to the Sort method causes a type mismatch.

Here is the cmdAdd_Click() code behind:

Private Sub cmdAdd_Click()


    Dim test As MSForms.ListBox

    lstPorts2.AddItem (txtPortName)
    Call SortListBox(lstPorts2)


End Sub

Here is the SortListBox Sub:

Public Sub SortListBox(ByRef oLb As MSForms.ListBox)

   Dim vaItems As Variant
   Dim i As Long, j As Long
   Dim vTemp As Variant

   'Put the items in a variant array
   vaItems = oLb.List

    For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
       For j = i + 1 To UBound(vaItems, 1)
        If vaItems(i, 0) > vaItems(j, 0) Then
            vTemp = vaItems(i, 0)
            vaItems(i, 0) = vaItems(j, 0)
            vaItems(j, 0) = vTemp
        End If
       Next j
    Next i

   'Clear the listbox
   oLb.Clear

   'Add the sorted array back to the listbox
   For i = LBound(vaItems, 1) To UBound(vaItems, 1)
       oLb.AddItem vaItems(i, 0)
   Next i

   End Sub

Any help out there? Since the Sort routine explicitly references the MSForms.ListBox, most of the results from Google aren't applicable.

Jason

Answer 1

Check the type of lstPorts2:

Debug.Print "TypeName(lstPorts2): " & TypeName(lstPorts2)

Your description sounds like lstPorts2 may actually be an Access listbox rather than an MSForms.ListBox ... and those are different object types. For example, an Access listbox doesn't have the Clear method you're using in your sort routine.

Perhaps you could convert to an Access listbox and have SortListBox use the RemoveItem method for all the listbox members as a substitute for Clear.

Edit: I'm unsure what TypeName would say for a MSForms.ListBox, so I may be off base here. Still I would open the form module, type Me.lstPorts2. and see if IntelliSense offers Clear as one of the methods/properties.

I'm on shaky ground with MSForms. Can you make lstPorts2 an Access listbox instead? If so, I think revising SortListBox to this could work:

Public Sub SortListBox(ByRef oLb As ListBox)

   Dim vaItems As Variant
   Dim i As Long, j As Long
   Dim vTemp As Variant

   'Put the items in a variant array '
   vaItems = Split(oLb.RowSource, ";")

    For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
       For j = i + 1 To UBound(vaItems, 1)
        If vaItems(i, 0) > vaItems(j, 0) Then
            vTemp = vaItems(i, 0)
            vaItems(i, 0) = vaItems(j, 0)
            vaItems(j, 0) = vTemp
        End If
       Next j
    Next i

   'Clear the listbox '
   For i = (oLb.ListCount - 1) To 0 Step -1
      oLb.RemoveItem (i)
   Next i

   'Add the sorted array back to the listbox '
   For i = LBound(vaItems, 1) To UBound(vaItems, 1)
       oLb.AddItem vaItems(i, 0)
   Next i

End Sub

Actually, clearing an Access listbox whose RowSourceType is "Value List" could be simpler:

oLb.RowSource = ""

Answer 2

I guess switching back to a regular ListBox worked.

I had wanted to use an MSForms one since that listbox would be populated with existing data, but the user could add new data. Anything the user added would have an id of -1, and then all the new ones would be easily identifiable.

Oh well. I appreciate the help greatly.