what is wrong with this list box code ?

[PhilipEwen]

Hi,

I have a listbox on a form which is based on a query and shows how which staff members are associated to the client.

I have added a button which opens a form and would allow you to select additional staff to associate.
This form comprises as multi-select list box showing all staff members pulled from a table.

What I am looking to do is to automatically highlight the the staff members in this list box, based on the previous form's list box.

i.e. If Bob and Bill are showing in form contacts_modify then when i open form selected_staff I would like the multiselect list box to already have Bob and Bill selected as this would save the user from highlighting them again.

Here is my code ( based on a few bits i have picked up from here )

Dim frm As Form, ctl As Control
Dim vItem As Variant
Dim thisfrm As Form, thisctl As Control


Set frm = Forms!contacts_modify
Set ctl = frm!selected_staff
Set thisfrm = Forms!admin_select_staff
Set thisctl = thisfrm!List0
For Each vItem In ctl.ItemsSelected
thisfrm.thisctl.Selected = True
Next vItem
[code]


Can anyone tell me why this does nothing ?

Many thanks

Phil.

 

[MattS]

You need another loop in there. For each item selected in the first list box, you then need to loop through the whole of the second listbox until you find the matching member of staff to select.

 

[PhilipEwen]

Wow, thanks for the rapid reply !

I am having a bit of trouble in how to do this as I am quite new to VBA.

I have modified the code as follows:

Dim frm As Form, ctl As Control
Dim vItem As Variant, thisvItem As Variant
Dim thisfrm As Form, thisctl As Control


Set frm = Forms!contacts_modify
Set ctl = frm!selected_staff
Set thisfrm = Forms!admin_select_staff
Set thisctl = thisfrm!List0


For Each vItem In ctl.ItemsSelected
For Each thisvItem In thisctl
thisfrm.thisctl.Selected(vItem) = True
Next thisvItem
Next vItem

[code]

Yet it does not seem to work. I think it is something to do with the thisfrm.thisctl.Selected(vItem) = True
line, but not entirely sure.

Sorry to be a pain but would you mind looking at it and explaining where i am going wrong ?


Thanks

 

[PhilipEwen]

AHA - Just realised what i have done.

I am trying to compare 2 lists, but my first list box ONLY show staff selected and is based on a query.
I therefore have to pull the staffid from the hidden id column and cycle through the new list and highlight the respective staff member.

Incase anyone wants to know how to cycle though the items and highlight based on the SAME list boxes, then

Dim frm As Form, ctl As Control
Dim vItem As Variant, thisvItem As Variant
Dim thisfrm As Form, thisctl As Control


Set frm = Forms!contacts_modify
Set ctl = frm!selected_staff
Set thisfrm = Forms!admin_select_staff
Set thisctl = thisfrm!List0


For vItem = 0 To ctl.ListCount - 1
If ctl.Selected(vItem) = True Then thisctl.Selected(vItem) = True
Next vItem

[code]

Thanks for your help MattS