Results 1 to 7 of 7
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Dynamic queries and subreport filters

    Is it possible to create a dynamic query for the main container of a report, then add additional criteria to the original query for 2 different subreports? I can't seem to figure out how. The report is a letter sent to selected individuals that have made contributions of either cash, or of items. The query selects the individuals based on the date of the donation. The two subreports are made visible only if the person gave cash(item_donations.amount for subreport1) or donated items (item_donations.[item type] for subreport2). The main report contains the return address, salutation, and the text of the letter. The subreports lists what the individual gave. So in essence, the query selects the individual based on the date of donation, then additional criteria are applied to determine if the subreport is visible or not.
    I have tried to set up a filter on the subreport without success. Not sure where to go with this. If I leave the record source blank on the subreports I get #name? errors in the fields of the sub report. If I set the subreport record source set to the name of the query and I have the master/child links both set to the primary key [Id_numberpk] I get all dates and all contributions i.e. no filtering at all.

    Here is the code to set up the report

    Code:
    Private Sub Cmdletter_Click()On Error GoTo Err_Cmdletter_Click
    Dim rs As dao.Recordset
    Dim qdf As QueryDef
    Dim dbs As dao.Database
    Dim startdate As Date
    Dim enddate As Date
    Dim DateRange As String
      
            startdate = Me.Text2.Value
             enddate = Me.Text4.Value
             DateRange = "item_donations.date >= #" & startdate & "# AND item_donations.date <= #" & enddate & "#"
         If IsNull(Me.Text2.Value) Then
             MsgBox " Please enter the range of dates for the report", vbOKOnly, "Missing dates"
             Me.Text2.SetFocus
         ElseIf IsNull(Me.Text4.Value) Then
             MsgBox " Please enter the range of dates for the report", vbOKOnly, "Missing dates"
             Me.Text4.SetFocus
         Else:
       Set dbs = CurrentDb
       strsql = "SELECT Org_zip, Org_name, Org_phone, Org_CEO, Org_fax, Org_website, logopath, Org_address1, Org_address2, Org_city, Org_state, " & _
       "Contact_Info.Salutation, Contact_Info.Spouse_First_Name, Contact_Info.Display_Name, Contact_Info.ID_numberPK, Contact_Info.Last_Name, Contact_Info.First_Name, Contact_Info.Phone1," & _
       "Contact_Info.Contact_Name, Contact_Info.Address1, Contact_Info.Address2, Contact_Info.City, Contact_Info.State, Contact_Info.Zip_Code," & _
       "Item_Donations.Date, Item_donations.ID_number_fk, Item_Donations.Amount, Item_Donations.[Item type], Item_Donations.[Approximate Cash Value], Item_donations.[restricted use]" & _
       "FROM Files_settings, Organization_info, Contact_Info INNER JOIN Item_Donations ON Contact_Info.ID_numberPK = Item_Donations.ID_number_FK " & _
       "WHERE item_donations.date >= #" & startdate & "# AND item_donations.date <= #" & enddate & "# ; "
       Set qdf = dbs.CreateQueryDef("Thank_you_letter_query", strsql)
       Set rs = qdf.OpenRecordset()
        End If
          
    
    
    rptname = "Tax info letter"
    prnttype = "Document"
    DoCmd.OpenReport rptname, acViewPreview, "Thank_You_letter_Query", DateRange, acWindowNormal
    
    
    Set rpt = Reports(rptname)
    Set rpt.Printer = Application.Printers(Selprnt(prnttype))
    rpt.Printer.Orientation = acPRORPortrait
    
    
    Me.Refresh
    
    
    DoCmd.Close acQuery, "Thank_You_letter_query", acSaveNo
          dbs.QueryDefs.Delete ("Thank_You_letter_query")
          Set rpt = Nothing
          Application.Printer = Nothing
          Set qdf = Nothing
          Set rs = Nothing
    
    
    Exit_Err_Cmdletter_Click:
       Exit Sub
       
    Err_Cmdletter_Click:
    MsgBox Err.Description
    Resume Exit_Err_Cmdletter_Click
    
    
    End Sub
    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I follow, making the sub reports visible isn't the issue, it's how to get the correct records. Have you tried setting each sub to it's own query that gets the cash or gift value from the main report? Or from the form that opens the main report? If the latter, that form has to be open when the report opens. Not sure why you refresh the form, or even why you create a recordset that you don't use. There are some traps you might fall into one day, such as if your code errs out before the qdf is deleted, in which case an error will be raised next time you try to create it. I'm also not a fan of continually creating/deleting a qdf, believing that it promotes corruption. I'd use a temp qdf (you simply give it a name of "") or just construct the sql as you are doing and make it the record source of the opening object (report or form). As far as that goes, I suppose it's another way to populate your subreports - testing that the report is loaded, setting their recordsource and refreshing the subs. Many ways to skin a cat (who does that anyway?).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Quote Originally Posted by Micron View Post
    If I follow, making the sub reports visible isn't the issue, it's how to get the correct records.
    That is correct.

    Have you tried setting each sub to it's own query that gets the cash or gift value from the main report?
    That was my original plan. The command button for this procedure sits on a form with 2 unbound controls for the user to enter the dates to query (text2 and text4). I was unable to figure out a way to get the values for text2 and text4 into the query for the subreport so when I run the report I end up with All dates returned in the subreport. Passing the date values from the form into the query of the subreport would be the easiest solution, just not sure how to do it.

    Or from the form that opens the main report?
    The form only has the dates to query in it, no other fields.

    If the latter, that form has to be open when the report opens.
    It is open

    Not sure why you refresh the form,
    Me neither- stray code; I'll get rid of it

    or even why you create a recordset that you don't use.
    The query selects the main report data, based on the date criteria from the form- it works fine.

    There are some traps you might fall into one day, such as if your code errs out before the qdf is deleted, in which case an error will be raised next time you try to create it. I'm also not a fan of continually creating/deleting a qdf, believing that it promotes corruption.
    I've run across that error and was hoping to devise code to delete the query in case of error but haven't tried to do that yet.

    Thanks for the help.

  4. #4
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Ok, now its time to admit it- I was overthinking this. I just put criteria in the date field of the subreport queries referring back to the form and it worked fine. I solved my problem.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The query selects the main report data, based on the date criteria from the form- it works fine.
    Glad you solved it, but just to be clear and differentiate between what a query is and what a recordset is. You correctly create then set the rs
    Code:
    Set rs = qdf.OpenRecordset()
    You don't refer to rs again until you close it, and it doesn't seem to be a global variable that you can use elsewhere, thus I'm saying you don't use rs at all. In fact, I don't see the need for continually creating/deleting the qdf when you could just pass the sql to the report as its recordsource. Not to worry, but you did say you were making it more difficult than necessary. I just thought I'd agree with you.

  6. #6
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Glad you solved it, but just to be clear and differentiate between what a query is and what a recordset is. You correctly create then set the rs
    Code:
    Set rs = qdf.OpenRecordset()

    You don't refer to rs again until you close it, and it doesn't seem to be a global variable that you can use elsewhere, thus I'm saying you don't use rs at all.
    OK, I see what you are saying. I copied that whole snippet of code from another part of my database and didn't realize the rs part was no longer used.

    In fact, I don't see the need for continually creating/deleting the qdf when you could just pass the sql to the report
    Just trying to cement the concept in my brain- and not doing a very good job of it!

    Thanks for your input!

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just trying to cement the concept in my brain- and not doing a very good job of it!
    depends on if the view is report view or not. Can't set report recordsource for just any view once it has opened; probably just report view allowed. So something like

    DoCmd.OpenReport "rptNameOfYourReport", acViewPreview, , , , strSql

    then in the report open event

    Me.RecordSource = Me.OpenArgs

    You might need to lookup OpenArgs to follow the logic.
    Last edited by Micron; 04-10-2018 at 07:32 PM. Reason: added info

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

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2017, 11:28 PM
  2. Replies: 1
    Last Post: 07-06-2016, 03:35 PM
  3. Replies: 1
    Last Post: 04-15-2013, 10:02 AM
  4. Embedding Dynamic Queries in Access Form
    By IainMc in forum Forms
    Replies: 5
    Last Post: 12-21-2011, 12:09 PM
  5. Replies: 5
    Last Post: 05-26-2011, 04:35 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