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"
           DoCmd.OpenReport "FiltersReport", A_PREVIEW, , Me![MiniList].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 at any time.  Is this possible?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If I'm understanding this right, you have 4 fields, each of which represents a different field in the recordsource to filter on and some may be chosen and some may not.
With that, number them txtFilter1, txtFilter2, txtFilter3, txtFilter4.  On each box put the recordset's fieldName in the "tag" property.
Example txtFilter1 represents CompanyName, put [CompanyName] in the tag property on the Other tab.

The following will build you a suitable filter string:

For j = 1 to 4    
    If Not IsNull(Me.Controls("txtFilter" & j)) _
        And (Me.Controls("txtFilter" & j)) <> "" Then
                           filterString = filterString & Me.Controls("txtFilter" & j).Tag _
                & " = '" & Me.Controls("txtFilter" & j) & "' AND "
     End If

If Right(filterString, 5) = " AND " Then _
    filterString = Left(filterString, Len(filterString) - 5) 'strip trailing AND

I hope that's what you were looking for.

Your syntax and logic looks okay to me.   I wonder how your filters are working on the subform.  
Could you add this line right before your If statement:
debug.print Me![MiniList].Form.Filter

Then you can see if your filter buttons are applying the filter, or playing with the underlying query for the subform.  

Also, not knowing how your filters are being applied to the subform, it might be possible for Me![MiniList].Form.Filter to contain a value even if Me![MiniList].Form.Filteron = false.

You could try checking the subform's FilterOn property instead of the filter itself.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

freespiritcherishesAuthor Commented:
no activity with the debug.print command.  Filter buts and combos are on the main form, when click, filter the records in the subform.  The work beautifully.
I have a preview report on the main form also which shows all my records.

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"

    ' and 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 procedure for this filter to be applied to subform also

    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
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 given chosen filter and any given time?  The only other thing i can thing of, is to create 8 print prev buttons and 8 printrep buttons and hide them!!

In this case, the subform has no filter - you are simply changing the record source. Try this instead:

If Instr(Me![MiniList].Form.RecordSource,"WHERE ")<>0 Then
   Msgbox 'Apply a filter to the form first"
   DoCmd.OpenReport "FiltersReport", A_PREVIEW, , Mid$(Me![MiniList].Form.RecordSource,Instr(Me![MiniList].Form.RecordSource,"WHERE")+6)
End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ok, based on the code you just posted, your 'filter' buttons don't actually add 'filters' to the subform, they change the recordsource.  Hence nothing showing up on the debug.print.  

You'll have to approach your report a different way.  Possibly changing it's recordsource, but it might be easier to follow something like walterecook's suggestion, where you build your own filter string to pass to the report.

...clicked to slow...  shanesuebsahakarn's suggestion looks like your best bet.   :-)
freespiritcherishesAuthor Commented:
Interesting, I click a filter, say, "filterbyname", then my preview report button with your code, and i get a prompt box with "hbyname" come up.  If i enter the input value again, the report comes up displaying all my records, so the report is unfiltered.  It happens with all the filters i click.  But its interesting how its reacting!  Any suggestions?
Hmm - exactly how are you applying the filter? What is "filterbyname" - is it the name of a query?
freespiritcherishesAuthor Commented:
yes, its a parameter query called "filterbyname".  I click the button and it applies the filter to the subform as recordsource and displays my records with say "Harriet".  If I click my preview report button, with the code you gave, a prompt comes up saying "ycontact".  I think its the prompt with the name of the control half cut of cos if i choose another filter button like "filterbycontent", i enter something like "nice day", again, the subform displays my records with nice day in it.  If I click prev report again, it prompts again with "ycontent".  If i select a combo filter, i get a different result.  If I select a subject like "holiday", the suform displays all my records with the subject "holiday", but when I click the prevrep button with sha's code, instead of prompting me like it did with the buttons, it throws up the msg "Apply a filter to the form first.".   Still not working but its fascinating stuff!

Experts, engage your missiles!
I don't use Parameter Queries a lot, but I've been reading the documentation and I don't see an easy way to accomplish what you are looking for using them.

One idea, make a single query for your subform, then when the user clicks one of the 'filter' buttons, you prompt for a value, and then in your code you apply a filter to the subform.  Then you can go back to your original code and you will have a filter to apply to the report.  (but you will want to account for the Filteron property.)

Another idea, make all the buttons act like Button 2, where you build the SQL and apply it to the recordsource in the code.   But for the buttons that used to be parameter queries, you can give them a field to type into on the main form, or you could prompt them for a value from your code.  
Then when you want to call your report, shanesuebsahakarn's solution looks like it will work.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.