Results 1 to 14 of 14
  1. #1
    AMCUser is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    6

    Access Reports - Applying a subform filter

    Good Day

    I have what seems to be a common issue in forums but I am unable to find a suitable solution.

    I have attached the DB created and posted by a forum user (sorry I cant track where I got it from)
    The database comprises of a search form that dynamically creates a search filter and then updates the subform based on the multiple filter criteria based on the main form fields.

    I now wish to apply this filter and print a report based on these same filtered results.
    --------------------------------------------------------------------------------------------
    The Search Button code looks like this.
    Private Sub btnSearch_Click()



    ' Update the record source
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

    ' Requery the subform
    Me.frmsubClients.Requery
    End Sub
    --------------------------------------------------------------------------------------------

    On the PrintPreview button I would like to try something like:


    Private Sub btnPrintPreview_Click()

    DoCmd.OpenReport "rptClients", acViewPreview, #insert the WHERE filter here if possible based on the same smple code as above#

    End Sub

    I have tried plenty of unsuccessful attempts and would really appreciate your input.

    Many Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I haven't looked at the sample, but you can use this technique (close to what you have, but one position off):

    http://www.baldyweb.com/wherecondition.htm

    It appears that BuildFilter returns a string such as "WHERE FieldName = Value". Since the wherecondition argument of OpenReport is an SQL WHERE clause without the word "WHERE", you can use the Mid() function to strip it off and the rest of the string should work as a wherecondition.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    AMCUser is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    6
    Thanks Pbaldy

    This is great for one field, the problem I have is that Buildfilter contains around ten filters & I don't know how to write the VB code for opening a report to use this filter.

    I Imagine it would be something like:
    DoCmd.OpenReport "rptClients", acViewPreview, "FieldName = '" & Me.ControlName & "'"
    but how do I specify mutiple field names = to mutiple control names?


    In the "Search function the code
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

    Tells the system to update the Recordsource.
    Do I need to update the recordsource for the report when opening it, and if so how?

    Thanks Again

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Like I said, I would just use BuildFilter, since it already has your desired criteria (I assume), with the Mid() function to get rid of the word "where ". Try:

    DoCmd.OpenReport "rptClients", acViewPreview, , Mid(BuildFilter, 7)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    AMCUser is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    6
    Thanks a mil

    I'll give that a try over the weekend. Much appreciated as you have know doubt seen I'm a tad lost in amongst the VB code.
    Cheers

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem; post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Join Date
    May 2010
    Posts
    339
    Change your record source on your subform to qryclientdata

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Access_Blaster View Post
    Change your record source on your subform to qryclientdata
    Perhaps I'm being dense, but how does that solve the problem? The whole point of this is to include criteria on top of that query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Join Date
    May 2010
    Posts
    339
    I down loaded his sample, and changed his record source and his search field worked...

  10. #10
    AMCUser is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    6
    Genius Pbaldy - Works like a charm

    Thanks for a really simple work around.
    I've attached the completed file for those that want to reference it for later.

    Cheers

  11. #11
    AMCUser is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    6
    Quote Originally Posted by Access_Blaster View Post
    I down loaded his sample, and changed his record source and his search field worked...
    Not sure if that makes any diffrence as the VB Code
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

    Sets the Recordsource and inputs the filters into the query at runtime.


  12. #12
    Join Date
    May 2010
    Posts
    339
    Ok so I am wrong on my post. I still don't know why you have this as your record source in your subform.
    Code:
    SELECT tblClients.ClientID, tblClients.LastName, tblClients.FirstName, tblClients.Age, tblCompanies.CompanyName, tblCountries.CountryName, tblClients.FavColor FROM tblCountries INNER JOIN (tblCompanies INNER JOIN tblClients ON tblCompanies.CompanyID=tblClients.CompanyID) ON tblCountries.CountryID=tblClients.CountryID;
    Then change it to this in vba?
    Code:
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter
    I guess i'm just dence

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad it worked for you AMC!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    AMCUser is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    6
    Quote Originally Posted by Access_Blaster View Post
    Ok so I am wrong on my post. I still don't know why you have this as your record source in your subform.
    Code:
    SELECT tblClients.ClientID, tblClients.LastName, tblClients.FirstName, tblClients.Age, tblCompanies.CompanyName, tblCountries.CountryName, tblClients.FavColor FROM tblCountries INNER JOIN (tblCompanies INNER JOIN tblClients ON tblCompanies.CompanyID=tblClients.CompanyID) ON tblCountries.CountryID=tblClients.CountryID;
    Then change it to this in vba?
    Code:
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter
    I guess i'm just dence
    The Recordset is set by the VBA Code.
    If you want delete whatever you see currently set as the record source - you can leave this blank if you wish. The code sets the data hence that is why you see the long code.

    It is far simpler to put a large filter in the VB code than it is to do it in the query itself.

    Hope that makes sense

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

Similar Threads

  1. Applying a find/replace function
    By Arr in forum Programming
    Replies: 2
    Last Post: 10-12-2009, 12:28 PM
  2. Replies: 7
    Last Post: 05-24-2009, 10:24 AM
  3. Replies: 1
    Last Post: 03-01-2009, 09:53 AM
  4. Applying a filter to a combo box
    By bugchaser in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 02:37 PM
  5. command button to filter a subform issue -
    By countdrako in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 11:58 PM

Tags for this Thread

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