Moving items from one list to another - error handling problem

[shanl]

I am using Access 2003 with SQL Server tables.
I have successfully coded two list boxes that I can move items from one box to another. I have four command buttons set up that either move all the items to the box or all the items back to the first box or it will move single items to one list box or a single item back to the other box. My problem is that I am unable to get my error handling to work when I don't select any items. It should be displaying a message to "Please Select an employee to move" but instead I get the error "Run time error 5 - Invalid procedure call or argument" . My first list box is named EmployeeList and my second list box is List2. When items are selected they work fine. Can anyone see what I'm missing. It would be in the routine called Movesingleitem.


Private Sub cmdMoveAlltoList1_Click()
MoveAllItems "List2", "EmployeeList"
End Sub
Private Sub cmdMoveAlltoList2_Click()
MoveAllItems "EmployeeList", "List2"
End Sub
Private Sub cmdMoveToList1_Click()
MoveSingleItem "List2", "EmployeeList"
End Sub
Private Sub cmdMoveToList2_Click()
MoveSingleItem "EmployeeList", "List2"
End Sub

Sub MoveSingleItem(strSourceControl As String, strTargetControl As String)
Debug.Print
Dim strItem As String
Dim intColumnCount As Integer

For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount) & ";"
Next
strItem = Left(strItem, Len(strItem) - 1)

'Check the length to make sure something is selected

If Len(strItem) > 0 Then
Me.Controls(strTargetControl).AddItem strItem
Me.Controls(strSourceControl).RemoveItem Me.Controls(strSourceControl).ListIndex
Else
MsgBox "Please Select an employee to move."
End If



End Sub
Sub MoveAllItems(strSourceControl As String, strTargetControl As String)
Dim strItem As String
Dim intColumnCount As Integer
Dim lngRowCount As Long
For lngRowCount = 0 To Me.Controls(strSourceControl).ListCount - 1
For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount, lngRowCount) & ";"
Next
strItem = Left(strItem, Len(strItem) - 1)
Me.Controls(strTargetControl).AddItem strItem
strItem = ""
Next

Me.Controls(strSourceControl).RowSource = ""
End Sub

 

[shanl]

Figured out the problem so I'll post it. The code above would work fine if you only have one field in your list box but in my case I have three fields in my list box so when I'm checking the length, VBA adds a count in for every ;.

'Check the length to make sure something is selected. You need to subtract 2 because
' it adds 2 ";" to separate the three fields. If there is something there Left(strItem) would
' equal Smith;John;5681 and Len(stritem) = 15 The 15 is the length. If nothing is there then Len(stritem) = 2

If (Len(strItem) - 2) = 0 Then
MsgBox "Please Select an employee to move."
Else
Me.Controls(strTargetControl).AddItem strItem
Me.Controls(strSourceControl).RemoveItem Me.Controls(strSourceControl).ListIndex
End If

 

[duGly]

Something I like to do is disable controls altogether rather than show a MsgBox. For example, in the event handler for your list box, you could add the following code which enables the command button only if one or more entries are selected in the respective list box:

cmdMoveSingleEntry.Enabled = Not lstEmployeeList.ListIndex=-1

Hope this helps!

[red] — Artificial intelligence is no match for natural stupidity.[/red]

 

[shanl]

Excellent idea. I was using another application that did that but I didn't know how to enable and disable. Thanks for the suggestion.

Where exactly do you put this code? I tried it where I had the error message but that didn't work. I tried it on the command button subroutine and that didn't work.

 

[duGly]

It needs to go in the click or change event handler for the list box so that it runs each time the user changes the selection.

[red] — Artificial intelligence is no match for natural stupidity.[/red]