Listbox populate approach and error question 2

[LikeThisName]

what is the best approach to take?

here is the hypothetical situation. i want to form a sports team and assign a captain.

This is the approach i thought I should take
i have two listboxes. the first is built off a query selecting all available players.
the second non-multiselect listbox appears on the form after multiple select listbox has been verified by hitting a button.

i wanted to populate the second listbox with selected items from the first listbox, this is where the captain would be selected.

first listbox works fine but having trouble populating second. this is the code i used:

For Each Item In Me.ListofMembers
Me.ListofChosenMembers.AddItem Item
Next

 

[wbwillson]

I set up something similar while creating an ad-hoc report form. I have a listbox which contains field names pulled from a table. The user can select as many of the fields as they like (by holding the control key). I then have a button which moves the selected fields to another empty listbox to the right of it. My Field listbox is named "lbFields" and my output listbox is named "lbOuput", the code for the Move button is:

Private Sub btnMove_Click()

Dim strVal As String
Dim varItem As Variant
Dim ctl As Control
Dim rst As DAO.Recordset

Set ctl = Me.lbFields
For Each varItem In ctl.ItemsSelected
strVal = strVal & ctl.ItemData(varItem) & ", "
Next

With lbOutput
.RowSourceType = "Value List"
.RowSource = strVal
.Value = strVal
End With

Set ctl = Nothing

End Sub

Hope that helps.

Bill

 

[LikeThisName]

Thanks Bill

Let me show you what I came up with and explain you the same obstacle I am facing with yours.

Sub AddItemsToList()
Dim x As Integer
For x = 0 To 228
If Me.ListofMembers.Selected(x) Then
Me.ListofChosenMembers.AddItem Me.ListofMembers.ItemData(x)
Me.ListofMembers.Selected(x) = False
End If
Next x
end sub

now with both your code which works great and I appreciate your quick response and my code. only returns the bound item.
for instance the first listbox shows players names but is bound by id. i need the second listbox where the selected items are moved to show players' names and be bound by ID.

both our codes only move the playerid.

do you have any suggestions.

and thanks for your original post.

 

[LikeThisName]

and i just wanted to point out to any third party learning from this that Bill's code is better, because in my original solution i assumed 229 will always be the number of available players which is not smart.

 

[wbwillson]

So are you saying that your first listbox contains for example the players last names, and you want to be able to select those last names and then when you press the move button it copies the entire name (i.e., first MI and last) to the new box?

Maybe it will help if I include the code I use to populate my lbFields listbox:

I have a combobox named cboTableNames, this combo box uses an SQL statement in it's RowSource property to lookup all my tables in the database:

SELECT [MSysObjects].[Name],[MSysObjects].[Type] FROM MSysObjects WHERE (([MSysObjects].[Type])=6)

type 6 is for Linked Tables. Then in the BeforeUpdate event procedure is use this code:

Private Sub cboTableNames_BeforeUpdate(Cancel As Integer)

With lbFields
.RowSourceType = "Field List"
.RowSource = cboTableNames.Value
End With

End Sub[/color]

In my form, when I use the move button I get the field names I've selected in my lbOuput listbox not just the ID. let me know if this helps at all.

Bill

 

[wbwillson]

I just had another thought about this, could try setting lbfields afterupdate event to:

lbOutput.RowSource = "SELECT DISTINCT [" & lbFields & "] _
FROM [" & cboTableName & "] ORDER BY _
[" & lbFields & "];"

I haven't tested this, but should work.

Bill

 

[LikeThisName]

Sorry Bill now I'm lost with the recent code you provided.

what i wanted to know how to do was transfer all columns from one listbox to another. where your code only transfered the bound column.

see my first column had PlayerID as as column1, the bound column, and playerName as column2, the column shown.

i wanted to transfer the selected items, both columns to then second listbox, where a captain would be selected.

maybe i'm making this overly complicated which brings me back to my approach question.

but determined to do it this way for learning purposes..
what i've started done is create a 3rd column in the first listbox, its playerID_Name, a union of playerID and playerName, then i made my 2nd listbox have one column.

the reason for this is i want the people to select the names but i need to use the id for my tables because players could have exact same name.

i appreciate your help, a lot.

now i am stripping the playerID_Name to just get the id, so i can do what i need to do with it.

is this entirely to elaborate? if you know a better way now that i specified or any further advice.. as always its appreciated

 

[LikeThisName]

Bill,

For Each varItem In ctl.ItemsSelected
strVal = strVal & ctl.ItemData(varItem) & ", "
Next

the comma works great except adds a blank item as the last item of the listbox.

 

[LikeThisName]

rewrote it with an if statement and works grrrreat!

For Each varItem In ctl.ItemsSelected
If strVal <> &quot;&quot; Then
strVal = strVal & &quot;, &quot;
End If[/color
strVal = strVal & ctl.ItemData(varItem)
Next

 

[wbwillson]

That's great, I didn't even realize it was adding a blank at the end.

Bill