AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Saturday, November 19, 2005

AddItem and RemoveItem for Listboxes in Access 97/2000

Access XP and 2003 listboxes already have these methods. I made 2 functions which manipulate Listbox’ RowSource property. You have to set Listbox Row Source Type property to Value List and then you can add items using:
ListBox1.RowSource = AddItem(ListBox1.RowSource, "key1" & ";" & "New Item", 0)
And remove:
ListBox1.RowSource = RemoveItem(ListBox1.RowSource, 0)

These 2 functions assumes, that Listbox has 2 columns. For one-column code version please look at Update for "AddItem and RemoveItem for Listboxes in Access 97/2000"

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, ";")
Pos2 = InStr(Pos2 + 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, ";")
If Pos1 > 0 Then
Pos1 = InStr(Pos1 + 1, strRowSource, ";")
End If
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


6 Comments:

Anonymous Anonymous said...

GENIO

gracias mil

11:59 PM  
Anonymous Eglish said...

Thank you very much for this.

4:10 AM  
Anonymous Pradeep said...

I lost 2 hours on this issue when i deployed my application in the client place.

Thanks Alex. :))
pradeepg77@yahoo.com

8:57 AM  
Anonymous Anonymous said...

Alex, you rock :-)
Thank you very much for this code.
Really useful. Cheers.

6:51 PM  
Anonymous Anonymous said...

Perfect Code...!! Vielen Dank

Dalik

6:54 PM  
Blogger Birger Jansen said...

Old post, but still: Thanks!

4:48 PM  

Post a Comment

Links to this post:

Create a Link

<< Home