#Name? error in the group footer

[Guest]

This is a duplicate of the subject I posted this morning about Conversion
from Access 97 to Access 2003. I'm reposting because the subject may have
been misleading
----------------------------------------------------------------------------------------
I converted an Access 97 database to Access 2003. When I view one of the
reports, a Text Box in the group footer now displays #Name?.

The Text Box Control Source contains =[strTopPowerRating].
strTopPowerRating is a variable declared in a module that is stored with the
report. It gets reset in GroupHeader1_Format and is calculated in
Detail_Format.

-----------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Public strTopPowerRating As Strin
-----------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Expr1000 = "TopPowerRating" Then
strTopPowerRating = "X"
End If
End Su
----------------------------------------------------------------------------------
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
strTopPowerRating = " "
End Su
----------------------------------------------------------------------------------

I've run the debugger and watched the value in strTopPowerRating. It is
calculated properly in Detail_Format and keeps it's value until being reset
to a blank in GroupHeader1_Format. For some reason, the Text Box is not
able to pull in the value of strTopPowerRating in my Access 2003 database.

Any suggestions would be greatly appreciated.

 

[Ken Snell [MVP]]

Variables in VBA code are not available directly to textboxes and other
controls on a report or form. When you type
=[strTopPowerRating]
in a control source for a textbox, you're telling ACCESS to get the value
from the field or control named strTopPowerRating -- and of course, because
there is no such control or field in the report, you get the #Name? error.

You could go the long way around and write a public function that will get
the value of the variable and return it as the function's value, and then
use the function in the control source expression:
=MyPublicFunctionToGetValueOfstrTopPowerRating()

But why not just use the Format event code to write the value into the
textbox:

Private Sub GroupFooternName_Format(Cancel As Integer, FormatCount As
Integer)
If Expr1000 = "TopPowerRating" Then
strTopPowerRating = "X"
End If
Me.TextBoxNameInFooter.Value = strTopPowerRating
End Sub

 

[Guest]

Suggestion 2 was perfect. Thanks for the explanation and recommendations.

Variables in VBA code are not available directly to textboxes and other
controls on a report or form. When you type
=[strTopPowerRating]
in a control source for a textbox, you're telling ACCESS to get the value
from the field or control named strTopPowerRating -- and of course, because
there is no such control or field in the report, you get the #Name? error.

You could go the long way around and write a public function that will get
the value of the variable and return it as the function's value, and then
use the function in the control source expression:
=MyPublicFunctionToGetValueOfstrTopPowerRating()

But why not just use the Format event code to write the value into the
textbox:

Private Sub GroupFooternName_Format(Cancel As Integer, FormatCount As
Integer)
If Expr1000 = "TopPowerRating" Then
strTopPowerRating = "X"
End If
Me.TextBoxNameInFooter.Value = strTopPowerRating
End Sub

--

Ken Snell
<MS ACCESS MVP>

This is a duplicate of the subject I posted this morning about Conversion
from Access 97 to Access 2003. I'm reposting because the subject may have
been misleading.
----------------------------------------------------------------------------------------
I converted an Access 97 database to Access 2003. When I view one of the
reports, a Text Box in the group footer now displays #Name?.

The Text Box Control Source contains =[strTopPowerRating].
strTopPowerRating is a variable declared in a module that is stored with
the
report. It gets reset in GroupHeader1_Format and is calculated in
Detail_Format.

-----------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Public strTopPowerRating As String
-----------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Expr1000 = "TopPowerRating" Then
strTopPowerRating = "X"
End If
End Sub
----------------------------------------------------------------------------------
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
strTopPowerRating = " "
End Sub
----------------------------------------------------------------------------------

I've run the debugger and watched the value in strTopPowerRating. It is
calculated properly in Detail_Format and keeps it's value until being
reset
to a blank in GroupHeader1_Format. For some reason, the Text Box is not
able to pull in the value of strTopPowerRating in my Access 2003 database.

Any suggestions would be greatly appreciated.