Update for “AddItem and RemoveItem for Listboxes in Access 97/2000”

Some update on previous article. The function I made for ListBox/ComboBox with 2 columns. In order to have it working for one-column controls – you have to remove 4 lines, and here the final code:

Public Function RemoveItem(strRowSource As String, _
                    lngItemNum As Integer) As String
    Dim Pos1 As Long, strResult As String, Pos2 As Long
    Dim lngCount As Long, i As Integer
  
    strResult = strRowSource
    Pos1 = 1
    For i = 0 To lngItemNum
        If (Pos1 > 0) And (i = lngItemNum) Then
            Pos2 = InStr(Pos1 + 1, strRowSource, ";")
            If Pos2 = 0 Then Pos2 = Len(strRowSource)
            strResult = Left(strRowSource, Pos1 - 1)
            If Len(strResult) > 0 Then _
                strResult = strResult & ";"
            strResult = strResult & _
                Mid(strRowSource, Pos2 + 1)
            If Right(strResult, 1) = ";" Then _
                strResult = _
                    Left(strResult, Len(strResult) - 1)
        End If
      
        Pos1 = InStr(Pos1 + 1, strRowSource, ";")
    Next i
  
    RemoveItem = strResult
End Function
Public Function AddItem(strRowSource As String, _
        strItem As String, _
        lngItemNum As Integer) As String
    Dim Pos1 As Long, strResult As String, Pos2 As Long
    Dim lngCount As Long, i As Integer
  
    strResult = strRowSource
    Pos1 = 1
    For i = 0 To lngItemNum
        If (Pos1 > 0) And (i = lngItemNum) Then
            strResult = Left(strRowSource, Pos1 - 1)
            If Len(strResult) > 0 Then _
                strResult = strResult & ";"
            strResult = strResult & strItem
            If Len(strResult) > 0 Then _
                strResult = strResult & ";"
            If Pos1 > 1 Then Pos1 = Pos1 + 1
            strResult = strResult & _
                Mid(strRowSource, Pos1)
            If Right(strResult, 1) = ";" Then _
                strResult = _
                    Left(strResult, Len(strResult) - 1)
            Exit For
        End If
      
        Pos1 = InStr(Pos1 + 1, strRowSource, ";")
        If Pos1 > 0 Then
            Pos1 = InStr(Pos1 + 1, strRowSource, ";")
            If Pos1 = 0 Then Pos1 = Len(strRowSource) + 1
        End If
    Next i
  
    AddItem = strResult
End Function

Thanks to our reader Bob, who pointed to this issue!