Explanation of Code

[alaric]

works but dunno how... can someone explain it to me

I found following code on the forum
It works great and I can change it for my needs BUT
I cant figure out how and why its working
Can someone lecture me?

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
Dim stDocName As String

' Build a list of the selections.
Set ctl = Me![LeaList]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm


thanks in advance
Al

 

[Mile-O]

Two things before I start:

1. The Code Libray forum is for code samples and not, as the forum states, for questions. I moved your thread here to Modules & VBA.
2. Please use the code tags when posting code. It can be hard to read code when it is not indented.

I thought I'd tidy the code first.

    ' Create constant for putting a " within a string
    ' As two "" within a string is viewed as one "
    Const QUOTE As String = """"
    
    ' We will refer to the listbox more than once so, rather than
    ' connect to it multiple times we will create a control that
    ' will refer to it
    Dim ctl As Control
    
    ' We want to cycle through the selected items of a listbox so
    ' we create a Variant variable type.
    Dim varItem As Variant
    
    ' We want to create a string of criteria so we create one
    ' string to build our criteria within
    Dim strCriteria As String
    
    ' We make the Control variable represent our listbox
    Set ctl = Me.LeaList
    
    ' We start to loop through our selected items in the listbox
    For Each varItem In ctl.ItemsSelected
        ' The first time we loop through our selected items the
        ' String variable holding the criteria will not have any
        ' value so we can check if this is the first time that the
        ' loop has been executed
        If strCriteria = vbNullString Then
            ' We assign the first criteria; not we use the QUOTE constant
            ' to delimit the string
            ' This means our string will look like this:
            ' """"Data""""
            ' When you consider that the " on each side of each QUOTE are
            ' the opening and closing of strings you are actually left with:
            ' ""Data""
            ' But, as two "" within a string are used to represent one, we
            ' actually have:
            ' "Data"
            ' The ItemData property of the listbox picks the value we want
            ' based on where we are in the ItemsSelected array
            strCriteria = QUOTE & ctl.ItemData(varItem) & QUOTE
        Else
            ' Similar to the previous condition only we add a comma to
            ' separate the selected items since the String for the criteria
            ' will no longer be a null length
            strCriteria = strCriteria & "," & QUOTE & ctl.ItemData(varItem) & QUOTE
        End If
    Next
    
    ' As the Control is an object variable we have to release it from memory
    ' otherwise it will continue to occupy space even after the subroutine has
    ' ended
    Set ctl = Nothing

Personally though, I'd prefer to create this as a function which returns the criteria:

Public Function MultipleCriteria(ByVal lst As ListBox) As String
    
    On Error GoTo Err_MultipleCriteria
    
    Const QUOTE As String = """"
    
    Dim varItem As Variant
    Dim strCriteria As String
    
    For Each varItem In lst.ItemsSelected
        If strCriteria = vbNullString Then
            strCriteria = QUOTE & lst.ItemData(varItem) & QUOTE
        Else
            strCriteria = strCriteria & "," & QUOTE & lst.ItemData(varItem) & QUOTE
        End If
    Next
    
    MultipleCriteria = strCriteria
    
Exit_MultipleCriteria:
    strCriteria = vbNullString
    Exit Function

Err_MultipleCriteria:
    MultipleCriteria = vbNullString
    Resume Exit_MultipleCriteria
    
End Function

To get the criteria with this function you would pass the listbox to the function.

ie.

If MultipleCriteria(Me.LeaList) = vbNullString Then
    MsgBox "Unable to create query."
Else
    MsgBox "Able to create query
End If