Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    5

    Question Unanswered: Changing Report Record Source Programatically

    I am a newbie, former FoxPro developer. I have several reports that could be changed to one report if I could change the query and title programatically before the report is rendered. Any ideas? Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could pass it in as query args, you could use a config table and reference it before loading the report, you could change "Me.Recordsource" in the report's onOpen event, you could use a parameter query fed by a form, you could use global variables...

    That's a few options, which one is best depends on your specific needs I suppose...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2006
    Posts
    5
    Thanks, I thought there would be many ways to solve this.

  4. #4
    Join Date
    Oct 2006
    Posts
    5
    I tried to create a variable in the calling procedure but the report could not see the new variable. Can you tell me how to create a public variable that I can reference in the OnOpen event. So I tried to create a config table to update prior to calling the report and then reference the table field name but that did not work either. Do you have any examples on how to reference a table field name from the OnOpen event. I would appreciate any help!

  5. #5
    Join Date
    Oct 2006
    Posts
    5
    Ok, I was able to use the Report Config Table and linked variables in the report to [Forms]![frmReport]![txtQuarter]. I also was able to change the record source with the "On Open Event" using this code: 'Me.RecordSource = [Forms]![frmReport]![txtQueryName]'. So it works this way but I still would like help with using a global or public variable. Thanks...

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not sure, but i don't think you can get there with just a global.

    in case it's of interest, here's another (DAO!) approach.
    your report always looks at qryForReport and you modify the query with something like:
    dim qdef as DAO.querydef
    set qdef = currentdb.querydefs("qryForReport")
    qdef.sql = "SELECT blah FROM here;"
    qdef.close
    set qdef = nothing

    you could have a bunch SQL strings in a table and switch them in as needed.

    izy
    Last edited by izyrider; 10-27-06 at 14:56.
    currently using SS 2008R2

  7. #7
    Join Date
    Oct 2006
    Posts
    5
    Great! Thanks for you help. This gives me some good ideas!

Posting Permissions

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