Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Public Variables to Reports

Discussion in 'Information Technology' started by apollo8359, Jul 28, 2006.

  1. apollo8359

    apollo8359
    Expand Collapse
    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
     
  2. Loading...


  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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


    apollo8359 wrote:
    > 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
     
  4. apollo8359

    apollo8359
    Expand Collapse
    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

    "Steve Schapel" wrote:

    > 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
    >
    >
    > apollo8359 wrote:
    > > 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

    >
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Michael,

    apollo8359 wrote:
    > 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.

    --
    Steve Schapel, Microsoft Access MVP
     
  6. apollo8359

    apollo8359
    Expand Collapse
    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

    "Steve Schapel" wrote:

    > Michael,
    >
    > apollo8359 wrote:
    > > 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.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
     
  7. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    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.


    "apollo8359" <apollo8359@discussions.microsoft.com> wrote in message
    news:2F256B24-46DC-48CF-80FE-C3EC861C5725@microsoft.com...
    >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
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Michael,

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

    --
    Steve Schapel, Microsoft Access MVP

    apollo8359 wrote:
    > 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
    >
     

Share This Page