Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Open Report from a form that has several filter buttons

Posted on 2004-11-04
11
Medium Priority
?
520 Views
Last Modified: 2012-08-13
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![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?

freespiritcherishes
0
Comment
Question by:freespiritcherishes
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 12498258
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
Next

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

I hope that's what you were looking for.
0
 
LVL 3

Expert Comment

by:BrentTemple
ID: 12498340
freespiritcherishes;

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.

-Brent
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12498342
You could try checking the subform's FilterOn property instead of the filter itself.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:freespiritcherishes
ID: 12499327
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
    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 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!!

freespiritcherishes
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 750 total points
ID: 12499408
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"
Else
   DoCmd.OpenReport "FiltersReport", A_PREVIEW, , Mid$(Me![MiniList].Form.RecordSource,Instr(Me![MiniList].Form.RecordSource,"WHERE")+6)
End If
0
 
LVL 3

Expert Comment

by:BrentTemple
ID: 12499420
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.

-Brent
0
 
LVL 3

Expert Comment

by:BrentTemple
ID: 12499445
...clicked to slow...  shanesuebsahakarn's suggestion looks like your best bet.   :-)
0
 

Author Comment

by:freespiritcherishes
ID: 12499502
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?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12499511
Hmm - exactly how are you applying the filter? What is "filterbyname" - is it the name of a query?
0
 

Author Comment

by:freespiritcherishes
ID: 12499762
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!
0
 
LVL 3

Assisted Solution

by:BrentTemple
BrentTemple earned 750 total points
ID: 12513893
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.

-Brent
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question