Solved: populating listbox2 with listbox1

[maryam]

we have two listboxes. we want to populate listbox2 with selected items of listbox1. there is "Add" and "delete" bottoms. We want to make sure no duplication is present. Tommy suggested these codes, but I dont know why I can still add a repeated item from listbox1 to listbox2! ( the last lines are for populating datagrid, dont become confused)

[VBA] Private Sub cmdAdd_Click()
Dim mI As Long
Dim CheckDic As Dictionary
Set CheckDic = New Dictionary
For mI = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(mI) Then
If CheckDic.Exists(ListBox1.Selected(mI)) Then
Else
CheckDic.Add ListBox1.Selected(mI), CheckDic.Count + 1
ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)
End If
End If
Next

'Set FrmMaterialStream.DataGrid1.DataSource = r

End Sub[/VBA]

[maryam]

I forgot to say:
Add a reference to Microsoft Scripting Runtime

[malik641]

It's because you're not fully populating the Dictionary object before using CheckDic.Exists(). Each time you press the Add command button it's creating a new instance of the object and the variables that were in it from the previous time you pressed the button are now lost.

Give this a try:
[vba]Private Sub cmdAdd_Click()
Dim mI As Long
Dim CheckDic As Dictionary
Set CheckDic = New Dictionary

' First populate the Dictionary object (CheckDic)
' with all items in listbox2...if any
If ListBox2.ListCount > 0 Then
For mI = 0 To ListBox2.ListCount - 1
CheckDic.Add ListBox2.Selected(mI), Nothing
Next
End If

' Now check for items from listbox1 that are to be added
' into listbox2 if they already exist.
For mI = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(mI) Then
If Not CheckDic.Exists(ListBox1.Selected(mI)) Then
ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)
End If
End If
Next
End Sub[/vba]

[maryam]

It gives debug when I want to add items!

[maryam]

CheckDic.Add ListBox2.Selected(mI), Nothing

this line becomes yellow. we dont high light the items of listbox2, so how are they selected?

[malik641]

My apologies Maryam. I'm not testing this code. Try this:

[vba] ' First populate the Dictionary object (CheckDic)
' with all items in listbox2...if any
If ListBox2.ListCount > 0 Then
For mI = 0 To ListBox2.ListCount - 1
CheckDic.Add ListBox2.List(mI), Nothing
Next
End If[/vba]

[malik641]

Ok, tested and came up with (hopefully) a final solution:
[VBA]Private Sub cmdAdd_Click()
Dim mI As Long
Dim CheckDic As Dictionary
Set CheckDic = New Dictionary

' First populate the Dictionary object (CheckDic)
' with all items in listbox2...if any
If ListBox2.ListCount > 0 Then
For mI = 0 To ListBox2.ListCount - 1
If Not CheckDic.Exists(ListBox2.List(mI)) Then _
CheckDic.Add ListBox2.List(mI), Nothing
Next
End If

' Now check for items from listbox1 that are to be added
' into listbox2 if they already exist.
For mI = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(mI) Then
If Not CheckDic.Exists(ListBox1.List(mI)) Then
ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)
End If
End If
Next
End Sub[/VBA]

[maryam]

do I have to Add a reference to Microsoft Scripting Runtime? What is it?

[maryam]

I want to write the codes for delete bottom, to delete from listbox2 as well as dataGrid or r. I started with this:
Private Sub CmdDelete_Click()
' If row is not selected, removing of input details cannot take place
If ListBox2.ListIndex = -1 Then Exit Sub
' Deleting the selected row from the invisible input worksheet
For i = listbox2.listcount To 1 -1
If ListBox2.Selected(i ) Then


I dont know how to disable these:


ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)

[maryam]

[malik641]

Maryam,
Just so you know, I use GMT -5. You posted your last two posts (9 & 10) at 3:00AM and 7:00AM (my time). Just hang tight.

I want to write the codes for delete bottom, to delete from listbox2 as well as dataGrid or r.

What?
1. How do you know that the last item in Listbox2 is the last row in the datagrid?
2. Unless whatever is in ListBox2 (last item) you are going to search through the datagrid for the right Row to delete. Is that what you're talking about?
2a. Or do you mean delete the last row of the Datagrid and if it's in the Listbox2 then delete that too?
3. You should let the user select an item from Listbox2 to delete rather than deleting the last item in Listbox2.
3a. If talking about 2a (above) then you should let the user be able to delete a selected row in the Datagrid.

I dont know how to disable these:

ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)

Disable? Under what condition?


Unfortunately I have no datagrid control available at my work. So I have no idea how you would delete a row from that control. Sorry Maryam, I'm no help with the Datagrid control while at work.

[maryam]

we have listbox2 which is populated with selected items from list box1. then the same list which is in listbox2 will go to dataGrid which is in another form. means when I open that form first column of dataGrid is already populated with what is in listbox2. the codes are here:
ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)

Now we want to have a delete bottom to let the user delete from listbox2 in case he selected wrongly to add from listbox1. It can be any row but generally last row as it is the last item added. simoultaniousely that row should be deleted from DataGrid.
1) the row in dataGrid is the same as the row in listbox2 and both are populated with the same list.
2) we first delete from listbox2 then we want it to be deleted from dataGrid as a result.
I dont mean 2a
3) yes
3a) NA

Disabled I meant by these codes we add to listbox2 and r (datasource of DataGrid) and now I want to delete.

[malik641]

Ok, thanks for the info.

Since Listbox2 reflects the Datagrid's rows, you can delete the last row in the datagrid and use that row number minus 1 to get the proper List(Index) to delete.

Say your last row is 10 so...

[VBA]' Code to delete row 10 from datagrid (not sure how to do this)
' ...

Listbox2.RemoveItem(DataGrid.LastRow - 1)[/VBA]

Still unsure about how to remove the datagrid's last row..

[maryam]

Tommy sent me these codes. Actually we populate datagrid with datasource which is r.

Dim mI As Long

For mI = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(mI) Then
r.MoveFirst
r.Find "Selected = '" & ListBox2.List(mI) & "'"
r.Delete adAffectCurrent
r.MoveNext
ListBox2.RemoveItem mI
End If
Next
Set UserForm1.DataGrid1.DataSource = r

This gives me debug when I copy it to my file:
"Item cannot be found in the collection corresponding to the requested name or ordinal" and
r.Find "Selected = '" & ListBox2.List(mI) & "'" is yellow.

I dont know, maybe becasue I defined r as Public r As ADOR.Recordset
in a module.

Hope I didnt make you confused.

[malik641]

I'll continue posting in the other thread you started regarding the deleting of Listbox2 items and datagrid items since we've seemed to solve the populating listbox2 from listbox1.

[maryam]

yes. this is solved. thank you.