Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    73

    Arrow Unanswered: dynamically set the RecordSource of a report????

    I am so close to finding this answer. I have a report that I have designed and I am happy with it. It is in quite detail and beautiful My problem is I want run this report with multiple stored procedures getting different data. As of now I would have to create copies of both the SP and the RPT. If I make a change to the design of the report I have to change them all! So my question is how do I dynamically change the record source of the report from an OnClick event of a form. Can this be done? I have tried to logically put together the following code with no luck:

    Private Sub Command354_Click()
    Dim stDocName As String
    Dim strRecordSource As String
    stDocName = "RPT_TEST_RMA"
    DoCmd.OpenReport stDocName, acNormal
    strRecordSource = "Exec [SP_ADVANCES_NOT_RECEIVED]"
    Me.RecordSource = strRecordSource
    End Sub

    Please help me as to what I am missing. I want to tell the report which SP to use.

    Thanks
    Last edited by X-Centric; 02-28-05 at 11:54.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Access version???? VERY IMPORTANT! If you have Access 2002 or up try using the OpenArgs property to "dynamically" set your recordsource ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2004
    Posts
    73
    I an using access 2003. I am not familiar with using OpenArgs. A sample or explanation would jumpstart my brain.

    Thanks

  4. #4
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    try calling the report by
    docmd.OpenReport stDocName, acNormal, OpenArgs:="SP_ADVANCES_NOT_RECEIVED"


    and enhance the report by implementing the report_Open event (onOpen) with
    me.recordsource="exec [" & me.openargs & "]"

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a couple of thoughts:

    don't play with the .recordsource, instead play with the .querydef that feeds the report. it's no issue to change querydef.SQL from code

    or

    play with the .recordsource... it ought to be possible to play with the report object, but a few seconds of experiments just now were not successful.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    May 2004
    Posts
    73
    Thank you guys so much, the OpenArgs worked like a charm. Topic closed.

Posting Permissions

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