Solved

Move data from one list to another and back again

Posted on 2012-03-16
6
305 Views
Last Modified: 2012-03-16
I have to list boxes, the user double-clicks to select an employee and this name shows up in the second list.  This works fine - HOWEVER, if the user needs to remove a name from the second list box (by double-clicking) , the name should move back to the first list box.  I have the code that moves the data from lstEmployees to lstAssignedTo, but if the user changes their mind, not sure how to do it in reverse.  The code uses a txtSelection control on the form to hold the userID's of those that have been selected, which is used in the underlying query to update the rowsource.

Sandra

Private Sub lstEmployees_DblClick(Cancel As Integer)
Dim strList As Variant
Dim ctl As Control
Dim varItem As Variant

    Set ctl = Me.lstEmployees

    strList = Me.txtSelection
   
    For Each varItem In ctl.ItemsSelected
        If IsNull(Me.txtSelection) Then
            strList = """" & ctl.ItemData(varItem) & ""","""
        Else
            strList = strList & """" & ctl.ItemData(varItem) & """" & ","""
        End If
    Next varItem
   
    Me.txtSelection = Left(strList, Len(strList) - 1)
   
    Call FctRefreshLists

End Sub
Public Function FctRefreshLists()
'The list box on the right will show only all items selected
On Error GoTo ErrorHandler
Dim strSelected As String
Dim strNotSelected As String
Dim intUserTeam As Integer
Dim strSQLNotselected As String
Dim strSelectedText As String

strSelectedText = Left(Me.txtSelection, Len(Me.txtSelection) - 1)

strSelected = ""

If Not IsNull(Me.txtSelection) And Me.txtSelection <> "" Then
    strSelected = "SELECT tblEmployees.EmpUserID, [EmpLastName] & ', ' & [EmpFirstName] AS EmpName " & _
        "FROM tblEmployees WHERE tblEmployees.EmpTerminated = 0 AND EmpUserID In(" & strSelectedText & ") " & _
        "ORDER BY tblEmployees.EmpLastName "
Else 'don't show any
    strSelected = ""
End If

Me.lstAssignedTo.RowSource = strSelected
Me.lstAssignedTo.Requery

'The list box on the LEFT will show only all items that are NOT selected
If Not IsNull(Me.txtSelection) And Me.txtSelection <> "" Then
    strNotSelected = "SELECT tblEmployees.EmpUserID, [EmpLastName] & ', ' & [EmpFirstName] AS EmpName " & _
        "FROM tblEmployees WHERE tblEmployees.EmpTerminated = 0 AND EmpUserID NOT IN(" & strSelectedText & ") " & _
        "ORDER BY tblEmployees.EmpLastName "
Else
    strNotSelected = "SELECT tblEmployees.EmpUserID, [EmpLastName] & ', ' & [EmpFirstName] AS EmpName " & _
        "FROM tblEmployees WHERE tblEmployees.EmpTerminated = 0" & _
        "ORDER BY tblEmployees.EmpLastName "
End If

Me.lstEmployees.RowSource = strNotSelected
Me.lstEmployees.Requery

Exit_ErrorHandler:
    Exit Function
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.description & vbCrLf & _
           "Function FctRefreshLists", vbOKOnly
    Resume Exit_ErrorHandler
End Function
0
Comment
Question by:ssmith94015
  • 3
  • 3
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37730872
See here perhaps:
http://support.microsoft.com/kb/132137

I have a sample that does this, I'll try to post it tonight.
But I am sure another expert will be along to help you with this directly
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37730883
0
 

Author Comment

by:ssmith94015
ID: 37730916
boag2000 Thank you.  I took a look at the article and it is a different approach, but am going to try some variations of it.  I am looking forward to your sample.

Sandra
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:ssmith94015
ID: 37730922
It looks perfect.  However, what libraries does it need as I need to create some references for it to work.

Sadnra
0
 

Author Closing Comment

by:ssmith94015
ID: 37730928
Got it to work, I just needed the script runtime.  Thank you again, this is saving me a big chunk of time.  Your layout is much better than teh approach I was going with
Sandra
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37730955
You should not have needed any special libraries to run my sample.
Perhaps the app you are using this in does...?

In any event, I am glad it is working for you.

Enjoy the weekend...
;-)

Jeff
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now