VBA looping thorugh data in column, not columns in table ACCESS 2010

I am trying to loop through columns of a table, but the below actually is looping through the data for each field in the columns.  How should I change the code to only loop through the columns themselves, not the data in the columns?

Public Sub ChangeDisplayControls()
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
   
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        strTableName = tdf.Name & 1
        Set rst = dbs.OpenRecordset(strTableName)
        With rst
        For i = 1 To .Fields.Count
            Debug.Print "Field Name: " & .Fields(i)  'Turns out is the data not the column!
            If TypeName(.Fields(i)) = "Text" Then

                If .Fields(i).Properties("DisplayControl") = "ComboBox" Then
                    .Fields(i).Properties("DisplayControl") = "Text"
                End If
            End If
        Next i
        End With
    End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
   
Exit_Errorhandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use

Debug.Print "Field Name: " & .Fields(i).NAME

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Yes, help solved the problem.  Below is the code that now shows the name of the column and type.  Which is string, so I can go back to the other post about changing the displaycontrol.
Public Sub ChangeDisplayControls()
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
   
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        strTableName = tdf.Name & 1
        Set rst = dbs.OpenRecordset(strTableName)
        With rst
        For i = 1 To .Fields.Count
            Debug.Print "Field Name: " & .Fields(i).Name
            Debug.Print "TypeName: " & TypeName(.Fields(i).Name)
            If TypeName(.Fields(i).Name) = "String" Then

                If .Fields(i).Properties("DisplayControl") = "ComboBox" Then
                    .Fields(i).Properties("DisplayControl") = "Text"
                End If
            End If
        Next i
        End With
    End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
   
Exit_Errorhandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.