Message if no selection made in list box 1

[THWatson]

Using Access 2000

Below is code behind a command button on a form in which there is a list box from which the user will select records to be archived.

The code runs fine except for the lines I have marked in blue. These lines produce Error 5 (invalid procedure call or argument). They are meant to give a message to the user if no names have been selected from the list box.

Can anyone suggest how to fix this?

Private Sub cmdArchive_Click()
On Error GoTo cmdArchive_Click_Error
 Dim frm As Form, ctl As Control
    Dim SQL1 As String
    Dim SQL2 As String
    Dim SQL3 As String
    Dim SQL4 As String
    Dim strSQL As String
        Dim varItm As Variant
        Dim strList As String

strList = ""
    Set frm = Forms!frmArchiveProcess
    Set ctl = frm!lstRemoved
    For Each varItm In ctl.ItemsSelected
       strList = strList & ctl.ItemData(varItm) & ", "
    
    Next varItm
    strList = Left(strList, Len(strList) - 2)

[COLOR=blue]If Len(strList) < 1 Then
Call MsgBox("Please select name(s) to be archived.", vbExclamation Or vbDefaultButton1, "No name(s) selected")
Exit Sub
End If[/color]

Select Case MsgBox("Are you sure you want to archive these records?" _
                   & vbCrLf & "         This process cannot be undone?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Run Archive Process?")
    Case vbYes
    Call MsgBox("Thank you! The archive will proceed.", vbExclamation Or vbDefaultButton1, "Archive Continuing")
        GoTo ArchiveProcess
    Case vbNo
    Call MsgBox("Thank you! The archive will be cancelled.", vbExclamation Or vbDefaultButton1, "Archive cancelled")
        Exit Sub
End Select
    
ArchiveProcess:
Me.txtCompleted = ""

SQL1 = "INSERT INTO tblChildrenArchive " _
& "SELECT tblChildren.* " _
& "FROM tblTrinity INNER JOIN tblChildren ON tblTrinity.UniqueID = tblChildren.MemberID " _
& "WHERE tblTrinity.UniqueID IN (" & strList & ");"
    strSQL = SQL1
    DoCmd.RunSQL strSQL

SQL2 = "INSERT INTO tblEnvelopeNumbersArchive " _
& "SELECT tblEnvelopeNumbers.* " _
& "FROM tblTrinity INNER JOIN tblEnvelopeNumbers ON tblTrinity.UniqueID = tblEnvelopeNumbers.UniqueID " _
& "WHERE tblTrinity.UniqueID IN (" & strList & ");"
    strSQL = SQL2
    DoCmd.RunSQL strSQL

SQL3 = "DELETE tblChildren.*, tblTrinity.LastName " _
& "FROM tblTrinity INNER JOIN tblChildren ON tblTrinity.UniqueID = tblChildren.MemberID " _
& "WHERE tblTrinity.UniqueID IN (" & strList & ");"
    strSQL = SQL3
    DoCmd.RunSQL strSQL

SQL4 = "DELETE tblEnvelopeNumbers.*, tblTrinity.LastName " _
& "FROM tblTrinity INNER JOIN tblEnvelopeNumbers ON tblTrinity.UniqueID = tblEnvelopeNumbers.UniqueID " _
& "WHERE tblTrinity.UniqueID IN (" & strList & ");"
    strSQL = SQL4
    DoCmd.RunSQL SQL4

Me.txtCompleted = "Archive Completed"

   On Error GoTo 0
   Exit Sub

cmdArchive_Click_Error:
    If Err.Number = 2501 Then
    Exit Sub
    End If
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdArchive_Click of VBA Document Form_frmArchiveProcess"

End Sub

Thanks for your help.

Tom

 

[mtn244]

You could use the count property to see if zero items have been selected:

If Me.lstRemoved.ItemsSelected.Count = 0 Then

Hope that helps.

 

[THWatson]

mtn244
Know what? I had tried several things and what you suggest was one of them.

But now I tried that one again and it works. However, its position in the code has to be moved so that it is triggered before the line that says strList = ""

So the code would go like this...

Dim frm As Form, ctl As Control
    Dim SQL1 As String
    Dim SQL2 As String
    Dim SQL3 As String
    Dim SQL4 As String
    Dim strSQL As String
        Dim varItm As Variant
        Dim strList As String

If Me.lstRemoved.ItemsSelected.Count = 0 Then
Call MsgBox("Please select name(s) to be archived.", vbExclamation Or vbDefaultButton1, "No name(s) selected")
Exit Sub
End If

strList = ""
    Set frm = Forms!frmArchiveProcess
    Set ctl = frm!lstRemoved
    For Each varItm In ctl.ItemsSelected
       strList = strList & ctl.ItemData(varItm) & ", "
    
    Next varItm
    strList = Left(strList, Len(strList) - 2)

Thanks for your help!

Tom