.RemoveItem Methode

[Marcel Stoop]

The .RemoveItem Methode is not in Supported Access. That's why I used the
Technical Paper writen by Jim Ferguson: "Use Classes to Enhance List and
Combo Boxes in Access 97".
(http://www.fmsinc.com/tpapers/97classes/index.html)
This is a great paper and it almost solved my problems.

According to this paper I have created a class, to build the .RemoveItem /
..AddItem Methode.
Within this class I've managed to create a Public Sub () which stores the
existing Items in List Box Me!lstMyList in the db. I used the SQL Statement:
INSERT INTO (...) VALUES ConcatRowSourceStrings .The variable
ConcatRowSourceStrings points to the Items listed in the Collection
mcolItems.
This works perfectly fine.

Subsequently I created another Public Sub () in this Class which retrieves
the Stored Items in the db, and write them in the Listbox Me!lstMyList
I saved the retrieved Items in a variable sRetItems which I defined as a
String.
With:
mColItems.Add sRetItems , I added the retrieved items to the Collection
mControl.RowSource = ConcatRowSourceStrings(), Putting the retrieved Items
in Me!lstMyList
Also this works perfectly fine.

My Problem is following.
- I have retrieved 4 Items, they are correctly shown in the Listbox
- I want to delete a single Item from the Listbox using the Public
Sub RemoveSelected (.) methode which is also described in the mentioned
technical Paper written by Jim Ferguson.
- If I have a look in the Collection I see that the 4 Items are
there (MsgBox ConcatRowSourceStrings )
- If I count the Items (MsgBox mColItems.Count) I see that it
count just 1 Item
- Deleting an Item doesn't work, nothing happen. When I add A new
Item to the Listbox by using Public Sub (.), I can only remove that specific
item

I Guess, the 4 Retrieved Items are being saved as one String in mcolItems,
rather than 4 single Items.

I would sure appreciate, if somebody could point me in the right direction
how to solve this problem

Thanks
Marcel

 

[Douglas J. Steele]

If you're storing the results of ConcatRowSourceStrings in the table, then
you're only storing a single piece of text. While the listbox will recognize
that it should parse that string into 4 entries, it is just one entry in the
collection.

It's not clear from your post whether you're actually using Access 97 or
not.

If you're using a newer version, then you can use the Split function on
sRetItems to convert it into a 4 element array. You can then add each of the
4 elements individually to the collection.

If you are using Access 97, you'll need to build your own equivalent to the
Split function. I use the following function Terry Kreft wrote:

Public Function MySplit( _
ByVal Expression As String, _
Optional Delimiter = " ", _
Optional limit As Long = -1, _
Optional Compare As Integer = vbBinaryCompare _
) As Variant
'*******************************************
'Name: MySplit (Function)
'Purpose: Emulates the A2k/A2k2 MySplit function
'Author: Terry Kreft
'Date: December 13, 2001, 02:47:07
'Called by: Any
'Calls: None
'Inputs:
' Expression - The string to MySplit
' Delimiter - The delimiter to MySplit on
' limit - How many terms to return
' (Default -1 return all terms)
' Compare - How to make the string comparison
' for the delimiter
' This should be
' vbBinaryCompare = 0 (Default)
' vbTextCompare = 1
' vbDatabaseCompare = 2
'Output:
'*******************************************

On Error GoTo Err_MySplit

Dim varValues As Variant
Dim lngCount As Long
Dim intInstr As Integer
Dim intLenDelim As Integer
Const ARRAY_LOW_BOUND = 0

varValues = Array()
If limit <> 0 Then
lngCount = 0
intLenDelim = Len(Delimiter)
intInstr = InStr(1, Expression, Delimiter, Compare)
Do While intInstr <> 0 And lngCount - limit + 1 <> 0
ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
varValues(lngCount) = Left(Expression, intInstr - 1)
Expression = Mid(Expression, intInstr + intLenDelim)
intInstr = InStr(1, Expression, Delimiter, Compare)
lngCount = lngCount + 1
Loop
If Len(Expression) <> 0 Then
ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
varValues(lngCount) = Expression
End If
End If

End_MySplit:
MySplit = varValues
Exit Function

Err_MySplit:
Err.Raise Err.Number, "MySplit", Err.Description
Resume End_MySplit

End Function

 

[Marcel Stoop]

Hi Douglas

Thanks for your help
Actually I'm using Access2000
Is it possible for you to give me an example of how to use the Split
Function.

Thanks Again
Marcel

 

[Douglas J. Steele]

Assuming sRetItems holds the string, and it contains a number of single
values semicolon delimited (as in "Tom";"Dick";"Harry"), you need something
like:

Dim intLoop As Integer
Dim varValues As Variant

varValues = Split(sRetItems, ";")
If IsNull(varValues) = False Then
For intLoop = LBound(varValues) To UBound(varValues)
mColItems.Add varValues(intLoop)
Next intLoop
End If

 

[Marcel Stoop]

Hi Doug

Manny thanks for the help

Regard
Marcel