VBA reference to listbox form control by variable name

Question

I am stuck on the syntax of referring to a listbox control on a form in VBA. I am trying to write a subroutine to build a SQL string based on the selections in a multiselect listbox. Currently, I have the following code:

Private Sub lstStudies_AfterUpdate()
    Call buildMultiSQL("lstStudies","field1")
End Sub

Private Sub buildMultiSQL(lstName As String, fldName As String)
     Dim lstBox As Object
     lstBox = Me.Controls(lstName)
     …
End Sub

I get the following error: "Run-time error '91': Object Block or With block variable not set" which highlights the line

lstBox = Me.Controls(lstName)

When I change the code to:

Private Sub buildMultiSQL(lstName As String, fldName As String)
     Dim lstBox As Object
     lstBox = Forms(frmMultiselect).Controls(lstName)
     …
End Sub

I get a different runtime error on the same line: "Run-time error '2465': [My Database Name] can't find the field 'lstStudies' referred to in your expression."

How do I refer to a list box that is a form control using a variable name? I have already double-checked that the listbox is really named lstStudies, and there are no other listboxes or controls with that name on the form.

Answer 1

When assigning object variables you need to use Set

Set lstBox = Me.Controls(lstName)