Passing Listbox object in vba Access

Question

I'm trying to pass a listbox to another sub to populate it, this is designed to allow multiple listboxes to use the same code by passing the name of the listbox, and the name of the table (which checks whether the input is valid) to the sub. At the moment the me.Autoclavelist (which is the name of the listbox on this particular form) in the below code displays 'null' when you hover over it.

1) What is the listbox object actually expecting as input?

2) Subs 1 and 2 appear to work even with the null being sent - however similar code using a .removeitem doesn't (3 & 4) why is this?

Thanks!

Edit: I found the issue here - I had locked the listboxes on the forms. This meant that it wasn't possible to select any items in them, and the value was therefore always null, so the second two subs failed.

Sub 1:

Private Sub cmdAutoClaveAddItem_Click()

On Error GoTo Errorhandler

Call AddtoList(Me.AutoClaveList, "[Autoclave Process]")

If Me.AutoClaveList.ListCount <> 0 Then
    Me.cmdRunAutoclave.Enabled = True
    Me.CmdRemoveItem.Enabled = True
End If


SubExit:
    Exit Sub

Errorhandler:
    MsgBox Error$
    Resume SubExit

End Sub

Sub 2:

Private Sub AddtoList(ListName As Listbox, FormName As String)

On Error GoTo Errorhandler

Dim StrLabel_Id As String
Dim l As Long

ListName.RowSourceType = "Value List"

StrLabel_Id = InputBox("Scan Tray Label", "Scan")

If StrLabel_Id = "" Then
    GoTo SubExit
Else
    If Not IsNull(DLookup("[Tracking_Label_ID]", "Label_Production", "[Tracking_Label_ID]='" & StrLabel_Id & "'")) Then
        If IsNull(DLookup("[Tracking_Label_ID]", FormName, "[Tracking_Label_ID]='" & StrLabel_Id & "'")) Then
            l = 0
            For l = 0 To (ListName.ListCount - 1) Step 1
                If ListName.ItemData(l) = StrLabel_Id Then
                    Call MsgBox("Label is already in batch!", , "Error")
                    GoTo SubExit
                End If
            Next
            ListName.AddItem StrLabel_Id
        Else
            Call MsgBox("Label has already been processed", , "Error")
        End If
    Else
        Call MsgBox("Label does not exist. Make sure you create label in Label Production", , "Error")
    End If
End If


SubExit:
    Exit Sub

Errorhandler:
    MsgBox Error$
    Resume SubExit

End Sub

Sub 3:

Private Sub CmdRemoveItem_Click()

On Error GoTo Errorhandler

Call RemovelistItem(AutoClaveList)

SubExit:
    Exit Sub

Errorhandler:
    MsgBox Error$
    Resume SubExit

End Sub

Sub 4:

Private Sub RemovelistItem(ListName As Listbox)

On Error GoTo Errorhandler

Dim strRemoveItem As String

If Not IsNull(ListName.Value) Then
    strRemoveItem = ListName.Value
    ListName.RemoveItem (strRemoveItem)
Else
    GoTo SubExit
End If

SubExit:
    ListName.Requery
    Exit Sub

Errorhandler:
    MsgBox Error$
    Resume SubExit

End Sub

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2014-05-09T10:34:02

Don't worry about the Null. The object exists and is just fine. The default property of ListBox objects is Value; therefore the value of Value is what gets displayed when you mouse over Me.AutoClaveList. Its Value happens to be Null (which it is by default):

Null Indicates the item is in a null state, neither selected nor cleared.

For more info, you can have a look at ListName and its properties in the Locals window.

Of course, if you do this:

If Not IsNull(ListName.Value) Then
    'do stuff
Else
    GoTo SubExit
End If

then it "won't work" i.e. will not do anything because .Value is Null. Get rid of that condition.

Passing Listbox object in vba Access

1 Answers