Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Indianapolis, IN
    Posts
    2

    Question Unanswered: Simple question -- Save current filter as query (realtime)

    I have what should be a simple question to pose. I am a Helpdesk Tech. stuck working on a database for someone in Access 97. I have a form where the users can go into a view mode and query the database for certain information (simple enough). They are using a QBF (Query By Form) and then applying that filter. I then have a button on that form they can click that needs to print out only the reports of the records which fit the QBF. I can see a few ways of getting this result, but have had no luck with any of them. My questions are these:

    1. When a user creates a QBF and clicks apply filter, how can I save that filter as a query using code or a macro?

    OR

    2. What is the name of the current filter? In otherwords, when a user creates a QBF and clicks apply filter, MSAccess is applying that filter which it has stored somewhere -- what is the code name of that filter? (eg. Me.CurrentFilter)

    OR

    3. How can I allow a user to filter a form and then print the filtered list (not all of the records, not just one, but the ones that matched the filter).

    Answering any of these three questions will lead me to being able to solve the problem. Thank you so much!

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Hello

    1. When a user creates a QBF and clicks apply filter, how can I save that filter as a query using code or a macro?
    The following code is a good start, you may want to use a personnal button to do so:

    The following code USE the filter and CREATE AN ACCESS QUERY:

    Code:
    Private Sub Commande2_Click()
    
        Dim qry As QueryDef
        Dim strSQL As String
        
        'This is just use to show the filter
        MsgBox Me.Filter
    
        'Now, create the SQL query using the filter as the WHERE condition
        strSQL = "SELECT * FROM TestNumText WHERE " & Me.Filter
        
        'Show thw SQL command
        MsgBox strSQL
    
        'Now, let's take a look at the number of queries in the database
        'It is really not USEFULL, it is just to show
        MsgBox CurrentDb.QueryDefs.Count
        
        'Add the qry to the database
        If IsError(CurrentDb.QueryDefs("QRY_MYQUERY").Name) Then
            Set qry = CurrentDb.CreateQueryDef("QRY_MYQUERY", strSQL)
        Else
            MsgBox "ALREADY EXIST QRY_MYQUERY"
        End If
        
        'Let's take a look at the number of queries AFTER our adding ;)
        MsgBox CurrentDb.QueryDefs.Count
        
        
        'Another way would be to use Recordset
        Dim rst As DAO.Recordset
    
        'Open the recordset using the SQL
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        'See how many records returned (just for fun ;) )
        MsgBox "Number of record returned: " & rst.RecordCount
        
        'To browse field of the recordset, use rst.Fields(ID_FIELD).value and rst.MoveNext
        
        
        
    End Sub
    2. What is the name of the current filter? In otherwords, when a user creates a QBF and clicks apply filter, MSAccess is applying that filter which it has stored somewhere -- what is the code name of that filter? (eg. Me.CurrentFilter)
    It is Me.Filter, and Access do NOT change the source of the form, Filter is supported by recordset

    3. How can I allow a user to filter a form and then print the filtered list (not all of the records, not just one, but the ones that matched the filter).
    You can use the last code, and print the query, or run a report or use the last remark line I provided and use a personnal Recordset to do whatever you want with it

    JefB - hope it helps

  3. #3
    Join Date
    Sep 2002
    Location
    Indianapolis, IN
    Posts
    2

    Thumbs up It Worked

    With a little tweaking to make it fit my database, the code you supplied worked perfectly! Thank you very, very much!

    Peace,
    BHunter_SdM

  4. #4
    Join Date
    Jul 2012
    Posts
    2
    Hi
    I have the exact same question, and it's very helpful that there is already an answer here--thanks! I would like to use this code in a very similar manner. I'm trying to use the filter by form function as almost a search function. I want users to be able to search something using filter by form, and then, if they choose, by clicking a button, the filter is saved as a query, a report is opened using that query, and then this report is opened in an email. I can't figure out how to do this--I've figured out the code that I need to add on, but I seem to run into trouble at this line--

    If IsError(CurrentDb.QueryDefs("QRY_MYQUERY").Name) Then

    The error message says "Item not found in this collection"

    Any help is greatly appreciated

  5. #5
    Join Date
    Sep 2013
    Posts
    3

    Print All Form Records based on parameter query

    I have the same question as BHunter's #3, but not sure I did things right. My form is populated based on a parameter query. I have a button to print one form only (uses an ON CLICK event procedure to open a report and print the current record using the following code:

    Private Sub cmdPrint_Click()
    Dim strWhere As String

    If Me.Dirty Then 'Save any edits.
    Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
    MsgBox "Select a record to print"
    Else
    strWhere = "[LoanNumber] = """ & Me.[LoanNumber] & """"
    DoCmd.OpenReport "r_CI_LoanRecords", , , strWhere
    End If
    End Sub

    Now I want a button to print all the returned form records. I didn't use the "Filter" options. Can it be done anyway using the current parameter query I have in place?

    Any help is greatly appreciated.

Posting Permissions

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