Results 1 to 5 of 5
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Multiple Record Sources for a Report

    I originally had 4 departments each with it's own Database. Some of the data tables were duplicated between the different databases. I've recently designed a new database that combines the 4 databases into a single database. Each of the 4 had various reports but again there was some similarities for example each had a Birthday List and Mailing label Reports. The new design has a demographic table so the customer is only listed once and there are 4 tables for the data unique to each of the original databases. Is it possible to set the Record Source for the Report in the Load Event of the Report? That way I can use a case statement that grabs the department from a combo box on the report page and get the right data without having to design the same report 4 times for each department.

    If not the load event of the report is there another Report event that I should use?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If the fields on the reports are all the same then you would open the report with a filter on the open statement.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can have a combo box, pick the dept, and filter on it
    docmd.openreport "myReport", acViewPreview ,,"[dept]='" & me.cboDept & "'"

    or
    Code:
    select case me.cboDept
        case "VP"
           sRpt = "rptMgmt
    
        case "Accounting"
           sRpt = "rptAcctg"
    end select
    
    docmd.openreport sRpt, acViewPreview ,,"[dept]='" & me.cboDept & "'"

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    No the problem is that there are 3 tables in the query for the Report.
    tbl_person with data specific to that person. Name, DOB, Gender, Ethnicity

    there is a link to an address table that contains Mailing, Residence and working addresses for that person.

    Then there are 4 additional tables with data specific to a Department (Member Services, Senior Programming, Health Education, Potential Members). A person could be in 1,2 or 3 of those departments. Each department sends out mailers for events occurring for the people that are linked specific to the department. So If Health Education is sending out the mailer the query would link tbl_person with tbl_address with tbl_Health_Education. If it was Senior programming the same fields would be used but the link would be with tbl_Sr_Programming not tbl_Health_Education. What I'm trying to do is use 1 report as the structure and data elements to display are all the same but the link to the specific department the report is running would change.

    So I would need to change the reports record source based on which department is running the report. Either that or build the same report 4 times once for each department. Was hoping not to have to do that there are a number of reports that are similar and if a change is required in the future I could have to modify 4 reports for each change.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A simple test produced the result you are looking for - you can change the record source in the OnLoad event!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Queries using multiple sources
    By BringTheR41n in forum Queries
    Replies: 10
    Last Post: 07-10-2014, 02:41 PM
  2. Replies: 57
    Last Post: 06-20-2013, 07:09 PM
  3. Replies: 5
    Last Post: 11-26-2012, 03:12 PM
  4. Linking to multiple sources
    By jlfoster2 in forum Access
    Replies: 4
    Last Post: 07-02-2012, 05:47 PM
  5. Link to multiple sources
    By AquaChaos in forum Import/Export Data
    Replies: 4
    Last Post: 06-16-2010, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums