Sandra Smith
asked on
Move data from one list to another and back again
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(Canc el 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
Sandra
Private Sub lstEmployees_DblClick(Canc
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
"ORDER BY tblEmployees.EmpLastName "
Else 'don't show any
strSelected = ""
End If
Me.lstAssignedTo.RowSource
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
"ORDER BY tblEmployees.EmpLastName "
Else
strNotSelected = "SELECT tblEmployees.EmpUserID, [EmpLastName] & ', ' & [EmpFirstName] AS EmpName " & _
"FROM tblEmployees WHERE tblEmployees.EmpTerminated
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Sandra
ASKER
It looks perfect. However, what libraries does it need as I need to create some references for it to work.
Sadnra
Sadnra
ASKER
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
Sandra
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
Perhaps the app you are using this in does...?
In any event, I am glad it is working for you.
Enjoy the weekend...
;-)
Jeff
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