Open Report from a form that has several filter buttons

[freespiritcherishes]

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

 

[dhookom]

How do you apply filters to the subform? Is this done by editing the filter property of the subform? Do you have a bit of code you would like to share?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[freespiritcherishes]

ok. 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 the chosen filter?

freespiritcherishes

 

[dhookom]

You are not setting the Filter property of the subform in any line of code that I can see. If you don't set the filter property then trying to retrieve the filter property will be of no value.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[freespiritcherishes]

but if i do that, i am restricting my report button to one filter? I have several filters on one main form with a subform. I click a button or a combo and the records on my subform change. Great, but how do i get the report to pick up on the applied filter, which ever one it is i choose?

 

[dhookom]

You could store your "where" condition in a text box on your main form. Continue to concatenate additional wheres with an " AND " in between. Then use this value in the where clause of your docmd.openreport.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[freespiritcherishes]

mmmmm... now that seems promising. Sounds like a big piece of code to me tho.. couldn't give me a snippet to get me started.. ?

Main Form "Diary"
Subform "MiniList"

Filters and their names: PQ mean Param Query, Cbo means combo

PQ - SearchDID
Cbo - DEType
PQ - BetweenDates
Cbo - FilterbyContact
Cbo - SubjectCbo
Cbo - DlkupCbo
PQ - FilterbyContent
PQ - SearchbyDocLinks

Requirement, piece of code for the onclick event of a preview report button that displays the filtered records of any of the filters. The word Loop comes to mind, but I have no idea where to start.

Experts engage your missiles!

 

[assets]

apply filter on the main form. also do a requery on the subform (on Activate).

Never give up never give in.

 

[dhookom]

Is all filtering based on the existing values of your controls on the main form? If so, I suggest you just loop through the controls and build a where string.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboDEType) Then
strWhere = strWhere & " AND DEType=" & Me.cboDEType
End If
'do the same for each control
If Not IsNul(....

End If


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[FancyPrairie]

The FAQ faq181-5497 contains a function that will return the Where clause for you (without the word Where). You could use the results to filter.

This functions works for 0 to many listboxes, textboxes, comboboxes, option groups, checkboxes, and ranges of data. You only have to do 2 things to get the results.

1. Create a new module and copy the code from the FAQ and paste it in your new module.
2. Set the Tag property of each of your "filter" controls as specified via the FAQ.
3. Call the function. To test it, in your OnClick event include this:

MsgBox BuildWhere Me

 

[freespiritcherishes]

Thanks fp!! I'll be back and will let you know how i got on.
freespriitcherishes