ListFields() Part 2: An Optional Prefix

In part 1 of this series, we started simple:

In its first incarnation, the ListFields function takes a single argument–the table name–and outputs a list of all the fields in that table.

I used the routine in this simple form for several weeks until I started running into a common problem.  I would often copy the field names from the immediate window and paste them into my code when I was building SQL strings.  This worked especially well for SELECT clauses.  Sometimes, though, the query included multiple JOINed tables.  In those situations, I would need to add a table name or alias as a prefix to every field.

Going back through the list of fields and adding a prefix to each field got old fast, so I added an optional parameter to prepend each field with a table name or alias.

The Approach

In part two, we're adding an optional String parameter to the ListFields routine.  When passed, the parameter will get prepended to each field name.

Subroutine Changes

Here are the changes we are making in this second iteration of the ListFields routine:‌

1. Add argument: Optional Prefix As String = ""
2. Change the line...
   NewItem = Flds(i).Name
   ...to...
   NewItem = Conc(Prefix, Flds(i).Name, ".")

The Full Code

The code below includes required helper functions that I've written about in the past:

• Come Together (the Conc() function)

The code also requires a reference to the DAO library, which is checked by default on newer versions of Access.

The code below can be copied and pasted into a blank standard module to get a fully-working solution that you can easily integrate into your projects:

' ----------------------------------------------------------------
' Procedure : ListFields
' Date      : 12/6/2022 - 12/7/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/listfields-part2/
' Purpose   : Print a comma-delimited list of fields to the
'               immediate window to reduce cognitive load from
'               context switching between code and application windows.
' Part 2    - Add optional Prefix argument to fully qualify field names
' ----------------------------------------------------------------
Sub ListFields(TblName As String, _
               Optional Prefix As Variant = Null)
    Const MaxLineLength As Integer = 80

    Dim Db As DAO.Database
    Set Db = CurrentDb

    Dim Flds As DAO.Fields
    Set Flds = Db.TableDefs(TblName).Fields
    
    'Loop through all the fields in the table
    Dim i As Integer, FldList As String
    For i = 0 To Flds.Count - 1
        Dim NewItem As String
        NewItem = Conc(Prefix, Flds(i).Name, ".")
        
        'If the next item will exceed the max line length,
        '   then print the line and clear the FldList for the next line
        If Len(FldList) + Len(", ") + Len(NewItem) > MaxLineLength Then
            Debug.Print FldList
            FldList = vbNullString
        End If
        FldList = Conc(FldList, NewItem)
    Next i
    
    'Print the last line to the immediate window
    Debug.Print FldList & vbCrLf
End Sub


'---------------------------------------------------------------------------------------
' 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
'---------------------------------------------------------------------------------------
'
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

Sample Usage‌

‌

Further reading