Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports in Access won't keep original source data

    Hi All,
    I am a fairly new Access developer and I am making a database with a menu that has a bunch of buttons on it that open reports for different shift data. So I created a report that has 3 queries or sub reports. So I need 1 for each shift, when I change queries all the forms I copied have the same data....if I go back to example shift 1 and change the queries that feed that report, and i now open shift 2 report, it now has shift 1 data. I hope I'm not too confusing in what I am explaining that is happening. I would appreciate anyone's help.

    thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,409
    Thanks
    208
    Thanked 834 Times in 767 Posts
    Lamore,

    Welcome to the lounge as a new poster!

    It would help if we knew your table structure or at least the fields in the queries.

    The easiest thing to do would be to tie your buttons to code that runs the report and use a where clause:
    Code:
    Sub PrintedBillsReport()
      
       Dim zWhere As String
       
       zWhere = "Not [EmailDocs]"
       Forms![Switchboard].Visible = False
       
       If SetDateForBills Then
         strDfltPrt = Application.Printer.DeviceName
         If UserSelectPrinterByNumber > -1 Then
          DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
         End If
       End If
       
       SwitchPrinters strDfltPrt
       Forms![Switchboard].Visible = True
    
    End Sub                   '*** PrintedBillsReport() ***
    Note: The code above will Print HOA Dues billings for all users who have not selected to have them emailed. [EmailDocs] is a check box and unchecked = False thus the where clause: Not [EmailDocs]

    You can have one routine to call the report and then attach each of your buttons to a stub that just calls that routine with the appropriate Shift setting.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Lamore,

    Welcome to the lounge as a new poster!

    It would help if we knew your table structure or at least the fields in the queries.

    The easiest thing to do would be to tie your buttons to code that runs the report and use a where clause:
    Code:
    Sub PrintedBillsReport()
      
       Dim zWhere As String
       
       zWhere = "Not [EmailDocs]"
       Forms![Switchboard].Visible = False
       
       If SetDateForBills Then
         strDfltPrt = Application.Printer.DeviceName
         If UserSelectPrinterByNumber > -1 Then
          DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
         End If
       End If
       
       SwitchPrinters strDfltPrt
       Forms![Switchboard].Visible = True
    
    End Sub                   '*** PrintedBillsReport() ***
    Note: The code above will Print HOA Dues billings for all users who have not selected to have them emailed. [EmailDocs] is a check box and unchecked = False thus the where clause: Not [EmailDocs]

    You can have one routine to call the report and then attach each of your buttons to a stub that just calls that routine with the appropriate Shift setting.

    HTH

    I have attached a copy of my database and if you look at report named "PV8 Block 1st" and "PV8 Block 2nd"...if you change the data source on one or text in a report....it copies it to all the reports instead of each report being a seperate shift.

    Thanks
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,409
    Thanks
    208
    Thanked 834 Times in 767 Posts
    Lamore,

    Here's your problem, each Report uses the same sub-reports. So if you change the queries they are based on it doesn't matter which report you open it will be based on the same data. Thus, you need to change each of the queries before running the report for a given Shift...as currently coded, see below. Then setup your report buttons Call the code below passing the shift selector value. The forgoing is conceptual of course and the details need to be worked.

    I notice that your buttons call Access Macros. I think this would work better if they called VBA code as it is more flexible as to what you can do. You can go into your queries and select the SQL view and copy the code into a VBA string variable where it can be manipulated so you can insert your parameters before running the report, essentially updating the query at run time.
    Code:
    zSQL = "SELECT [Master Seniority List (all empl].GID, [Master Seniority List (all empl].FullName," & _
                 " [Master Seniority List (all empl].DROT, [Master Seniority List (all empl].OccCode," & _
                 " [Master Seniority List (all empl].OccName, [Master Seniority List (all empl].PltSen " & _
                 "FROM [Master Seniority List (all empl] " & _
                 "WHERE ((([Master Seniority List (all empl].DROT)=" & chr(34) & zDROT & chr(34) & "));"
    The above assumes that the variable zDROT contains the value to select the proper shift.

    See here post #3 on how to save it

    I'm not as fluent on the mechanics of what I've written above but I'm sure others here can step up with the details. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    What I would have done presuming all the queries are the same except for the DROT code is to populate a single record table with the DROT code then include that table in your query.
    Then all you have to do is populate that table's DROT code and it works for all shifts.
    If there are other conditions then that is not enough, let me know your thoughts.

  6. #6
    New Lounger
    Join Date
    Aug 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ????

    Quote Originally Posted by patt View Post
    What I would have done presuming all the queries are the same except for the DROT code is to populate a single record table with the DROT code then include that table in your query.
    Then all you have to do is populate that table's DROT code and it works for all shifts.
    If there are other conditions then that is not enough, let me know your thoughts.
    Patt,
    I don't have a clue on how to even begin.......any thoughts?????

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Are all the queries that form the source of all reports the same except for the DROT code.
    Are all reports the same referencing queries with differing DROT codes?

    If the answer is yes to both, just create a table (call it tblDROTSelect) with just the DROT code in it and change one of the queries to use that table joined to the other table on DROT code.

    To cater for different reports, change the DROT code in the new table and it should work for all scenarios.

  8. #8
    New Lounger
    Join Date
    Aug 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt View Post
    Are all the queries that form the source of all reports the same except for the DROT code.
    Are all reports the same referencing queries with differing DROT codes?

    If the answer is yes to both, just create a table (call it tblDROTSelect) with just the DROT code in it and change one of the queries to use that table joined to the other table on DROT code.

    To cater for different reports, change the DROT code in the new table and it should work for all scenarios.
    I have no idea how to even begin.....do you have any sample databases?

Posting Permissions

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