Loop counter not working properly 1

[vba317]

I am lost in my loops. I am trying to create a summary table about all the tables in my database. I have the part of the function that reads the tables working correctly. Because this function reads all the fields from the table. And I just want the table name I am trying with no success to keep the row counter the same until the table changes. Any help is appreciated.

Code from command button
[code}

Public Sub cmdTblInfo_Click()
Dim lTbl As Long
Dim lFld As Long
Dim lRow As Long
Dim intDataRow As Integer
Dim dBase As DAO.Database
Dim strFileLoc As String
Dim strClient As String
Dim strFile As String
Dim strSheet As String
Dim rst As Recordset
Dim intFlg As Integer
Dim intFR As Integer

Set dBase = CurrentDb
'Set on error in case there are no tables
On Error Resume Next
strFileLoc = "Z:\Adhoc projects\WMG\TableDefinitions\"
strFile = "TableDescriptions"
strSheet = "Descriptions"
Call XLOpen(strFileLoc, strClient, strFile, strSheet)
lRow = 2
intDataRow = 1
intFR = 1
'Loop through all tables
For lTbl = 0 To dBase.TableDefs.Count
For lFld = 0 To dBase.TableDefs(lTbl).Fields.Count - 1
With goXL.ActiveSheet
If intDataRow = 1 Then .Range("A1") = "Table Name"
If intDataRow = 1 Then intFlg = 1
intDataRow = intDataRow + 1
If lRow > 2 Then lRow = lRow + 1
Call GetDataType2(dBase.TableDefs(lTbl).Fields(lFld), (lTbl), (intDataRow), (lFld), intFlg, intFR)
lRow = lRow + 1
End With
Next lFld
'End If ' Temp table check If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or
Next lTbl ' switched next ltbl with lfld 10/22
' End If ' Loop for Table names 'Resume error breaks
On Error GoTo 0
Call XLSave(strFileLoc, strFile)
End Sub
[/code]

Code from function

Public Function GetDataType2(D As Field, ByRef lTbl As Integer, ByRef intDataRow As Integer, ByRef lFld As Integer, intFlg As Integer, intFR As Integer) As String
' **********************************************************************************
' *** THIS FUNCTION READS THE FIELDS FROM THE TABLE AND CREATED A REPORT OF IT******
' **********************************************************************************
    Dim dBase As DAO.Database
    Dim xlApp As Object
    Dim wbExcel As Object
    Dim fRow As Long
    Set dBase = CurrentDb
    Dim IndexExists As Boolean
    Dim IndexText As String
    Dim idx As DAO.Index
    Dim prp As DAO.Property
    Dim strIdx As DAO.Index
    Dim Col As String
    Dim strTbl As String
    Dim strFld As String
    Dim str1 As String
    Dim str2 As String
    Dim intDataRowM As Integer

    If intFlg = 1 Then intDataRowM = intDataRow - 1
    With goXL.ActiveSheet
        str1 = dBase.TableDefs(lTbl).Fields(lFld).SourceTable
        str2 = .Range("A" & intDataRowM)
        If str1 = str2 Then intFlg = 0
        If str1 <> str2 Then intFlg = 1
        If intFlg = 1 Then .Range("A" & intDataRow) = dBase.TableDefs(lTbl).Fields(lFld).SourceTable
    End With
End Function

 

[PHV]

Use lTbl + 1 as table number.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thank you for letting me see the forest thru the trees