ListFields() Part 1: Listing Table Fields in the Immediate Window

In my post on the hidden costs of context switching, I wrote the following:

To make things easier on your brain, you need to reduce the amount of context switching you are asking it to do.

One way to do that is to avoid saved "action" (INSERT/UPDATE/DELETE) query definitions.  Instead, you can write and execute the query SQL definitions in code.  I wrote a class module to simplify the SQL generation and execution, but you could also just write the raw SQL strings and execute them directly.

By doing it this way, you can follow the entire logical process in one place with no context switching required.

When I first started doing this, I still found myself jumping back and forth between the IDE and the application windows.  I was writing code in the IDE, but I had to refer back to the table definitions to verify field names, types, which fields were required, etc.

In short, I needed a way to easily see all that table information inside the IDE.

The opening paragraphs of the article describe the "Why" behind the function or routine.  What is the problem we are trying to solve?  Why does it need to be solved?  What benefit(s) do we gain by solving it?  Etc.

The Approach

We start 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.

In future articles, we will build on this functionality to provide additional details about the fields.

The Algorithm

1. Loop through the fields of a TableDef object
2. Print a comma-delimited list of field names to the immediate window
3. Add line breaks as needed to avoid having to scroll the window horizontally

The Function

Here is the initial implementation of the ListFields function:

Sub ListFields(TblName As String)
    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 = 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

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
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/listfields-part1/
' Purpose   : Print a comma-delimited list of fields to the
'               immediate window to reduce cognitive load from
'               context switching between code and application windows.
' ----------------------------------------------------------------
Sub ListFields(TblName As String)
    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 = 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

Referenced articles

Cover image created with Microsoft Designer

UPDATE [2022-12-07]: Remove unused optional parameter from the ListFields function signature.