#name? error on report (1 Viewer)

guidop_24

New member
Local time
Today, 00:05
Joined
Feb 24, 2021
Messages
3
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:05
Joined
Sep 21, 2011
Messages
8,826
You do not need the chr(34) unless you want to see " on your report.
Form would also need to be open?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:05
Joined
Feb 28, 2001
Messages
20,673
At first blush I cannot see where you assign a value to TableName or sTableName, which is probably the source of the #name error. Using sTableName as you do, it is probably an empty string, but to successfully look up db.TableDefs(sTableName), sTableName can't be empty.
 

guidop_24

New member
Local time
Today, 00:05
Joined
Feb 24, 2021
Messages
3
Right I was leaning towards that. Unfortunately the gentlemen that wrote this is no longer here and I'm not the greatest programmer. I was hoping that someone could decipher the module
 

guidop_24

New member
Local time
Today, 00:05
Joined
Feb 24, 2021
Messages
3
Just an FYI also this all worked in access 97 but doesn't work in the newest version of access
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:05
Joined
Feb 28, 2001
Messages
20,673
Without trying to be argumentative, something is missing here because as it was presented, this wouldn't have worked in Ac97 either. Even back then, undefined variables used to "pick" a named object from a collection still required the name to be explicit or you would get #name errors. Something must be defined externally as a public variable somewhere in order for this to have worked in Ac97. If you added Option Explicit at the top of the module from which you got that code, EITHER something would barf OR you have some things defined as Public in a place we have not seen yet.
 

Users who are viewing this thread

Top Bottom