Error Handling on Listbox procedure

[JimLes]

Can anyone tell me why this error handling won't work? I am trying to error the code out if nothing is selected in my listbox (AcctList). It gives me an invalid call procedure when nothing is selected and the error number is 5. Everything works perfect if I select an item.

Private Sub Command757_Click()
    On Error GoTo Err_Command757_Click
    
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
  
    
    Set MyDB = CurrentDb()

     strSQL = "SELECT * FROM qrySendAcctEmployees"
        
    'Build the IN string by looping through the listbox
    For i = 0 To AcctList.ListCount - 1
        If AcctList.Selected(i) Then
            If AcctList.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & AcctList.Column(0, i) & "',"
        End If
    Next i
    
    On Error GoTo Err_Command757_Click
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [EID] in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")"

    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If

    MyDB.QueryDefs.Delete "qrySendAcctEmployees2"
    Set qdef = MyDB.CreateQueryDef("qrySendAcctEmployees2", strSQL)

    'Open the query, built using the IN clause to set the criteria
    'DoCmd.OpenQuery "qrySendAcctEmployees2", acViewNormal

    'Clear listbox selection after running query
    For Each varItem In Me.AcctList.ItemsSelected
        Me.AcctList.Selected(varItem) = False
    Next varItem
    
    
    DoCmd.SendObject acQuery, "qrySendAcctEmployees2", "MicrosoftExcelBiff8(*.xls)", "", "", "", "Termed Employee(s) to be Processed", "Please process the attached employees for termination", True, ""

   
Exit_Command757_Click:
    Exit Sub

Err_Command757_Click:
    If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
        Resume Exit_Command757_Click

    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
         'MsgBox Error$
        Resume Exit_Command757_Click
    End If
End Sub

 

[davejam]

Not sure if i've picked up your issue completely, a simple fix would be to jump out of the query if nothing selected.

after your code

For i = 0 To AcctList.ListCount - 1
 If AcctList.Selected(i) Then
  If AcctList.Column(0, i) = "All" Then
   flgSelectAll = True
  End If
  strIN = strIN & "'" & AcctList.Column(0, i) & "',"         
 End If
Next i

try the following

if not flgSelectAll AND strIn & "" = "" then
 msgbox "nothing has been selected"
 exit sub
end if

daveJam

it works on my machine, so technically i win!

 

[PHV]

Replace this:
strWhere = " WHERE [EID] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
with something like this:
If Len(strIN) > 0 Then
strWhere = " WHERE [EID] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
Else
strWhere = " WHERE 1=0 "
end If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

never use error trapping for program flow
....
Dim varItem As Variant

If Me.AcctList.ItemsSelected.Count = 0 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
exit sub
end if

Set MyDB = CurrentDb()
...

 

[JimLes]

Thanks for the tips! Brilliant!!

 

[TheAceMan1]

How are ya JimLes . . .

I intended to post this yesterday but didn't have the time. Just want you to take note of the [blue]logic flow[/blue] ... espcially that of changing the SQL of the querdef:

[blue]On Error GoTo GotErr
   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim Lbx As ListBox, itm, strIN As String, flg As Boolean
   
   Set db = CurrentDb
   Set Lbx = Me!AcctList
   SQL = "SELECT * FROM qrySendAcctEmployees"
   
   If Lbx.ItemsSelected.Count <> 0 Then
      For Each itm In Lbx.ItemsSelected
         If Lbx.Column(1, itm) = "All" Then
            [purple][b]flg = True
            Exit For[/b][/purple]
         ElseIf strIN <> "" Then
            strIN = strIN & ", '" & AcctList.Column(0, itm) & "'"
         Else
            strIN = "'" & AcctList.Column(0, itm) & "'"
         End If
      Next
   End If
          
   [purple][b]If flg Or strIN = ""[/b][/purple] Then [green]'Show All![/green]
      SQL = SQL & ";"
   Else
      SQL = SQL & " WHERE [EID] IN (" & strIN & ");"
   End If
   
   [purple][b]Set qdf = db.QueryDefs("qrySendAcctEmployees")
   qdf.SQL = SQL
   Set qdf = Nothing
   DoCmd.OpenQuery "qrySendAcctEmployees", acViewNormal[/b][/purple]
   
   For Each itm In Lbx.ItemsSelected
       Me.AcctList.Selected(itm) = False
   Next
   
   DoCmd.SendObject acQuery, _
                   "qrySendAcctEmployees2", _
                   "MicrosoftExcelBiff8(*.xls)", _
                   "", _
                   "", _
                   "", _
                   "Termed Employee(s) to be Processed", _
                   "Please process the attached employees for termination", True, _
                   ""
   
SeeYa:
   Set qdf = Nothing
   setlbx = Nothing
   Set db = Nothing
   Exit Sub
   
GotErr:
   If Err.Number = 5 Then
       MsgBox "You must make a selection(s) from the list" _
              , , "Selection Required !"
       Resume SeeYa
   
   Else
       'Write out the error and exit the sub
       MsgBox Err.Description
        'MsgBox Error$
       Resume SeeYa
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]