I have a report that I'm trying to display the table name on it. On the report I have
="Table Name: " & Chr(34) & [Forms]![frmfieldwidths].[flTableName] & Chr(34) as the Name
In the module I have
Public Function FieldLengths()
' Calculate maximum length used in all text fields of a table
Dim i As Integer, SQL As String, rs As Recordset, tmp As String
Dim db As Database, tbl As TableDef, v As Variant
's = InputBox("Enter Table Name:", "Calc Field Lengths")
'If s = "" Then Exit Function
'
DoCmd.OpenForm "frmSelectTable", acNormal, , , acFormEdit, acDialog
If Len(Nz(sTableName, "")) = 0 Then Exit Function
'
On Error GoTo eh
DoCmd.Hourglass True
v = SysCmd(acSysCmdInitMeter, "Calculating Lengths", 100)
v = SysCmd(acSysCmdUpdateMeter, 5)
SQL = "select "
Set db = CurrentDb
'Set cdb = CodeDb
Set tbl = db.TableDefs(sTableName)
For i = 0 To tbl.Fields.Count - 1
SQL = SQL & "Max(len([" & tbl.Fields(i).Name & "])) as [" & tbl.Fields(i).Name & " Length],"
Debug.Print "SQL Length=" & Len(SQL)
Next
SQL = Left(SQL, Len(SQL) - 1) & " from [" & sTableName & "]"
v = SysCmd(acSysCmdUpdateMeter, 15)
Set rs = CurrentDb.OpenRecordset(SQL)
v = SysCmd(acSysCmdUpdateMeter, 75)
For i = 0 To tbl.Fields.Count - 1
'Debug.Print rs(i).Name & " " & rs(i).Value
tmp = tmp & tbl.Fields(i).Name & ";" & IIf(tbl.Fields(i).Type = dbText, Nz(rs(i).Value, 0), "") & ";" & FieldType(tbl.Fields(i).Type) & ";"
Debug.Print "tmp Length=" & Len(tmp)
Next
rs.Close
v = SysCmd(acSysCmdUpdateMeter, 90)
DoCmd.OpenForm "frmfieldwidths", acNormal, , , acFormEdit, acWindowNormal
v = SysCmd(acSysCmdUpdateMeter, 90)
Forms!frmfieldwidths!List0.RowSource = tmp
Forms!frmfieldwidths.flTableName = sTableName
v = SysCmd(acSysCmdUpdateMeter, 100)
v = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
Exit Function
eh:
DoCmd.Hourglass False
MsgBox "Error: " & Err.Description, vbCritical, "Calc Field Lengths"
End Function
So when I run the report I get #name? in place of the name of the table.
Any help is appreciated
="Table Name: " & Chr(34) & [Forms]![frmfieldwidths].[flTableName] & Chr(34) as the Name
In the module I have
Public Function FieldLengths()
' Calculate maximum length used in all text fields of a table
Dim i As Integer, SQL As String, rs As Recordset, tmp As String
Dim db As Database, tbl As TableDef, v As Variant
's = InputBox("Enter Table Name:", "Calc Field Lengths")
'If s = "" Then Exit Function
'
DoCmd.OpenForm "frmSelectTable", acNormal, , , acFormEdit, acDialog
If Len(Nz(sTableName, "")) = 0 Then Exit Function
'
On Error GoTo eh
DoCmd.Hourglass True
v = SysCmd(acSysCmdInitMeter, "Calculating Lengths", 100)
v = SysCmd(acSysCmdUpdateMeter, 5)
SQL = "select "
Set db = CurrentDb
'Set cdb = CodeDb
Set tbl = db.TableDefs(sTableName)
For i = 0 To tbl.Fields.Count - 1
SQL = SQL & "Max(len([" & tbl.Fields(i).Name & "])) as [" & tbl.Fields(i).Name & " Length],"
Debug.Print "SQL Length=" & Len(SQL)
Next
SQL = Left(SQL, Len(SQL) - 1) & " from [" & sTableName & "]"
v = SysCmd(acSysCmdUpdateMeter, 15)
Set rs = CurrentDb.OpenRecordset(SQL)
v = SysCmd(acSysCmdUpdateMeter, 75)
For i = 0 To tbl.Fields.Count - 1
'Debug.Print rs(i).Name & " " & rs(i).Value
tmp = tmp & tbl.Fields(i).Name & ";" & IIf(tbl.Fields(i).Type = dbText, Nz(rs(i).Value, 0), "") & ";" & FieldType(tbl.Fields(i).Type) & ";"
Debug.Print "tmp Length=" & Len(tmp)
Next
rs.Close
v = SysCmd(acSysCmdUpdateMeter, 90)
DoCmd.OpenForm "frmfieldwidths", acNormal, , , acFormEdit, acWindowNormal
v = SysCmd(acSysCmdUpdateMeter, 90)
Forms!frmfieldwidths!List0.RowSource = tmp
Forms!frmfieldwidths.flTableName = sTableName
v = SysCmd(acSysCmdUpdateMeter, 100)
v = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
Exit Function
eh:
DoCmd.Hourglass False
MsgBox "Error: " & Err.Description, vbCritical, "Calc Field Lengths"
End Function
So when I run the report I get #name? in place of the name of the table.
Any help is appreciated