Public Variables to Reports

[Guest]

I can't seem to get the public variable to display in the report. It shows up
in the immediate window just fine, (debug.print [ReportDate]), but the report
stops and asks me for the value. I have stopped the code and moused over the
variable and it has the correct information before the report is opened.
On the report it's in a text box and the control source is =[ReportDate]. It
is defined as a public variable string in my main module, not behind the
report form. The string is generated in VB and based on date options the user
selects in the report form, so I can't include this in the query. Any help
would be greatly appreciated as I could then reduce the number of reports.
Thanks

 

[Steve Schapel]

Apollo,

No you can't do it like this. The Control Source of the textbox will
"think" that ReportDate is a field in the report's record source.

I think you could write code in the report's Open event like this...
Me.YourTextbox = ReportDate

However, there are probably also other ways to do it, for example set
the textbox's Control Source to the equivalent of...
=[Forms]![ReportForm]![DateTextbox]

Also, I suspect you may not be correct in your claim that "I can't
include this in the query".

If you need more help with htis, please post back with some further
details, with examples. Thanks.

 

[Guest]

The report form has a start and stop date. I concatenate those into a string
like "1/31/2006 through 3/31/2006", and put that into the variable
ReportDate. However, there is a check box on the form that negates any date
filtering and when that is checked off the string changes to "2005 through
today" which is the span of our data right now. Although the dates are in the
query, I don't know how I could reference just the beginning and ending dates.

I just can't believe a public variable can't be pulled into a report, if
it's public any place should be able to reference it; but I am relatively new
to programming in VB. The real reason I want this to work is if I can change
the titles and dates around to reflect the user selected options I can reduce
the number of reports I have.
Thanks Steve,
Michael

Apollo,

No you can't do it like this. The Control Source of the textbox will
"think" that ReportDate is a field in the report's record source.

I think you could write code in the report's Open event like this...
Me.YourTextbox = ReportDate

However, there are probably also other ways to do it, for example set
the textbox's Control Source to the equivalent of...
=[Forms]![ReportForm]![DateTextbox]

Also, I suspect you may not be correct in your claim that "I can't
include this in the query".

If you need more help with htis, please post back with some further
details, with examples. Thanks.

--
Steve Schapel, Microsoft Access MVP

I can't seem to get the public variable to display in the report. It shows up
in the immediate window just fine, (debug.print [ReportDate]), but the report
stops and asks me for the value. I have stopped the code and moused over the
variable and it has the correct information before the report is opened.
On the report it's in a text box and the control source is =[ReportDate]. It
is defined as a public variable string in my main module, not behind the
report form. The string is generated in VB and based on date options the user
selects in the report form, so I can't include this in the query. Any help
would be greatly appreciated as I could then reduce the number of reports.
Thanks

 

[Steve Schapel]

Michael,

I just can't believe a public variable can't be pulled into a report, if
it's public any place should be able to reference it;

Well, that's not what I said . You can't reference it in the
properties (such as Control Source) of a control on the report. If it's
a variable in the report module, it has to be managed in the report
module, i.e. assigned to the control in code. Which you can do, as I
mentioned before.

If it was mine, though, I would put a calculated field in the query, and
then bind the report control directly to it. Like this...
ReportDate:
IIf([Forms]![MyForm]![MyCheckBox],[Forms]![MyForm]![DateFrom] & "
through " & [Forms]![MyForm]![DateTo],"2005 through today")

Alternatively, you could put the above expression directly into the
Control Source of the textbox.

 

[Guest]

Steve,
Thanks for the clairification, I think I will reference it in the report
module and assign it there. During our discussion I found a less elegant way,
I assigned the value to the report form and hid the text box. In the report
itself I referenced the text box on the report form and that seemed to work.
Two steps to get one, but it worked. As I mentioned, I will put this in the
report module and keep the form clean. Thanks for your suggestions and help.
Michael

Michael,

I just can't believe a public variable can't be pulled into a report, if
it's public any place should be able to reference it;

Well, that's not what I said . You can't reference it in the
properties (such as Control Source) of a control on the report. If it's
a variable in the report module, it has to be managed in the report
module, i.e. assigned to the control in code. Which you can do, as I
mentioned before.

If it was mine, though, I would put a calculated field in the query, and
then bind the report control directly to it. Like this...
ReportDate:
IIf([Forms]![MyForm]![MyCheckBox],[Forms]![MyForm]![DateFrom] & "
through " & [Forms]![MyForm]![DateTo],"2005 through today")

Alternatively, you could put the above expression directly into the
Control Source of the textbox.

 

[Bill Edwards]

You could create a public function to return the value of the public
variable and then use the public function as the control source of a
report's control;

Option Compare Database
Option Explicit
' First declare the public variable, in this case a string
Public strPublic As String

' Now create a function that returns the value of strPublic
Public Function GetPublic() As String
On Error Goto Err_Label
' Check to see if we have initialized the value of strPublic to something
If Len(strPublic) & "" > 0 Then
' We have initialized strPublic to some value, so return that value
GetPublic = strPublic
Else
' We have not initialized strPublic to some value, so return a value of
"Undefined"
GetPublic = "Undefined"
End If

Exit_Label:
Exit_Function
Err_Label:
Msgbox Err.Description
GetPublic = "Error"
Resume Exit_Label
End Function

The Control source setting for your text box in your report would be
=GetPublic()

I used a string variable in the example, but it could be easily changed to
return a date value.

 

[Steve Schapel]

Michael,

Ok, sounds good. As I mentioned, I think the Open event of the report.