Solved getting filtered records

[ClaraBarton]

I have the following code to filter the form recordsource. The records are on a subdatasheet that can also be filtered by the headers.

Select Case Me.optSales
            Case 1 'Default--All Contacts
                Me!subfrmList.Form.RecordSource = "qryContacts"
                Exit Sub
            Case 2 'Contacts with no sales
                 strWhere = "[Sales] <" & 1
            Case 3 'Contacts with Sales
                strWhere = "[Sales] >" & 0
            End Select

strSQL = "SELECT * FROM qryContacts " _
            & "WHERE " & strWhere

Me!subfrmList.Form.RecordSource = strSQL

When I want a report of the found records the filter only returns what has been filtered by the headers

 strReport = Me!lstReports

'check if report is already open and close it
        DoCmd.Close acReport, strReport
  
   Select Case strReport
        Case Is = "repContacts"
            If FilterOn = True Then
                strWhere = Me!subfrmList.Form.Filter
            Else: strWhere = ""
            End If

        Case Is = "repContactSales"
                strWhere = Me!subfrmList.Form.Filter

        Case Is = "repFollowUp"
            strWhere = ""

    End Select
    If strReport = "repContacts" And FilterOn = True Then
        strWhere = Me!subfrmList.Form.Filter
    Else: strWhere = ""
    End If

Why doesn't the filter use both conditions and how can I fix this?

 

[Gasman]

You are not setting a form filter though?, just changing the recordsource. Completely different exercise.

 

[ClaraBarton]

Oh! I hadn't noticed! the form is not filtered with the options. So... I should use form recordsource on OpenReport...
I would think that would be assumed on the report. How do I do this? OpenReport doesn't have a recordsource option. OpenArgs? Is that my only option?

 

[ClaraBarton]

Can I refer to the option sub to get a recordsource?

 

[Gasman]

Well I would have thought the easiest option would be to set the form filter with your criteria? as you thought you were doing?

 

[CJ_London]

OpenReport doesn't have a recordsource option.

one of the parameters of openreport is a (I think misleadingly named) one called filtername. This is populated with the name of a query. So if you save your strSQL or form recordsource as a temporarily named querydef, you can then either apply the filter to the strSQL before saving as a querydef, or still include it in the where parameter.

Alternatively combine your 'header' strWhere with your filter strWhere - note always better to not reuse variables for different purposes

Overall I prefer to not use filters - better to just bring through what is required with criteria so I applaud you using this method for the 'headers'. It's more efficient for large datasets, and more secure if you are filtering out records a user is not supposed to be able to see (filters can be removed)

 

[ClaraBarton]

Yes the form filter works well. But the report prints the default recordset for some reason so I get with sales, without sales, whole mess when I only want with sales. Or without. Or you know...

 

[Gasman]

Apply a filter when opening a form or report

Office VBA reference topic

learn.microsoft.com

 

[bastanu]

Can you please show us the entire procedure where you open the report? As mentioned you need to put together the two Where clauses; or you could change the record source for the report to incorporate the selection in the option group optSales assuming that is the form that launches the report and therefore open. If doing the later you just need to pass the current subform filter as the Where clause of the OpenReport method.

Cheers,

 

[ClaraBarton]

Private Sub btnReport_Click()
On Error GoTo Error_Handler

    Dim strReport As String
    Dim strWhere As String
    
    strReport = Me!lstReports

'check if report is already open and close it
        DoCmd.Close acReport, strReport
  
   Select Case strReport
        Case Is = "repContacts"
            If FilterOn = True Then
                strWhere = Me!subfrmList.Form.Filter
            Else: strWhere = ""
            End If

        Case Is = "repContactSales"
                strWhere = Me!subfrmList.Form.Filter

        Case Is = "repFollowUp"
            strWhere = ""

    End Select
    If strReport = "repContacts" And FilterOn = True Then
        strWhere = Me!subfrmList.Form.Filter
    Else: strWhere = ""
    End If
    
      
'    Me.Visible = False
    DoCmd.OpenReport _
        ReportName:=strReport, _
        View:=acViewPreview, _
        WhereCondition:=strWhere, _
        OpenArgs:=Me!subfrmList.Form.RecordSource
        
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
    Resume Exit_Procedure
    Resume

End Sub

This is the report code. It does not use the recordset that's showing. It uses the default All Contacts.
the strWhere is ([subfrmList].[Ck] Not In (0)) which returning a column of checked checkboxes.
I now understand that the option box is changing the recordset and not filtering so how to I get the report to use the recordset?

 

[bastanu]

Clara,

You would need to apply both "filters" to the report; the easiest way I think would be to change the report's record source query "qryAllContacts" to another one that is being dependant on the optSales option group. Add a calculated field to the original query SalesOpt: Forms!frmYourForm!optSales. Now in the criteria rows for this new field add 1, 2 and 3 (on three different rows, one beneath the other); go to the Sales field now and add corresponding criteria (leave blank for 1, <1 for 2 and >0 for 3). Save the query with a different name and set it as the report recordsource. Now this would take care of the first "filtering", your other code should account for the second (manual filtering of the subform).

Cheers,

 

[ClaraBarton]

Thank you for your help. After sleeping on my problem I realized the easiest fix was to change my recordset to a filter. I wouldn't have figured this out without you all!