Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2016
    Posts
    22

    Answered: Print Records in Continuous Subform to an Existing Report

    I have a main form with an unbound subform. There are two date boxes on the main form that when entered will run a query and update the subform with any records within that date range. Then there is a command button than when clicked will run another query on the results already in the subform and display any duplicate records. I have added another command button and would like to be able to print whatever records are displayed (original records, or records from the first query, or records from the last query) in the subform to an existing report. I have tried the code below. It opens the report but with all of the records. I used the code below to export whatever records are in the subform to Excel and it works fine. Any help would be greatly appreciated with the code below or a better method to print whatever records are currently displayed in the subform. The report does have an existing record source for a different purpose.

    Code:
    Me.frmDuplicatesSfrm.SetFocus
    [frmDuplicatesSfrm].Form![PerformID].ColumnHidden = False
    DoCmd.GoToControl "PerformID"
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.RunCommand acCmdCopy
    DoCmd.OpenReport "rptPerformData", acViewPreview, , , acWindowNormal
    I have also posted this question at the board below but have not received any replies.
    http://www.accessforums.net/editpost...&postid=378175

  2. Best Answer
    Posted by weejas

    "The required code turned out to be simpler than I expected. It helps that your queries do most of the heavy lifting, and they all have the same columns. Where I was thinking you'd need to add filter clauses, all that's required is one line of code in the report's On Open event, and slight change to the print button's action.

    In the report's On Open event, set the following line:
    Code:
    RecordSource = OpenArgs
    Then amend the DoCmd method in cmdPrint's On Click event as follows:
    Code:
    DoCmd.OpenReport "rptPerformDataExp", acViewPreview, , , acWindowNormal, frmDuplicatesSfrm.Form.RecordSource
    Whichever query is being used as the source for records in the subform will then be used for the report."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 35
    you want to swap out the subform .sourceobject
    if you want to see all records, set the subform.sourceobject=table.mytable
    if you want to see records within the date, set the subform.sourceobject=query.qs2Dates
    etc.

    same with the report.
    the report would look at the subform.sourceobject and get the data from it.

    I have a combo box:
    CAPTION, QRY
    all records, qsAll
    recs between dates,qsBetweenDates
    missing records, qsMissing

    the user sees the caption, but the subfrom is assigned the query.
    same with reports.

  4. #3
    Join Date
    Dec 2016
    Posts
    22
    While I'm very appreciative of ranman256's response, I don't understand it and have noticed that he leaves a response and then doesn't follow-up with anyone who states they don't understand his responses.

    Hoping there is someone else that can assist with the code I provided above.
    My form has the possibility of displaying data from three different queries - one query when it's loaded, a different query if the date text boxes are populated, and a third query if the command button is checked, therefore, just changing the report's sourceobject will not work. I really need the code to select/copy the records in subform and print them to the report.

    Again, thank you ranman256 for your response.

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    The code that you've posted appears to be selecting a data set and copying it. That sounds reasonable for exporting data to Excel, but reports don't work that way.

    If you want to limit the records displayed by a report, you need to use the WhereCondition argument (fourth argument) of the OpenReport method:
    Code:
    DoCmd.OpenReport "rptPerformData", acViewPreview, , "[RecordDate] BETWEEN #" & Format(DateControl1, "yyyy-mm-dd") & "# AND #" & Format(DateControl2, "yyyy-mm-dd") & "#" , acWindowNormal
    Change [RecordDate] to the name of the column in your report's data source that has the relevant date, and DateControl1 and DateControl2 to the names of your actual form controls.

    I've wrapped the date controls in Format functions because Access has an annoying habit of reinterpreting dates in the US format if it can get away with it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #5
    Join Date
    Dec 2016
    Posts
    22
    weejas,

    Thank you so much for your assistance. It is so very close but I think I didn't describe the situation correctly regarding the third query.

    Query1 is the record source for the form when it opens
    Query2 executes in the AfterUpdate event for the second date text box and displays records that have a ReportingDate between the two dates in text boxes 1 and 2.
    Query3 executes when a command button on the main form is clicked and runs a Duplicate query on Query2 to display records within that date range but are duplicates based on three fields.

    When dates are entered and the command button is clicked, the form displays the correct records. How do I implement the third portion (Query3) in the WhereCondition?

    So very grateful for your assistance.

    Margaret

  7. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    Can you upload a zipped copy of the database with some sample records? I think I understand what you need, but it's easier to deal with an actual database rather than an impression of a description.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  8. #7
    Join Date
    Dec 2016
    Posts
    22
    Thank you for your assistance. Attached is a sample database. My goal is to be able to print whatever records are displayed in the subform, whether records when the form loads, or after the first query (after From and To dates are entered and after event runs) or after the Check button is clicked.Test Database.zip

  9. #8
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    The required code turned out to be simpler than I expected. It helps that your queries do most of the heavy lifting, and they all have the same columns. Where I was thinking you'd need to add filter clauses, all that's required is one line of code in the report's On Open event, and slight change to the print button's action.

    In the report's On Open event, set the following line:
    Code:
    RecordSource = OpenArgs
    Then amend the DoCmd method in cmdPrint's On Click event as follows:
    Code:
    DoCmd.OpenReport "rptPerformDataExp", acViewPreview, , , acWindowNormal, frmDuplicatesSfrm.Form.RecordSource
    Whichever query is being used as the source for records in the subform will then be used for the report.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  10. #9
    Join Date
    Dec 2016
    Posts
    22
    weejas,

    My apologies for not responding sooner. Your solution above works perfectly. I now need to alter some other form's code that utilizes the same report. Unfortunately, the awesome code you provided doesn't work when the subform is filtered in my other forms. On to my next issue to resolve and learn more. Thanks so much.

    Margaret

  11. #10
    Join Date
    Dec 2016
    Posts
    22
    weejas,

    Is there a way to disregarding the report's OnOpen event from the other forms that use the same report but don't need to change the record source?

  12. #11
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    You're welcome!

    You can't disregard the report's OnOpen event once it's coded, but there are a number of ways around it. These all require a conditional statement of some kind, and either defining and populating global variables, or manipulating the OpenArgs value.

    For example, you could define a global variable Filter_Rep as a Boolean. Each time you need to open the report, you set the value to True, and pass the required record source in the OpenArgs arguement, or to False and don't pass OpenArgs. In the report's OnOpen event, you only use the value in OpenArgs if Filter_Rep = True.

    Alternatively, you could pass the record source or "UNFILTERED" as OpenArgs. Then, in the report's OnOpen event, you check the value in OpenArgs. If it's "UNFILTERED", just open the report. Otherwise, update the report's record source.

    The easiest way is to have two copies of the report, one without the OnOpen coding. Then you just call whichever one you require, passing OpenArgs as necessary.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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