Move Selected between two listboxes

[wickidwe]

Goal:

Move Selected Items Between Listbox1 (listEmp) and listbox2 (listAllocated). the items moved from listEmp must be removed from listEmp when moved to list allocated and vice versa.


Overview:

listEmp is my first listbox and currently has the query in it's rowsource
SELECT e.* FROM qryEmp AS e;
it has 7 columns but only 6 are showing (Column 1 is hidden)
and it is currently bound to 1

listAllocated has only 4 columns (can be changed) i only need the first 4 columns from the first list.
currently it has no row source and the row source type is set to Value
it is also bound to 1

I did find some code that does not do what I need but is closer than anything that I have found so far. It seems like it is copying the Selected Items from Column 1 and pasting it in the second listbox.
but it is pasting them all in a row and not in individual columns
below is the code. I will post a screen shot in 2 posts time.

Private Sub cmdCopyItem_Click()
  CopySelected Me
End Sub
Public Sub CopySelected(ByRef frm As Form)
Debug.Print
    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

I have posted on another forum with no luck yet (maybe My post doesnt make sense)

 

[JHB]

Try the attached database - open the only form in it.

 

[wickidwe]

Thank you Very much!

Using the Basic Idea of your form I think I can do what I need.
Is there a way to Update the table with multiple selections? or does it need to be done one at a time?

 

[JHB]

I've updated the database to multiple selections.

 

[wickidwe]

That Is Exactly What I needed
thanks so much!

 

[wickidwe]

One more thing this works great. How can I add another update to another table?

Add to query below: tblFunctions SET FctnSelected = True

dbs.Execute ("UPDATE tblEmpDetails SET IsVisible = False " _
& "WHERE EmpID='" & Me.listEmp.Column(0, varItm) & "'")

 

[Galaxiom]

The weakness in the provided example is that the table requires an extra Boolean field to store the selection checkbox data. Moreover the table cannot be used simultaneously by multiple users so each user requires a separate copy of the table which must be kept synchronised with the base data.

A better technique uses fabricated ADO recordsets as the Recordset property of the listboxes. The recordsets have the boolean field added during its generation and the data is copied into them from the source table. The boolean field only exists in memory and each user's recordsets are completely independent.

Records are "moved" from one list to the other by filtering the recordsets. They can also be moved up and down the list by sorting the recordsets.

An added advantage is that once the recordset is generated the database engine is not queried again. The entire processing is done in RAM. It is very fast and efficient.

Data is not limited to records in a table. It can be generated using virtually anything. For example it can be populated with lists of tables, forms, reports, fields, properties, databases or files.

 

[wickidwe]

Do you have an Example of how I Could Do This?