The empty (null) listbox selection

[1jet]

Hi all,

Basically my listbox lists the results of my query.
The list has 3 columns, 1. [Employee ID] 2. [ABN] 3. [Business Name]
Column 1 is hidden, and listbox is bound to column 2.

If the user makes a selection from that listbox, and clicks delete, things will delete as planned.

But there will be times when a user does not select from the list and click delete anyway.
The first two IF conditions of my code dont pick up a non (???) selection and just tells me that ZERO rows will be deleted from these two tables.
How can I fix these so that "Please make a selection" is displayed?

Set lstBusinessDetails = Forms("frmEditEmployee").Controls("lstBusinessDetails")

If IsNull(lstBusinessDetails.Value) Then
    MsgBox "Please make a selection."
    GoTo function_end
ElseIf lstBusinessDetails.Value = "" Then
    MsgBox "Please make a selection."
    GoTo function_end
Else
    selection = lstBusinessDetails.Value
    strSQL_delete = " DELETE * " & _
                    " FROM tblEmployeeBusiness " & _
                    " WHERE tblEmployeeBusiness.[ABN] = '" & selection & "' ; "
    strSQL_delete2 = " DELETE * " & _
                     " FROM tblBusiness " & _
                     " WHERE tblBusiness.[ABN] = '" & selection & "' ; "
    DoCmd.RunSQL strSQL_delete
    DoCmd.RunSQL strSQL_delete2
    
    vbaBusinessRequery
    
End If

 

[Steve R.]

But there will be times when a user does not select from the list and click delete anyway.

I am not sure that I fully understand your question. One technique that I have used is to have a default value of "No Selection" for every list box. That way if the user makes no selection, the program knows it.

Another approach, an perhaps the correct approach, is to use the selected property. Below is a code snippet that uses the selected property to determine if a record has been deleted. In your case, if the user has not made a selection, then selected would be false in all cases and you could send the user a "Please ..." message.

   RecordCount01 = Me.List7.ListCount
    Do Until i = Me.List7.ListCount
        i = i + 1
        If Me.List7.Selected(i) Then j = i
        Loop
    If j = 0 Then 'No Selected Record Found, which means a record has been deleted. Selects first record.
        [List7].Selected(1) = True
        End If

 

[1jet]

Surely there must be an easier way to code whether a listbox item is selected or not?

(I have used your ListCount suggestion, but only to display a message of "There's nothing to delete" if the value is 0)

 

[Steve R.]

Surely there must be an easier way to code whether a listbox item is selected or not?

If there is an easier way, then one of the real guru's will need to weigh in on this. Good luck.

 

[1jet]

well ive just read up on the help file on listbox.Selected(x) and it seems that x is a mandatory parameter. ill probably have to incorporate your loop suggestion as i dont think the easier way exists...
cheers stevo

 

[boblarson]

well ive just read up on the help file on listbox.Selected(x) and it seems that x is a mandatory parameter. ill probably have to incorporate your loop suggestion as i dont think the easier way exists...
cheers stevo

Is this a MULTI-Select listbox or single select?

 

[1jet]

Is this a MULTI-Select listbox or single select?

hi bob, ive set the listbox property as a single select (ie property set to none)

 

[pbaldy]

I would simply test with

If Me.lstBusinessDetails.ItemsSelected.Count = 0 Then
  'nothing selected

 

[boblarson]

As far as I know, if it is a single select then you use the listbox's VALUE property (referring to the bound column). If you try to use the ItemsSelected property with a single select listbox you will generate an error.

If IsNull(Me.lstBusinessDetails) Then
'nothing selected

 

[pbaldy]

As far as I know, if it is a single select then you use the listbox's VALUE property (referring to the bound column). If you try to use the ItemsSelected property with a single select listbox you will generate an error.

That has not been my experience. One reason I use that is it works either way. Did you test?

 

[boblarson]

I guess you're right Paul. The count works fine and this will return the same as the bound value:
Me.lst1.ItemData(Me.lst1.ItemsSelected(0))

 

[Steve R.]

I would simply test with

If Me.lstBusinessDetails.ItemsSelected.Count = 0 Then
  'nothing selected

Thanks. A much more efficient way.

 

[imx]

If Me.lstBusinessDetails.ListIndex + 1 = 0 Then
MsgBox "please select a row"
Exit Sub
End If