Form as Form? or String?

[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)