Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Viewing a Filtered Query (A2K)

    SCENARIO

    I have a form that has a filter applied when it is being used that can specifically narrow down the records from 1461 to 17. I can then Preview/Print a report using the following code:

    Private Sub PreviewReport_Click()
    Dim strCustomerID As String
    Dim rpt As Access.Report
    Dim strFilter As String
    strCustomerID = Me![AGMProjName] ' filter key
    strFilter = "[AGMProjName] = " & Chr$(39) & strCustomerID & Chr$(39) ' filter key
    Debug.Print "Filter: " & strFilter
    DoCmd.OpenReport "rpt Estimating - PrintOut", acViewPreview ' report name
    Set rpt = [Reports]![rpt Estimating - PrintOut] ' report name
    rpt.FilterOn = True
    rpt.Filter = strFilter
    ErrorHandlerExit:
    Exit Sub
    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    On Error GoTo Err_Command163_Click
    Dim stDocName As String
    stDocName = "rpt Estimating - PrintOut" ' report name
    DoCmd.OpenReport stDocName, acPreview
    Exit_Command163_Click:
    Exit Sub
    Err_Command163_Click:
    MsgBox Err.Description
    Resume Exit_Command163_Click
    End Sub

    This works perfectly.

    PROBLEM

    I would like to view the "filtered" query at this point in processing time in order to manually identify certain records that will be then used for an even narrow selection to be reported. I use a print/don't print checkbox in order to identify the specific records I want. If I open the source query at this point, in displays all 1461 records, not the 'filtered' 17.

    QUESTION
    How can I 'call up' the filtered query in order to check the records I wish to print? I messed around with the report version, but got nowhere.


    I'd appreciate any help from you kind folks out there. Thanks in advance,
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Viewing a Filtered Query (A2K)

    You can't. Your query isn't being filtered, the recordset of the report is being filtered and there's a big difference. The report is getting *all* the records in the query but the filter is then being applied to restrict the records printed. Without knowing more about your database, it's hard to give much useful advice here. You could pass in a pre-filtered query, which sounds more like what you need to do, but it sounds like the process of picking records to print is somewhat manual, so I don't know what to suggest. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing a Filtered Query (A2K)

    Hi Charlotte,

    Thanks for your response. This actually is a tough situation to explain. The whole process is happening from a form that I'm selecting reports from. When I open the menu-like form, all the records are available. I then apply the filter at form level and then call for the report. I guess I was just wishfully thinking that I could also have access to the query in filtered mode. Does that make sense? Probably not, but thanks again for your input.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Viewing a Filtered Query (A2K)

    Perhaps you could explain how you apply the filter at form level.

    It is possible to use a multi-select list box on a form to construct the filter for the report. The basic idea is like this, but it will of course have to be adapted:

    Dim strWhere As String
    Dim i As Long
    ' Loop through list box items
    For i = 0 To Me.lstMyListBox.ListCount - 1
    If Me.lstMyListBox.Selected(i) = True Then
    strWhere = strWhere & "," & Me.lstMyListBox.ItemData(i)
    End If
    Next i
    If strWhere = "" Then
    ' Nothing selected - get out now!
    Exit Sub
    Else
    ' Remove leading comma
    strWhere = Mid(strWhere, 2)
    ' construct WhereCondition
    strWhere = "FieldName In (" & strWhere & ")"
    ' Open report
    DoCmd.OpenReport "rpt Estimating - PrintOut", acViewPreview, , strWhere
    End If

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing a Filtered Query (A2K)

    Good Day Hans,

    Once more do I try and explain myself. Well, not myself, but my problem.

    Scenario

    I've got a query.
    It has a 100 records representing individual items.
    These 100 records are categorized by a code into 10 different groups.

    I've got a form.
    It has a combo box which selects or 'filters' any one of the 10 groups.
    It has 15 buttons which allow me to print different reports based on the group that I have just selected or 'filtered'.

    Problem/Wish
    To access the current 'filtered' group's view of the query so that I can check off certain records within that group for further, immediate actions before the filter is reset and I've 'lost' the selected group.

    Hope this helps,
    Cheers,
    Andy

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing a Filtered Query (A2K)

    You could add the filter to the record source of the form as a where condition.
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing a Filtered Query (A2K)

    Francois, thanks for jumping in to my rescue.

    Let's hope nobody is up this early watching what I'm doing to myself.

    When I open the form, the record source is unfiltered and there is no "where" condition.

    1. Exactly how would I apply this dynamically during and/or after doing my selection in the combo box?
    2. Would I then do an "Open" query at this point?

    Sorry about the seemingly blank expression on my face,
    Cheers,
    Andy

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing a Filtered Query (A2K)

    Maybe you can open a new form with in the on open event of this form some code to set the recordsource to what is selected in the other form.
    Here a sample of what I mean.
    Open the form Namen, click on the set filter button and click on the open form button.

    Making this sample, another idea came in. Can't you just switch over to datasheet view ?
    Francois

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing a Filtered Query (A2K)

    Francois,

    Don't you just hate it when you keep banging into the forest while walking through the trees? I came up with a variation on your theme which works perfectly. Basically I created a datasheet subform which on put on my regular form and simply linked it on the matching key. As Homer Simpson would say, DUH!
    Thanks ever so much for clearing my head for me,
    Cheers,
    Andy

Posting Permissions

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