Scope issue? Variables passed from form to report with OpenArgs

[rwfreeman]

Access 2003. I'm trying to pass values for two variables to a report
from a form but the controls on the report are giving me "#Name?"
errors wherever the passed variables are used.

Here's the code on the form
Private Sub cmdPreviewClaim_Click()
Dim strOpenArgs As String
strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
frmMain.Form.currSetAside
DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
End Sub

Here's the Open event of the report (ParseText() is a module level
function to parse strings with a "|" delimiter). The variables are
DIMmed as public variables in the Declarations section of the report
code.
Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
currCAFESetAside = ParseText(Me.OpenArgs, 1)
End If
End Sub

There aren't any name conflicts between controls in the report and
names for the passed variables. It doesn't seem to matter where
(module vs. report) that the variables are DIMmed. If Access pops up
the window that asks for values for these variables and one enters
values for them, the report does what it's supposed to do. But if
values for the variables come from code, the report controls (which
are text boxes with experssions as the ControlSource) show #Name?
errors.

It's gotta be simple but I'm stumped.

Richard

 

[Ken Snell \(MVP\)]

How are you using the named variables in the report? What are the control
source property values for the controls that show the error?

What data type is ParseText function declared as?

 

[rwfreeman]

How are you using the named variables in the report? What are the control
source property values for the controls that show the error?

What data type is ParseText function declared as?

--

Ken Snell
<MS ACCESS MVP>











- Show quoted text -

Named variables are used in expressions in textbox controls. An
example for the currClaimedToDate control (formatted as Currency) the
ControlSource is
=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
Year([ExpDate])=" & intCafePlanYear) The intent of the expression is
to see if there are any records in the tblExpenses table for the
selected plan year that are marked as being claimed. If so, sum the
amounts for the plan year, otherwise show a 0 value. Each time I try
to fully enclose the Criteria in quotes (i.e. "criteria") I get an
error.

ParseText returns a Variant which when put into the declared variable
follows its declared variable type - integer for intCAFEPlanYear and
currency for currCAFESetAside (I checked this when stepping through
the code; their VarType()s were 2 and 6, respectively.

BTW, if it's easier to do this with a function as the control source,
could you indicate for me how to call the function from the
control's .ControlSource property and pass the intCAFEPlanYear
variable?

There's a line from one of my favorite Jimmy Buffett songs (he's
describing his attempts to mimic the bear dance, "It's so simple it
plum evaded me") that pretty well describes my level of angst over to
being unable to make Access do this apparently simple task. Thanks
again for your time and patience.

Richard

 

[Ken Snell \(MVP\)]

You cannot reference a VBA variable within a Control Source expression. You
must write a public function that will read the value of the variable and
return it as the function's value - -for example,:

Public Function GetMyCafePlanYearVariable() As Integer
GetMyCafePlanYearVariable = intCafePlanYear
End Function


This function should be put in the report's module so that it can see the
variable, and you must declare the variable as global for the report module
by putting this statement in the report's module's Declarations section:

Private intCafePlanYear As Integer


Do the same for all such variables that you want to use.


Then, use the function in your control source expression:

=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
GetMyCafePlanYearVariable())=0,0,DSUM("[Amount]","tblExpenses","[Claimed]
AND
Year([ExpDate])=" & GetMyCafePlanYearVariable())


--

Ken Snell
<MS ACCESS MVP>

How are you using the named variables in the report? What are the control
source property values for the controls that show the error?

What data type is ParseText function declared as?

--

Ken Snell
<MS ACCESS MVP>











- Show quoted text -

Named variables are used in expressions in textbox controls. An
example for the currClaimedToDate control (formatted as Currency) the
ControlSource is
=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
Year([ExpDate])=" & intCafePlanYear) The intent of the expression is
to see if there are any records in the tblExpenses table for the
selected plan year that are marked as being claimed. If so, sum the
amounts for the plan year, otherwise show a 0 value. Each time I try
to fully enclose the Criteria in quotes (i.e. "criteria") I get an
error.

ParseText returns a Variant which when put into the declared variable
follows its declared variable type - integer for intCAFEPlanYear and
currency for currCAFESetAside (I checked this when stepping through
the code; their VarType()s were 2 and 6, respectively.

BTW, if it's easier to do this with a function as the control source,
could you indicate for me how to call the function from the
control's .ControlSource property and pass the intCAFEPlanYear
variable?

There's a line from one of my favorite Jimmy Buffett songs (he's
describing his attempts to mimic the bear dance, "It's so simple it
plum evaded me") that pretty well describes my level of angst over to
being unable to make Access do this apparently simple task. Thanks
again for your time and patience.

Richard

 

[rwfreeman]

You cannot reference a VBA variable within a Control Source expression. You
must write a public function that will read the value of the variable and
return it as the function's value - -for example,:

Public Function GetMyCafePlanYearVariable() As Integer
GetMyCafePlanYearVariable = intCafePlanYear
End Function

This function should be put in the report's module so that it can see the
variable, and you must declare the variable as global for the report module
by putting this statement in the report's module's Declarations section:

Private intCafePlanYear As Integer

Do the same for all such variables that you want to use.

Then, use the function in your control source expression:

=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
GetMyCafePlanYearVariable())=0,0,DSUM("[Amount]","tblExpenses","[Claimed]
AND
Year([ExpDate])=" & GetMyCafePlanYearVariable())

--

Ken Snell
<MS ACCESS MVP>

Named variables are used in expressions in textbox controls. An
example for the currClaimedToDate control (formatted as Currency) the
ControlSource is
=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
Year([ExpDate])=" & intCafePlanYear) The intent of the expression is
to see if there are any records in the tblExpenses table for the
selected plan year that are marked as being claimed. If so, sum the
amounts for the plan year, otherwise show a 0 value. Each time I try
to fully enclose the Criteria in quotes (i.e. "criteria") I get an
error.

ParseText returns a Variant which when put into the declared variable
follows its declared variable type - integer for intCAFEPlanYear and
currency for currCAFESetAside (I checked this when stepping through
the code; their VarType()s were 2 and 6, respectively.

BTW, if it's easier to do this with a function as the control source,
could you indicate for me how to call the function from the
control's .ControlSource property and pass the intCAFEPlanYear
variable?

There's a line from one of my favorite Jimmy Buffett songs (he's
describing his attempts to mimic the bear dance, "It's so simple it
plum evaded me") that pretty well describes my level of angst over to
being unable to make Access do this apparently simple task. Thanks
again for your time and patience.

Richard- Hide quoted text -

- Show quoted text -

Ken - I'm making slow progress. The assignment function and variable
declarations are just the ticket and now I can get some of the
controls on the report to use the passed value for the plan year.
However, where a control's .ControlSource is an expression such as the
DCOUNT() and DSUM() functions above, an #Error is returned; so it's
gotta be a syntaxt error, right?
If I wanted to sum the Amount field in the tblExpenses table where the
ExpDate (format medium date) is within the plan year 2007 and the
Claimed field (boolean) is true, wouldn't the Criteria clause of the
DSUM function have to literally be (i.e. the pseudo-WHERE clause is
bounded by double quotes)?

"Year([ExpDate])=2007 AND [claimed]"

Why is it that - double quote double quote YEAR([ExpDate])=double
quote & GetCAFEPlanYearVariable() & double quote AND [Claim] double
quote double quote - won't make it past the expression editor. It
seems to me that such syntax would preserve the bounding double quotes
yet let one use a variable for one of the parts of the criteria
clause. I tried single quotes to set off the variable but no going
there either.

One thing I have found out is that the DCOUNT() function could return
a Null value so I've set its tru-false condition to IsNull(DCOUNT()).

Thanks in advance for the help
Richard

 

[rwfreeman]

You cannot reference a VBA variable within a Control Source expression. You
must write a public function that will read the value of the variable and
return it as the function's value - -for example,:

Public Function GetMyCafePlanYearVariable() As Integer
GetMyCafePlanYearVariable = intCafePlanYear
End Function

This function should be put in the report's module so that it can see the
variable, and you must declare the variable as global for the report module
by putting this statement in the report's module's Declarations section:

Private intCafePlanYear As Integer

Do the same for all such variables that you want to use.

Then, use the function in your control source expression:

=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
GetMyCafePlanYearVariable())=0,0,DSUM("[Amount]","tblExpenses","[Claimed]
AND
Year([ExpDate])=" & GetMyCafePlanYearVariable())

--

Ken Snell
<MS ACCESS MVP>

news:[email protected]...

On May 24, 8:41 am, "Ken Snell \(MVP\)"
How are you using the named variables in the report? What are the control
source property values for the controls that show the error?
What data type is ParseText function declared as?
--
Ken Snell
<MS ACCESS MVP>

Access 2003. I'm trying to pass values for two variables to a report
from a form but the controls on the report are giving me "#Name?"
errors wherever the passed variables are used.
Here's the code on the form
Private Sub cmdPreviewClaim_Click()
Dim strOpenArgs As String
strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
frmMain.Form.currSetAside
DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
End Sub
Here's the Open event of the report (ParseText() is a module level
function to parse strings with a "|" delimiter). The variables are
DIMmed as public variables in the Declarations section of the report
code.
Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
currCAFESetAside = ParseText(Me.OpenArgs, 1)
End If
End Sub
There aren't any name conflicts between controls in the report and
names for the passed variables. It doesn't seem to matter where
(module vs. report) that the variables are DIMmed. If Access pops up
the window that asks for values for these variables and one enters
values for them, the report does what it's supposed to do. But if
values for the variables come from code, the report controls (which
are text boxes with experssions as the ControlSource) show #Name?
errors.
It's gotta be simple but I'm stumped.
Richard- Hide quoted text -
- Show quoted text -
Named variables are used in expressions in textbox controls. An
example for the currClaimedToDate control (formatted as Currency) the
ControlSource is
=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
Year([ExpDate])=" & intCafePlanYear) The intent of the expression is
to see if there are any records in the tblExpenses table for the
selected plan year that are marked as being claimed. If so, sum the
amounts for the plan year, otherwise show a 0 value. Each time I try
to fully enclose the Criteria in quotes (i.e. "criteria") I get an
error.
ParseText returns a Variant which when put into the declared variable
follows its declared variable type - integer for intCAFEPlanYear and
currency for currCAFESetAside (I checked this when stepping through
the code; their VarType()s were 2 and 6, respectively.
BTW, if it's easier to do this with a function as the control source,
could you indicate for me how to call the function from the
control's .ControlSource property and pass the intCAFEPlanYear
variable?
There's a line from one of my favorite Jimmy Buffett songs (he's
describing his attempts to mimic the bear dance, "It's so simple it
plum evaded me") that pretty well describes my level of angst over to
being unable to make Access do this apparently simple task. Thanks
again for your time and patience.
Richard- Hide quoted text -

- Show quoted text -

Ken - I'm making slow progress. The assignment function and variable
declarations are just the ticket and now I can get some of the
controls on the report to use the passed value for the plan year.
However, where a control's .ControlSource is an expression such as the
DCOUNT() and DSUM() functions above, an #Error is returned; so it's
gotta be a syntaxt error, right?
If I wanted to sum the Amount field in the tblExpenses table where the
ExpDate (format medium date) is within the plan year 2007 and the
Claimed field (boolean) is true, wouldn't the Criteria clause of the
DSUM function have to literally be (i.e. the pseudo-WHERE clause is
bounded by double quotes)?

"Year([ExpDate])=2007 AND [claimed]"

Why is it that - double quote double quote YEAR([ExpDate])=double
quote & GetCAFEPlanYearVariable() & double quote AND [Claim] double
quote double quote - won't make it past the expression editor. It
seems to me that such syntax would preserve the bounding double quotes
yet let one use a variable for one of the parts of the criteria
clause. I tried single quotes to set off the variable but no going
there either.

One thing I have found out is that the DCOUNT() function could return
a Null value so I've set its tru-false condition to IsNull(DCOUNT()).

Thanks in advance for the help
Richard- Hide quoted text -

- Show quoted text -

I finally gave up on the expressions as controlsource property for the
textbox. Instead, I calculate the value of the ClaimsToDate in the
report's Open Form event then use a GetClaimsToDateVariable function
to pass the value to the report. Works fine and is error free.
Thanks again for your insights.

 

[Ken Snell \(MVP\)]

Not sure of exactly the syntax you tried to use, but here is how a DCount
function would look in a control source expression when using a function to
get the value of a module-level variable:

=DCount("*", "QueryTableName", "Year([ExpDate])=" &
GetCAFEPlanYearVariable() & " And [Claim] = True")