Column String

[Thompyt]

I am trying to get a the column names from a table, and assign them a number, then placing it all into an array.

Private Sub Assign_Colums__Number_Click()

Dim dbs As DAO.Database
Dim tdf as DAO.TableDef
Dim rs As DAO.Recordset
Dim fld as DAO.Field
Dim C as Integer
Dim Last_Col_Num As Long, Col_Num
Dim strFieldName as String
Dim Headers As Variant

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("ActualTableName")

   Last_Col_Num = CurrentDb.TableDefs("actualTableName").Fields.Count

For C = 2 To Last_Col_Num
   For Each fld In tdf.Fields
      If Len(strFieldName) = 0 Then
                    MsgBox "It's Broke:"  & C
      ElseIf Len(strFieldName) <> 0 Then
          strFieldName = fld.Name
          Col_Num = C
      End If
   Next fld
Next C

   Headers = Array(strFieldName, Col_Num)

    DoEvents

dbs = Nothing
tdf = Nothing
rs=Nothing

End Sub

I keep getting- It's Broke and the column number.

[moke123]

looks like the len of strFieldName will always be 0 so it never reaches the else part of your code which appends the field name.
I'm not sure why your testing for length or using the "For C = ..." loop. Couldnt you just use the "For Each fld In tdf.Fields " loop and use "C = C+1"

[Thompyt]

Moke,
It gets past this part now: If Len(fld.Name) = 0

I wanted to assign the number to the name in an array.

Headers - Array(Column1,1,

Column2,2,

Column3,1,

Column3,1,

ColumnN,N,.....end where the column stringvalue = 0)

[June7]

AFAIK, Array() function can only populate a 1-dimensional array object. Looks to me like you want a 2-dimensional array object. Since the final size of the array is unknown, need to use ReDim. Two-dimension array indexes are (Row, Column). Code creates an array with a row for each field and 2 columns.

What do you want to do with array?

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim Last_Col_Num As Long, Col_Num As Integer
Dim Headers() As Variant
Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("YourTableNameHere")
Last_Col_Num = tdf.Fields.Count
ReDim Headers(1 To Last_Col_Num, 1 To 2)
For Col_Num = 1 To Last_Col_Num
   For Each fld In tdf.Fields
      If Len(fld.Name) = 0 Then
          MsgBox "It's Broke:" & Col_Num
      Else
          Headers(Col_Num, 1) = fld.Name
          Headers(Col_Num, 2) = Col_Num
          Debug.Print Headers(Col_Num, 1) & " : " & Headers(Col_Num, 2)
          Col_Num = Col_Num + 1
      End If
   Next fld
Next
Set dbs = Nothing
Set tdf = Nothing

[accesstos]

Thompyt, maybe you will find useful the function below:

Function TableFields(ByVal strTable As String, ByRef varHeaders() As Variant) As Long
    Dim i As Integer

    On Error Resume Next
    With CurrentDb.TableDefs(strTable).OpenRecordset
        With .Fields
            ReDim varHeaders(.Count, 2)
            For i = 0 To .Count - 1
                varHeaders(i, 0) = i + 1                            'Field's Index
                varHeaders(i, 1) = .Item(i).Name                    'Field's Name
                varHeaders(i, 2) = .Item(i).Properties("Caption")   'Field's Caption
            Next i
            TableFields = .Count
        End With

Best regards,
John
        .Close
    End With
End Function

It returns the count of the fields of the table with the strTable name and fills the var Headers() array with their names (and captions if exist).
You can use it as follows:

Sub PrintTableHeaders()
    Dim TD As DAO.TableDef
    Dim vHeaders() As Variant
    Dim i As Integer

    For Each TD In CurrentDb.TableDefs
        If Not TD.Name Like "MSys*" Then
            Debug.Print TD.Name
            Debug.Print vbTab & "Details of " & TD.Fields.Count & " Fields"
            For i = 0 To TableFields(TD.Name, vHeaders) - 1
                Debug.Print vbTab & vHeaders(i, 0) & ". " & vHeaders(i, 1) & " (""" & vHeaders(i, 2) & """)"
            Next i
        End If
    Next TD
End Sub