Populating a List Box with the TempVars Collection

This article is part of the series, A Safer Way to Use TempVars.

The TempVars collection in Microsoft Access is an intriguing alternative to global variables.

One drawback of going that route, though, is that it can be difficult to gain visibility into the status of the TempVars collection:

• Which TempVars have been defined?
• What are their values?
• What are their types?  (i.e., what underlying type of Variant are they?)

One quick way to display all that information is to use an Access List Box control.

Sample Usage

Here's what a sample list box looks like:

Here's the corresponding contents of the TempVars object as displayed in the VBA IDE Watch window:

Step-by-Step

Implementing the concept is dead simple.

1. Open a new form in Design View
2. Place a List Box control anywhere on the form
3. [OPTIONAL] Adjust a few of the properties of the List Box:
   Column Heads: Yes (if you want to show the header row)
   Horizontal Anchor: Both (to grow the list box horizontally)
   Vertical Anchor: Both (to grow the list box vertically)
   Column Widths: 2;1;4 (adjust to fit your TempVars data)
4. Set the form's On Load property to: =PopulateListBoxWithTempVars([List0])
    (where List0 is the name of the list box control)

That's it!

You may also want to add a button with a caption of [Refresh] to the form that also calls the PopulateListBoxWithTempVars function in its Click event so that you can refresh the contents of the list box as you make changes to the TempVars collection.

The Code

The code uses one of my favorite string-building functions, Conc(), which I've included as a Private Function for easy copy-and-pasting.

' ----------------------------------------------------------------
' Procedure : PopulateListBoxWithTempVars
' DateTime  : 9/27/2022 17:30
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/populatelistboxwithtempvars/
' Purpose   : Populates a list box control with the current contents
'               of the TempVars collection.
' Notes     - To show the column headers, set the ListBox control's
'               ColumnHeads property to Yes in Form Design view.
'           - Defined as a Function even though there's no return value 
'               to allow calling from the form's property sheet 
'               (for compatibility with lightweight forms).
' Usage(VBA): PopulateListBoxWithTempVars Me.List0
' Form Open : =PopulateListBoxWithTempVars([List0])
' ----------------------------------------------------------------
Public Function PopulateListBoxWithTempVars(lbCtl As Access.ListBox)
    
    'Set required list box properties
    lbCtl.ColumnCount = 3
    lbCtl.RowSourceType = "Value List"

    
    'Build the RowSource for the list box
    Dim s As String
    If lbCtl.ColumnHeads Then s = "Name;Value;Type"
    
    Dim Var As TempVar
    For Each Var In TempVars
        s = Conc(s, Var.Name, ";")
        s = Conc(s, Nz(Var.Value, "{NULL}"), ";")
        s = Conc(s, TypeName(Var.Value), ";")
    Next Var
    lbCtl.RowSource = s
End Function

'---------------------------------------------------------------------------------------
' Procedure : Conc
' Author    : Mike Wolfe
' Date      : 1/23/2009 - 4/1/2015
' Source    : https://nolongerset.com/come-together/
' Purpose   : Concatenates two strings
' Notes     : Eliminates the need to strip off the leading/trailing delimiter when
'               building a string list
' 4/17/09   - If StartText is filled, but nextval is empty, then StartText is returned unchanged.
' 5/ 1/09   - Changed return type of conc from Variant to String.
' 4/ 1/15   - Allow passing Nulls as StartText.
'>>> Conc("1, 2, 3", "4")
' 1, 2, 3, 4
'>>> Conc("This", "that", " and ")
' This and that
'>>> Conc("Five", Null, " and ")
' Five
'>>> Conc(Null, "Dime", " and ")
' Dime
'>>> "#" & Conc(Null, Null) & "#"
' ##
'---------------------------------------------------------------------------------------
'
Private Function Conc(StartText As Variant, NextVal As Variant, _
              Optional Delimiter As String = ", ") As String
    If Len(Nz(StartText)) = 0 Then
        Conc = Nz(NextVal)
    ElseIf Len(Nz(NextVal)) = 0 Then
        Conc = StartText
    Else
        Conc = StartText & Delimiter & NextVal
    End If
End Function