Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Posts
    60

    Unanswered: Manipulate subreports by code-repost

    Didn't get any response the first time, thought I'd try again:


    I have a report with nested subreports (subreport in subreport of original report). Sometimes I want to filter the innermost subreport by a date range and sometimes I don't.

    Instead of creating two reports (one with filter, one w/o) I am trying to manipulate the Filter property with code in the original report. I keep getting errors saying I can't do that. I have placed the code in Report Open event, Detail Format event and just about all other events.

    I then tried to change the RecordSource of the innermost subreport via code in the original report, again no success.

    Does anyone have any suggestions how to be able apply a filter or change RecordSource of a subreport via code in the original report?

    I am using Access XP.

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just a thought (...never tried it, since i don't use filters):

    can you try a .visible=no textbox on the mother report that is set to something suitable by code in the mother
    ...and some code in the daughter report that decides what to do based on that textbox in the mother.

    ?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Posts
    60
    Thanks for the input: doesn't work. Can't change the RecordSource or apply Filter of nested subreport after main report starts to 'print'-can't read value out of textbox until after start of 'print'. Might be able to have the Filter refer to the textbox, will look into that as time permits.

    Another approach that will work is to use globally declared variables in the WHERE clause of the queries. As of yet I have not tried to experiment with this, do you know if this possible?

    I have gone ahead and made two copies of all the reports/subreports just so I can move forward. I really want to figure this out and make the database more efficient/smaller.

    I'm not a Filter fan myself, just trying to get this to work.

    Thanks again.

    Originally posted by izyrider
    just a thought (...never tried it, since i don't use filters):

    can you try a .visible=no textbox on the mother report that is set to something suitable by code in the mother
    ...and some code in the daughter report that decides what to do based on that textbox in the mother.

    ?

    izy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your globals will work if you have the "timing" right to set the .recordsource

    unfortunately i dont know from memory the full event sequence that occurs when you open a report-with-subreport.

    this takes me back to a question i asked a while back that got no answers: does anyone have an "event monitor" that i can use in development to track every (including "empty") event that occurs?????

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by izyrider
    your globals will work if you have the "timing" right to set the .recordsource

    unfortunately i dont know from memory the full event sequence that occurs when you open a report-with-subreport.

    this takes me back to a question i asked a while back that got no answers: does anyone have an "event monitor" that i can use in development to track every (including "empty") event that occurs?????

    izy
    With the Visual InterDev there's the Spy++ ... Is that what you're looking for?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tks - i'll have a look tomorrow.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Manipulate subreports by code-repost

    Originally posted by bc301
    Didn't get any response the first time, thought I'd try again:


    I have a report with nested subreports (subreport in subreport of original report). Sometimes I want to filter the innermost subreport by a date range and sometimes I don't.

    Instead of creating two reports (one with filter, one w/o) I am trying to manipulate the Filter property with code in the original report. I keep getting errors saying I can't do that. I have placed the code in Report Open event, Detail Format event and just about all other events.

    I then tried to change the RecordSource of the innermost subreport via code in the original report, again no success.

    Does anyone have any suggestions how to be able apply a filter or change RecordSource of a subreport via code in the original report?

    I am using Access XP.

    Thanks.
    One option is to open the subreport in code in design view and hidden. You can then set properties like FilterOn and Filter and then save and close it. Then open the main report. This is an example of what worked for me. strReportName is a variable that stores the current main report that I am opening which in this case is rptTrainingDataSummary.

    Dim rpt as Report

    If strReportName = "rptTrainingDataSummary" Then
    If CurrentProject.AllReports("srptTrainingDataSummary ").IsLoaded = False Then
    DoCmd.OpenReport "srptTrainingDataSummary", acViewDesign, , , acHidden
    Set rpt = Reports("srptTrainingDataSummary")
    rpt.FilterOn = True
    rpt.Filter = "CompetencyID = 1"
    DoCmd.Close acReport, rpt.Name, acSaveYes
    End If
    End If
    DoCmd.OpenReport strReportName, acViewPreview

    Just an option. Hope it helps.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  8. #8
    Join Date
    Dec 2002
    Posts
    60
    Thanks for the input, I had considered trying something like that, just wasn't sure how to do it.

    Does this method cause the database to 'balloon' in size (requiring more frequent compaction)? My (limited) understanding is that doing something like this will create temp objects that require a compaction to totally get rid of.

    Once again, thanks for the code.

  9. #9
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by bc301
    Thanks for the input, I had considered trying something like that, just wasn't sure how to do it.

    Does this method cause the database to 'balloon' in size (requiring more frequent compaction)? My (limited) understanding is that doing something like this will create temp objects that require a compaction to totally get rid of.

    Once again, thanks for the code.
    I tend to compact my databases regularly anyway so it doesn't really bother me. Designing and saving will tend to make things bigger. I would suggest you try it for a session or so and monitor the size increase.

    It seems like you ought to be able to include the fields that you want to filter on in the main report, or use DLookup to restrict things. If you don't, you will probably end up with main report records that don't contain any subreport records.

    Just some additional ramblings.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  10. #10
    Join Date
    Dec 2002
    Posts
    60
    I found a solution using global variables:

    Recap: sometimes want to limit query by date range, sometimes want to return all records. Can't change RecordSource of a Subreport in Subreport in Report by code unless report is open in design mode (per basicmek in earlier reply).

    Create three functions:

    Function ReportFirstDate() As Date
    If Len(gstrReportFirstDate) > 0 Then ReportFirstDate = Format(gstrReportFirstDate, "0")
    End Function

    Function ReportLastDate() As Date
    If Len(gstrReportLastDate) > 0 Then ReportLastDate = Format(gstrReportLastDate, "0")
    End Function

    Function NoDateRange() As Boolean
    NoDateRange=False
    If Len(gstrReportFirstDate) = 0 Then NoDateRange = True
    End Function


    Assigned gstrReportFirstDate and gstrReportLastDate the date range desired OR if no range desired (return all records) assign gstrReportFirstDate="" (zero length string)

    Use BETWEEN ReportFirstDate() AND ReportLastDate() as first criteria of date field in query then use NoDateRange()=True as OR criteria in same date field.
    Last edited by bc301; 04-21-04 at 23:57.

Posting Permissions

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