Report on filtered datasheet? (1 Viewer)

Gasman · Jul 13, 2017

Hi all,
I have a simple form with an unbound combo on it.
In a subform I have a datasheet linked to the combo.
In the datasheet I can filter further for date and let's say telephone number, but any of the fields that are shown.
I wish to put a report button on the form to produce a report, but that only shows the data currently filtered on the datasheet.?

I'm fine with filtering for the combo for the report, but how do I add the rest of the filter criteria?

As an aside, if I filter on a date, that works fine, but if I then filter on a telephone number, I am presented with numbers that do not belong to the records already filtered?, and selecting an incorrect one filters the data to an empty datasheet.
Is there a way around this, as I must admit I thought it worked as per Excel?

TIA

Gasman · Jul 13, 2017

Solved !, well partially.
The report part is solved, still working on the multiple filter issue.

Code:

DoCmd.OpenReport "rptBTCalls", acViewPreview, , Me.Controls("sfrmlBTCalls").Form.Filter & "AND Caller='" & Me.txtCallerName & "'"

isladogs · Jul 13, 2017

Hi Paul

Looks like you need a space before "AND ...

I find it easier to construct the filter criteria as a string before the open report line.
Also the first part looks wrong to me ... though I think you are saying that bit works ...

Anyway I'd do something like

Code:

Dim strCriteria As String

strCriteria ="BTCallNumber = '" & Me.cboCallNumber & "' AND Caller='" & Me.txtCallerName & "'"

DoCmd.OpenReport "rptBTCalls", acViewPreview, , strCriteria

Gasman · Jul 13, 2017

Hi Colin,

Yes I can see that there should be a space before the AND, however it works, but I'll correct it in case of further changes.

The filter for the form ends up like

Code:

((([qryBTCalls].[CalledDate] In ("03/07/2017","07/07/2017")))) AND ([qryBTCalls].[CallingNumber]=14953113219)

however I have discovered a problem as the filter is not cleared when the form closes, and is present on reopen, although no filter shows in the datasheet, so will need to work out how/when to clear it.

TIA

isladogs · Jul 13, 2017

If you go with the strCriteria idea, you can set strCriteria ="" when you close the form

Another reason why I use that approach is so I can use a debug.print line whenever I have trouble with SQL for multiple filters

Gasman · Jul 13, 2017

Yes, I've gone that way as well mainly due to the fact there might not be a form filter?:banghead:
I now clear the subform filter on main form load.

Code:

Private Sub cmdReport_Click()
Dim strFormFilter As String, strReportFilter As String
strReportFilter = "Caller='" & Me.txtCallerName & "'"
strFormFilter = Me.Controls("sfrmBTCalls").Form.Filter
If strFormFilter <> "" Then
    strReportFilter = strFormFilter & " AND " & strReportFilter
End If

DoCmd.OpenReport "rptBTCalls", acViewPreview, , strReportFilter
End Sub

Report on filtered datasheet? (1 Viewer)

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread