help with filtering code

[ninachopper]

Hello,



I have all my data in a report and the report is set so that the user can not access design view or layout view.
I am trying to work out a code where the user can double click on a record and it will automatically filter the report to the record they chose.
Any help would be great! (I'm still a newbie)

Thanks !

[pbaldy]

This should work for you:

BaldyWeb wherecondition

[ninachopper]

I tried to use this code for 'text' but everytime I clicked on a record, it prompted my computer to print out the whole report. Never opened up a new report with the filtered information. Any ideas?
For the 'control name' , you mean the control for the field which I would be clicking on, right?

Thanks for your time!

[pbaldy]

Yes; ControlName would be the name of the textbox (or whatever) containing the value you want the report restricted to. FieldName would be the name of the field in the report's source. What is your exact code?

[ninachopper]

I was a little confused. Do I have to make a 2nd report in order for this to work? I was just hoping to use the same report and just minimize the data by filtering.

I have one field set up where it is a number field so you double click on any number, and a button(form) pops up where you can choose > = < and it filters the data right there on the original report.

The code I am using is:


Private Sub GREATER_Click()
Reports("Noise Report").FilterOn = False
Reports("NOISE REPORT").Filter = "[TWA] >= " & ORDERVAR
Reports("Noise Report").FilterOn = True

DoCmd.Close acForm, Me.Name
End Sub


I was hoping that for all the text fields I could just click on any record and it would do the same thing, minus the button. I have been experimenting but can't quite get it to work. The number field works perfect though.

Thanks

[pbaldy]

The method I posted does not require a second report. In essence it applies a filter on the report as it opens it. I use this technique all the time, so I feel confident it will work for you if we get the syntax right.

Now that I read through again, are you trying to filter the report from inside the report? That would be possible only in the new Report view, but the modification to your working code for a text field would be:

Reports("Noise Report").FilterOn = False
Reports("NOISE REPORT").Filter = "[FieldName] = '" & ORDERVAR & "'"
Reports("Noise Report").FilterOn = True

[ninachopper]

ok, lets give it a shot.

Here's what I have...


Private Sub Job_Title_DblClick(Cancel As Integer)
DoCmd.OpenReport "Noise Report", , , "Job_Title = '" & Me.Job_Title & "'"
End Sub



My field name and control name are the same. Is this going to cause problems?

thanks again!

[ninachopper]

also, I fixed the code for the way I was going to do it and it filters, but no data comes up, it just pulls up a blank report. Then I can toggle the filter and the full report pops back up. Any ideas?

[pbaldy]

Let's clarify first; are you running this code from inside the same report? If so, I'm not sure about the result. My code is intended to open the report filtered when it isn't already open.

By the way, it would not be a problem here to have the field and control with the same name.

[ninachopper]

I think that's the confusion. I am trying to be able to run the code while viewing the full report already opened. I want the viewers to be able to view the full data, and then have the option to filter it down even more for a printable version.


My database is set up where there is a main form with multiple dropdown boxes. Any option you choose, will bring up a report with just the data you chose. I need it to be read only so no changes can be made, but my boss wants to be able to filter down even further once the report is opened. (kind of what the shortcut menu does on a report if you right click on a record, except in order to have the drop down, they can get into the design view)

So yes, the code is running through the report

[pbaldy]

Did you try that modification I made to your filter code in post 6? You would need to change the names, but the quoting would be appropriate for a text value.

[ninachopper]

hi

I did try the changes you suggested and got it to work perfectly! Only with one catch. I was hoping to be able to filter one field, and then from there, be able to filter down again but, that does not work. After the 2nd filter, it randomly picks out a bunch of files to view that do not match. Maybe you have one last word of advice?
Here's the code that works for a single filter...

Private Sub Facility_Type_DblClick(Cancel As Integer)

Reports("Noise Report").FilterOn = False
Reports("Noise Report").Filter = "[Facility Type]= '" & Facility_Type & "'"
Reports("Noise Report").FilterOn = True
End Sub

I added this sort of text to every field, now is there a trick to be able to run a filter over a filter?

[pbaldy]

Well, this probably isn't the way I'd go to begin with, but this type of thing might work (untested air-code warning):

If Len(Reports("Noise Report").Filter & vbNullString) = 0 Then 'no existing filter
  Reports("Noise Report").Filter = "[Facility Type]= '" & Facility_Type & "'"
Else 'there's already a filter
  Reports("Noise Report").Filter = Reports("Noise Report").Filter & " AND [Facility Type]= '" & Facility_Type & "'"
End If

It would basically add the current filter to whatever was already there. I don't really like the method because you can't tell what's already been applied, and no easy way to undo one (the first problem is solvable, but I think would look funny). You might add a button that clears the filter so the user can start over.

[ninachopper]

I think I like the reset button (like a toggle filter button) instead. Again, I tried to make it work and it prompt a 'print report' function. I have to look into it to see why this keeps happening. I have been working on this non-stop for days, so I appreciate the feedback and ideas to keep my mind straight!
I set each field to filter when double clicked, so with the reset button, would I just open the report again? What would be the best way to toggle the filter through a button?

Again, thanks so much for your help, you have been a lifesaver!

Also, I believe after this issue, my database will be good to go! I appreciate all the help but I may not need to bug ya anymore

[pbaldy]

Happy to help, and you're not bugging me! What I would do with the button is set the filter to "" (and probably turn it off). If you want to toggle it, you'll need to save the filter at that time so you can put it back.