Move Selected between two listboxes

W

wickidwe

Registered User.
Local time
Today, 08:21
Joined
Dec 12, 2013
Messages
16
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.

Code: 
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)
 
Try the attached database - open the only form in it.
 

Attachments

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?
 
I've updated the database to multiple selections.
 

Attachments

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) & "'")
 
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.
 
You must log in or register to reply here.

Similar threads

T
ListBox Doesn't Show Selected Values
Replies
10
Views
457
MajP
MajP
C
Element not found
Replies
13
Views
802
ClaraBarton
C
M
Refresh Listbox Query
Replies
6
Views
978
monika_V
M
zelarra821
List all song features
2
Replies
22
Views
2,000
zelarra821
zelarra821
C
Solved Position form to added record
Replies
8
Views
490
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom