Hello,
I was wondering if somebody could help me with this code. I have two listboxes on a form and the left listbox has a table as a rowsource and the right has 'Value' as its rowsource. There are two buttons in the middle of the listboxes, one 'add' and the other 'remove'. The problem is when you remove an item from the right listbox, it will leave a blank line where the item was located in the listbox which looks bad and also adds to the listbox's listcount. The item names in the left listbox can consist of one word, two words, or two words with a dash between them. (eg. xxxxx, xxxx xxxxx, xxxxx-xxxxxx) Here is the code for the 'add' and 'remove' buttons:

Private Sub cmdAdd_Click()

On Error GoTo cmdAdd_Click_ERR

If Me.lstYourList.ListCount = 0 Then
Me.lstYourList.RowSource = Trim(Me.lstProducts)
ElseIf Me.lstYourList.ListCount < 10 Then

If InStr(Me.lstYourList.RowSource, Me.lstProducts) > 0 Then
MsgBox "You have already selected this item.", vbInformation + vbOKOnly
Else
Me.lstYourList.RowSource = Me.lstYourList.RowSource & ";" & Trim(Me.lstProducts)
End If
Else
MsgBox "You have reached the item limit.", vbInformation + vbOKOnly
End If


cmdAdd_Click_EXIT:
Exit Sub

cmdAdd_Click_ERR:

MsgBox "Please select an item to add to your list.", vbInformation + vbOKOnly

Resume cmdAdd_Click_EXIT


End Sub
________________________________________________________________________________
__________

Private Sub cmdRemove_Click()

On Error GoTo cmdRemove_Click_ERR

Dim intI As Integer
Dim intJ As Integer
Dim strFirst As String
Dim strSecond As String

If Len(Me.lstYourList & vbNullString) = 0 Then
MsgBox "Please select an item to remove from your list.", vbInformation + vbOKOnly

ElseIf Me.lstYourList.ListCount = 0 Then
MsgBox "Please select a product to remove from your list.", vbInformation + vbOKOnly
Else
intI = InStr(Me.lstYourList.RowSource, Me.lstYourList)
intJ = Len(Me.lstYourList)
strFirst = Left$(Me.lstYourList.RowSource, intI - 1)
strSecond = Mid$(Me.lstYourList.RowSource, intI + intJ + 1)

Me.lstYourList.RowSource = strFirst & strSecond
End If

Me.Requery
cmdRemove_Click_EXIT:
Exit Sub

cmdRemove_Click_ERR:

MsgBox "Please select an item to remove from your list.", vbInformation + vbOKOnly


Resume cmdRemove_Click_EXIT

End Sub
________________________________________________________________________________
________

Thanks,
Matt

Hi there,

In your cmdRemove_Click procedure adjust this line:

intJ = Len(Me.lstYourList)

to :

intJ = Len(Me.lstYourList) + 1


You need to remove the ';' as well.


hth,

d

Still didn't work. Thanks for trying though. This is going to be a tough one.

Matt

Here is a demo, using a different approach, that should do what you want.

hth,
Jack

Also, in addition to Jack's example, have a look at the RemoveItem method for listboxes/comboboxes available for Value Lists in Acc2002 and higher.


hth,

d

cheekybuddha -

I tried to slip the demo past you but I should have known better!!!

I was not aware of the RemoveItem bit so thank you for that... Now all I have to do is try to remember it!!

Jack

Hi Jack,

LOL! I personally don't like the RemoveItem method because it only works with Value List type RowSources and IIRC it wasn't available in A2K, so can cause versioning issues. I would go about the process differently too, but for what the Matt is trying to achieve perhaps it is time for RemoveItem!

I can't remember whether I mentioned this already, but I would just like to say how dapper and distinguished you look with your new coiffure!



d

I appreciate the kind words about the new 'do'! It was time to tell the truth about my real hair color...

I have never liked Value Lists and I never use them. I think MS should eliminate them and force users to create a table or query as the ONLY Row Source....

Jack

I'll second that! I think these methods/properties are there to make them similar to VB list/combo boxes which aren't able to be databound.

d

Ah ha! The old 'similar to VB' thingo! Since I have never used VB I had no idea how their list/combo's worked. Now I know!

Jack

Hi,

The only time the separator won't get removed is when you remove the very last item in the second listbox. (ie.say you have 5 items in the list, if you remove the fifth item and then add a new item it will leave the blank line where the fifth item was; the rowsource will look like item1;item2;item3;item4;;item6. But if you remove the second item for example then every item below it will move up and everything works fine if you add more items to the list.) I've tried all the suggestions i have been given and nothing seems to fix this problem. Does anyone have anymore ideas?

Thanks,
Matt

I do not know how you are adding new items to the listbox, but I tried adding a new item to the table in the demo I sent you and I had no problem. (There was data in both list boxes at the time.) I do not know what the problem is so my only suggestion is that you start a new thread (this on is getting a bit long in the tooth) and post you db so that someone can see what you are doing. Be sure and compact and repair and zip the db and the max size you can post is 500k.

hth,
Jack