Form as Form? or String?

[Ken Sheridan]

All I'm trying to do is figure which listbox is visible and then grab that for other events. What I'm struggling with is calling the above function. I've figured out I need to make it a string

By specifically naming the two possible controls your code is very restrictive. The following, on the other hand, allows you to reference any form which contains one visible list box control. The number of list box control's in the form is completely arbitrary from 1 to n, as long as only one is visible at any one time:

Public Function GetList(frm As Form) As Control

    Dim ctrl As Control
 
    For Each ctrl In frm.Controls
        If ctrl.ControlType = acListBox Then
            If ctrl.Visible Then
                Set GetList = ctrl
            End If
        End If
    Next ctrl

End Function

You can then call it by passing a reference to the form into the function, e.g. in the same form's module:

    Dim ctrl As Control
    Set ctrl = GetList(Me)

If you do need the name of the control as a string expression you can return the control object's Name property:

    Debug.Print ctrl.Name

 

[RonPaii]

I forgot vbNewLine is a 2 character sting. Requiring the same treatment as the Null eDelimiterType

Public Enum eDelimiterType
    Public Enum eDelimiterType
    NoDelimiter = 0
    DoubleQuotes = 34
    Octothorpes = 35
    SingleQuotes = 39
End Enum

Public Enum eSeperatorType
    Comma = 44
    Pipe = 124
    SemiColon = 59
    Tilde = 126
    NewLine = 1
End Enum

' ----------------------------------------------------------------
' Procedure Name: fGetLbx
' Purpose: Get array of item in a multiselect listbox
' Procedure Kind: Function
' Procedure Access: Public
' Parameter lbx (ListBox): Your listbox object (ie. Me.MyList)
' Parameter intColumn (Integer): The listbox column to return
' Parameter Seperator (eSeperatorType): character seperating the array values
' Parameter Delimiter (eDelimiterType): Delimiters for array values (ie.Double Quotes or Octothorpes)
' Return Type: Variant
' Author: Moke123
'
' **** NOTE **** Returns Null if no items selected. Use NZ() in calling code to handle nulls
'
' ----------------------------------------------------------------

Public Function fgetLBX(lbx As ListBox, _
    Optional intColumn As Integer = 0, _
    Optional Seperator As eSeperatorType = eSeperatorType.Comma, _
    Optional Delimiter As eDelimiterType = eDelimiterType.NoDelimiter) As Variant

    On Error GoTo fGetLbx_Error
    
    Dim strlist As String, varSelected As Variant, DeLimit As Variant, SepChar As String
    
    if Delimiter = eDelimiterType.NoDelimiter then
        DeLimit = Null
    else
    DeLimit = Chr(Delimiter)
    end if
              
    if Seperator = eSeperatorType.NewLine then
        SepChar = vbNewLine
    else
        SepChar = Chr(Seperator)
    end if

 

[moke123]

All I'm trying to do is figure which listbox is visible and then grab that for other events.

This begs the question of how they get hidden or not in the first place. You could set a variable at that point.

 

[MarkK]

If a form exposes two lists, one of which may be considered 'Active' based on its visibility state, then on that form I'd do...

Public Property Get ActiveList() As Access.ListBox
    If Me.List0.Visible Then
        Set ActiveList = Me.List0
    Else
        Set ActiveList = Me.List2
    End If
End Property

This form might also expose a method to allow outside consumers to toggle the ActiveList...

Public Sub ActiveListToggle()
    Me.List0.Visible = Not Me.List0.Visible ' toggle List0.Visible
    Me.List2.Visible = Not Me.List0.Visible ' invert List2.Visible re: List0
    
    ' perform other 'ActiveList' change logic here if this
    ' state change has broader implications
End Sub

Consumers that reference the form don't know or care which list is which, and they can freely do...

   frm.ActiveList.Requery
    ' or
   frm.ActiveListToggle

 

[Isaac]

I have a form that has 2 listboxes. Only 1 visible at a time. Since both share many routines I need to know which one is visible.
Hence a little public routing that checks it.

Public Function VisibleList(frm As Form)
    Dim ListView         As ListBox
       
    If frm.lstName.Visible = True Then
        Set ListView = frm.lstName
    Else: Set ListView = frm.lstTrans
    End If
End Function

My problem is... I never know how to address controls on a form
Is a form in this situation a form? or is it a string?

So in a routine I want to say:

dim lst as Listbox
Set lst = ListView me.Name
lst.requery

is lst a listbox?
or is it a string?

Basic VBA that I should know but never do!

how about just Me.ListBoxName.Requery?