Move Data from one listbox to another

[WickidWe]

Hi I have two particlular listboxes in an Access Form the first is named listEmp and the second is listAllocated


the source of the first is a query which is based on previous combo boxes.
how can i move selected or all items from one box to another and back if needed.
I need the items moved to be removed from the original box so the data is only in one or the other box.
please include any VBA that may be needed.

thanks

[ItsMe]

Maybe you can just use one listbox and adjust its Rowsource using an SQL string.

Before you assign a new RowSource you could setfocus on the form. This way, you could use the listboxes AfterUpdate event. Something like
Me.Setfocus
Me.lstBox.RowSource = strSQL
Me.lstBox.Requery

[WickidWe]

thanks for the reply,
I really need to move between the boxes, because in each category i need to select a specific number of row to add to another list
that will be used with the final report will the above still work for that situation?

[ItsMe]

I mention one list box because you said that you want to be able to switch back and forth between the two listboxes. Whatever you decide to do in the end, in order to get values from a multiselect list box you will have to loop through the listbox using a Variant and store each of the records/selections in a string.

With the string you can adjust the other listboxes Rowsource

Here is some (untested) example code to get data from a multiselct listbox

dim varEmail as variant
dim strAnswer as string
 if me.lstSelection.count = 0 then
 exit sub
 else
 for each varEmail in me.lstSelection.itemselection
 strAnswer = strAnswer & me.lstSelection.column (0, varEmail) & "; "
 next varEmail
 end if

debug.print strEmail

[June7]

How are you using this list of selected values with a report? Do you want to filter report?

Review http://allenbrowne.com/ser-50.html

[WickidWe]

I Hav'nt gotten that far to sort how I am going to use for the report here is my plan hopefully.
first Listbox (listEmp) is a query that shows a list of team members who are assigned to a particular function (which is selected in a previous combobox)
who are on a particular shift (which is also selected in a previous combobox) on a particular roster (which is also selected in a previous combobox).
the listEmp listbox shows all the info exactly as i need it.

I now need to select a required number of team members who are are assigned to each function (not all are needed)
and move them to another box called "listAllocated". once I can populate this list I will attempt to make a report which will separate the function.
each function will have the assigned team members below.

does this make sense

[June7]

Build report using Grouping and Sorting features. Create a group based on function.

Then you need code to populate the second listbox. Two ways to construct list for the second combobox: query with filter criteria or value list.

Then more code like shown in the referenced link to build filter criteria for report.

[WickidWe]

Sorry I am very confused, I am Kind of thrown in the deep end and expected to swim.
I will try and explain again exactly what I am looking for.

It is the same as I stated above, below is the query I am currently using in the record set for "listEmp"

SELECT qryAvailable.*, qryPickCount.pick AS Pick, qryFctnCount.Fctn AS Fctn, qryGrpCount.[FGrp] AS FGrp
FROM ((qryAvailable LEFT JOIN qryPickCount ON qryAvailable.Name = qryPickCount.name) LEFT JOIN qryGrpCount ON qryAvailable.Name = qryGrpCount.Name) LEFT JOIN qryFctnCount ON qryAvailable.Name = qryFctnCount.name
ORDER BY qryPickCount.pick, qryAvailable.Name;

I want to be able to move the selected to another list box and remove from the Original list so it cant be used again when filtering between different functions.
once I have moved all the numbers of various functions I need to listAllocated, then I need to create a report grouping all ppl allocated to each function under a the Function.
im sure that part will have to be in another thread post. but one step at a time.

[orange]

WickedWe,

see this tutorial

[June7]

No matter what, will need VBA code that loops through the selected items of listbox, as demonstrated in Allen Brown example. Options:

1. complex code that either sets RowSource sql for each listbox or builds a ValueList in each listbox or filters query in the first and builds ValueList in the second

2. a Yes/No field in table that can be used to include/exclude records for each listbox, code would have to update the record(s) in table based on selection(s) in each listbox then requery each to refresh the lists

[WickidWe]

Would It be possible to move from a subform to a list?

[June7]

Options:

1. Yes/No field to select/deselect records. Listbox RowSource is a query that retrieves only the yes records. Code in checkbox Click event requeries the listbox.

2. Code in subform DblClick event adds an item to the listbox value list. Removing item from listbox value list is harder.

[WickidWe]

How do I create the YesNo Column in my query?
Is it best to just creat a table with the yesno column in it, or add a column to an existing table?

[June7]

I expect it goes in existing table - whatever table you want to select records from. I don't know your database so can't really be specific. I have no way of knowing if it will work at all with your db structure. Just offering ideas.

[WickidWe]

I found this little piece of code located http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Private Sub cboShift_AfterUpdate()
Me.listEmp.Requery
End Sub
Private Sub cmdCopyItem_Click()
CopySelected Me
End Sub
Public Sub CopySelected(ByRef frm As Form)
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Forms!form1!listEmp
Set ctlDest = Forms!form1!listAllocated

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing
End Sub


it is part the way of what I Need
here is a screenshot of the two boxes.


As Pictured only the data from Column 1(Hidden) is showing.
and instead of being stored in rows it is using columns.
Also i need the items I moved to the second box to stay there
and be removed from the first.
Any Ideas?