Form as Form? or String? (1 Viewer)

[ClaraBarton]

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!

 

[cheekybuddha]

If this Requery code is for two listboxes in the same form, then why isn't the code in that form's module?

If you put this code in the form's module:

Sub RequeryLstBx()

  Const lst1 As String = "lstName", _
        lst2 As String = "lstTrans"
  Dim lst As Variant, lsts As Variant
 
  lsts = Array(lst1, lst2)
  For Each lst In lsts
    If Me(lst).Visible Then Me(lst).Requery
    Exit For
  Next

End Sub

Then, when you need to requery the visible listbox just use:

  RequeryLstBx

 

[theDBguy]

Just curious, do you get an error if you requery a hidden (invisible) listbox? Or, is this a problem of speed?

ie. Is requerying two listboxes too slow compared to requerying only one of them?

 

[moke123]

is lst a listbox?
or is it a string?

I would say it's an object.

when I need to requery all the list boxes on a form I use something like this and pass in the form reference

Public Sub ReqLbx(frm As Form)

Dim ctl As Control

    For Each ctl In frm.Controls
        If ctl.ControlType = acListBox Then
            ctl.Requery
        End If
    Next

End Sub

 

[ClaraBarton]

The reason I need to specify a specific listbox is because I requery it from another form, I do multiple selections from them, I move items in it.
I thought this was the "smart" code, to make one function work for multiple places.

Function GetList()
On Error GoTo Err_GetList
    Dim i           As Variant
    Dim strIN       As Variant
    Dim strWhere    As String
    Dim lst         As ListBox
    
lst = ListView me.form
'    If Me.lstName.Visible = True Then
'        Set lst = Me.lstName
'    Else: Set lst = Me.lstTrans
'    End If
    
    strIN = ""
        For Each i In lst.ItemsSelected
            strIN = strIN & lst.ItemData(i) & ","
        Next i
        strIN = Left(strIN, Len(strIN) - 1) ' remove trailing comma.
        
GetList = "TransactionID IN(" & strIN & ")"
        
Exit_GetList:
    Exit Function

 

[moke123]

For multi-select listboxes I use the following code. The reason for the enums is that they are also used in other procedures.

you could use it as

GetList = "TransactionID IN(" & fGetLbx("your listbox object" & ")"

Public Enum eDelimiterType
    NoDelimiter = 0
    DoubleQuotes = 1
    Octothorpes = 2
    SingleQuotes = 3
End Enum

Public Enum eSeperatorType
    Comma = 0
    Pipe = 1
    SemiColon = 2
    Tilde = 3
    NewLine = 4
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 = 0, _
    Optional Delimiter As eDelimiterType = 0) As Variant

    On Error GoTo fGetLbx_Error
    
    Dim strlist As String, varSelected As Variant, DeLimit As Variant, SepChar As String
    
    Select Case Delimiter
        Case 0
            DeLimit = Null
        Case 1
            DeLimit = Chr(34) 'Quotes
        Case 2
            DeLimit = Chr(35) 'Octothorpes
        Case 3
            DeLimit = Chr(39) 'SingleQuotes
    End Select
                
    Select Case Seperator
        Case 0
            SepChar = Chr(44)   'comma
        Case 1
            SepChar = Chr(124)  'pipe
        Case 2
            SepChar = Chr(59)   'semicolon
        Case 3
            SepChar = Chr(126)  'tilde
        Case 4
            SepChar = vbNewLine 'newline
                
    End Select
 
    If lbx.ItemsSelected.Count > 0 Then
  
        For Each varSelected In lbx.ItemsSelected

            If lbx.Column(intColumn, (varSelected)) <> "" Then
            
                If strlist <> "" Then
                    strlist = strlist & SepChar & DeLimit & lbx.Column(intColumn, (varSelected)) & DeLimit
                Else
                    strlist = DeLimit & lbx.Column(intColumn, (varSelected)) & DeLimit
                End If

            End If

        Next varSelected
        
        fgetLBX = strlist
        
    Else
    
        fgetLBX = Null
        
    End If
    
    On Error GoTo 0
    Exit Function

fGetLbx_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fGetLbx, line " & Erl & "."

End Function

 

[RonPaii]

You can directly use your enums, making the code more readable without comment.

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
   
    Select Case Delimiter
        Case eDelimiterType.NoDelimiter
            DeLimit = Null
        Case eDelimiterType.DoubleQuotes
            DeLimit = Chr(34)
        Case eDelimiterType.Octothorpes
            DeLimit = Chr(35)
        Case eDelimiterType.SingleQuotes
            DeLimit = Chr(39)
    End Select
               
    Select Case Seperator
        Case eSeperatorType.Comma
            SepChar = Chr(44)  
        Case eSeperatorType.Pipe
            SepChar = Chr(124) 
        Case eSeperatorType.SemiColon
            SepChar = Chr(59)  
        Case eSeperatorType.Tilde
            SepChar = Chr(126) 
        CaseeSeperatorType.NewLine
            SepChar = vbNewLine
               
    End Select

 

[Gasman]

I was about to ask that.
However I was just thinking of the names by themselves, no eDelimiterType or eSeperatorType prefixes.

 

[RonPaii]

Or by changing the Enum you can make it simpler.

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 = vbNewLine
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
                
    SepChar = Chr(Seperator)

 

[Ken Sheridan]

The reason I need to specify a specific listbox is because I requery it from another form, I do multiple selections from them, I move items in it.
I thought this was the "smart" code, to make one function work for multiple places.

If you standardise the RowSource properties of the list boxes in the form or forms containing the list boxes you want to address so that they start with SELECT DISTINCT, and the control's BoundColumn property is always 1, then you can get the correct column name for whichever list box is visible by calling the Split function. Pass a reference to the form in question as the GetList function's argument, which would then be:

Public Function GetList(frm As Form) As String

    Dim strList As String
    Dim varItem As Variant
    Dim lst As ListBox
    Dim ctrl As Control
    Dim strColumn As String
  
    For Each ctrl In frm.Controls
        If ctrl.ControlType = acListBox Then
            If ctrl.Visible Then
                Set lst = ctrl
                strColumn = Split(ctrl.RowSource)(2)
                ' remove trailing comma if necessary
                If Right(strColumn, 1) = "," Then
                    strColumn = Left(strColumn, Len(strColumn) - 1)
                End If
            End If
        End If
    Next ctrl

    With lst
        If .ItemsSelected.Count > 0 Then
            For Each varItem In .ItemsSelected
                strList = strList & "," & .ItemData(varItem)
            Next varItem

           ' remove leading comma
            strList = Mid(strList, 2)
            GetList = strColumn & " IN(" & strList & ")"
        End If
    End With
  
End Function

Testing this in the immediate window with one of my own forms containing two list boxes, one of which I temporarily made invisible for the purpose of the test, the following is the result I got:

? GetList(Forms("frmReportDialogue"))
Contacts.ContactID IN(3,9,6)

PS: I've assumed in the above that the control's bound column will be of a number data type.

 

[ClaraBarton]

way overcomplicating my question. 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

Public Function VisibleList(frm As Form) a string
        
    If frm.lstName.Visible = True Then
        VisibleList = frm.lstName
    Else: VisibleList = frm.lstTrans
    End If
End Function

Now my question is... how do I get this info?

lst = VisibleList me.Name

Nope

lst = VisibleList (me.Name)

Nope

 

[arnelgp]

you can simplify it:

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

on your code:

Dim lst as listbox
set lst = VisibleList()

 

[ClaraBarton]

Thank you so much!!!

 

[DaveMD]

Just for future additional reference for the OP about how objects are handled in VBA:

1. Setting a variable of an object type (such as ListBox, Combobox, Textbox) requires the Set keyword to be used:

Dim lst as ListBox
Set lst = frm.lstTrans

2. Getting the selected value from the object for the Bound column can be done like this (assuming it is a single-selection listbox):

Dim strSelectedValue As String
strSelectedValue = frm.lstTrans

Alternatively, you can also be explicit by specifying the .Value attribute which will give the same result in this context as the above. Note that in effect, the above code block is doing the same thing as the below code block but only because VBA knows that you are assigning the listbox to a string variable and not an object variable (which would require the "Set" keyword) so it implicitly pulls the .Value attribute for you.

Dim strSelectedValue As String
strSelectedValue = frm.lstTrans.Value

I prefer myself to explicitly specify .Value to make the intent of the code clear, but it can be fine either way.

 

[ClaraBarton]

@arnelgp stupid question...

set lst = VisibleList(me.name)
set lst = VisibleList me.name
set lst = VisibleList (popUsedBy)
set lst = VisibleList ("popUsedBy")

Seriously! nothing compiles

 

[DaveMD]

Assuming you are calling @arnelgp 's helper function from the form itself:

Set lst = VisibleList(Me.Form)

 

[arnelgp]

or you can use:

Set lst = Visiblelist(Me)

 

[KitaYama]

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

That won't work. In VBA, Enum members must be numeric constants because they are compiled as Long integers.
NewLine = vbNewLine will throw an error.

It can be
NewLine = 10 ' Line Feed (LF)
OR
NewLine = 13 'Carriage Return (CR)

But I'm not sure if that works. Because I always use vbCrLF.

 

[moke123]

That won't work. In VBA, Enum members must be numeric constants because they are compiled as Long integers.
NewLine = vbNewLine will throw an error.

I encountered errors too. vbNewLine and vbCrLf both resolve to 13 but chr(13) wasn't working correctly, neither was chr(0) for null.
Easy work arounds with only 2 short lines of code.

 

[KitaYama]

I encountered errors too. vbNewLine and vbCrLf both resolve to 13 but chr(13) wasn't working correctly, neither was chr(0) for null.
Easy work arounds with only 2 short lines of code.

I simply mentioned it in case anyone in future visits this page.
I assume you have assigned a negative value to it, because no character has a negative chr. Then in the function, using an IF clause, the value is set to vbCrLf or vbNewline if the passed value is less than 0.

Later, I will check your file.