Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Setting a RecordSource of a subreport (AccessXP)

    I have got the situation where I need to filter a SubReport. I have the following code in the main reports OnOpen event.
    <pre> sSql = "SELECT [tbl Tags].TrainerID, [tbl Tags].FinancialYear, [tbl Tags].TagNumber,"
    sSql = sSql & " [tbl Tags].IssueDate, [tbl Tags].AmountCharged, [tbl Tags].AmountPaid,"
    sSql = sSql & " [tbl Tags].DatePaid, [tbl Tags].PaymentType, [tbl Tags].Comments"
    sSql = sSql & " FROM [tbl Tags], Control"
    If Not IsNull(OpenArgs) Then
    sSql = sSql & " WHERE [tbl Tags].FinancialYear)=" & OpenArgs
    End If
    Reports![rpt Statement]![rpt Statement Tags].Report.RecordSource = sSql
    </pre>


    It comes up with an error as follows:
    Run time error 2455
    You entered an expression which has an invalid reference to the property Form/Report.

    Question: Can I in fact setup a RecordSource of a subreport in the main reports OnOpen event.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting a RecordSource of a subreport (AccessXP)

    If I remember correctly, the answer is no, Pat. Subreports recordsources are readonly at runtime. One workaround would be to use a saved query as the recordsource for the subreport. You can change the SQL of the saved query at runtime or you could even have the saved query reference a public property of the report containing the OpenArgs value, which would save you from modifying the query at all.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting a RecordSource of a subreport (AccessXP)

    I must try that, the public property of a report, I presume that this is a variant (because it could be a null value) defined just below the Option Explicit and I would just store the OpenArgs value into it. Is this what you mean?

    I got around it by storing the value into a one record table and including that in the query, it works fine.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting a RecordSource of a subreport (AccessXP)

    You can either declare a public variable in the report or you can create a formal property with a private module level variable. The advantage of a property, should you need it, is that you can make it readonly, writeonly, or readwrite and you can actually validate and manipulate the contents in the code inside the property. Personally, I would tend to make the variable/property a string, which will default to an empty string rather than a null. Then if OpenArgs aren't null, assign the value to the property/variable. Your query would then only need to test for a zero length string, not a null.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting a RecordSource of a subreport (AccessXP)

    Thanks Charlotte, but I have decided to go with the one record query, I believe it's a lot simpler.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting a RecordSource of a subreport (AccessXP)

    It depends on how you define simpler. There's nothing wrong with either approach. I generally go with the one that the user is least likely to trash by deleting something ... like a single field table with one record. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting a RecordSource of a subreport (AccessXP)

    Fair point, but I could overcome that by making a new table, then it does not matter if they delete that table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •