Open Report from a form that has several filter buttons

[freespiritcherishes]

Open Report from a form that has several filter buttons to reflect filtered records of chosen filter.

This is a hard one, so if anyone can help with the code maybe my head would stop thumping.

I have a main Form called Diary. In it a Subform called MiniList. On the Main form I have 4 combo filters and 4 buttons that activate param queries. It all works lovely.

I have a Preview Report button on the main form which shows up my 2000 records in a report called FiltersReport. But when I apply any of my filters, the preview report just chucks up my message.

My code on my preview button is:

If Me![MiniList].Form.Filter = "" Then
Msgbox 'Apply a filter to the form first"
Else
DoCmd.OpenReport "FiltersReport", A_PREVIEW, , Me![MiniList2].Form.Filter

End If

The Report Filter is set to on.

I can apply a specific filter from any of my eight but I don't want to do that otherwise i would have to create 8 preview report buttons. I want one button to display filtered records from any filter that was applied to my subform by the user and any one time. Is this possible?

freespiritcherishes

 

[Trendsetter]

Is MiniList sourced from "activate param queries" and if so why not use that query to activate and populate your report?

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[freespiritcherishes]

No.
Eg. For Button 1, i set the onclick event to

'Assign parameter query SearchDID to Subform
'This prompts user for a Diary ID no. to enter
Me![MiniList].Form.RecordSource = "SearchDID"

'If there are no records

If Me.MiniList.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no diary notes with that ID"
DoCmd.Close acForm, Me.Name

another Button, Button 2 is a combo lookup Called DETypeCbo
I created a proc

Private Sub SetDETypeFilter()

Dim DETSQL As String

DETSQL = "select * from Diary"
DETSQL = DETSQL & " where DEType = '" & DETypeCbo & "'"

Me![MiniList].Form.RecordSource = DETSQL

and on its AfterUpdate event:

Private Sub DETypeCbo_AfterUpdate()

'Call subroutine to set filter based on selected Detype
SetDETypeFilter

End Sub

The other filters are structured the same and they all work. So how do I get the report to reflect the filtered records of any of the chosen filters at any time?

freespiritcherishes

 

[Trendsetter]

in the click event of a print command button

Private Sub cmdPrintMini_Click
Dim DETSQL As String
DETSQL = "select * from Diary"
DETSQL = DETSQL & " where DEType = '" & DETypeCbo & "'"
DoCmd.OpenReport "repMinList",acViewPreview ,,DETSQL
End Sub


DETSQL is the WHERE clause - check in the help file

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[freespiritcherishes]

Im wondering if I have not made my self clear. It doesn't seem logical to apply the filter direct to the onclick event of the preview report button. Having said that, I tried realising that Im no expert, and as predicted, the above code gave a massive error message which I have never seen before.

The point is, the button needs to be able to pick up on any of the filters as any of them can be chosen by the user. Tieing the filter to the button prevents the button being available to the other filters. This is the point.