Buttons in reports to sort

[redekopp]

Hey there, I have a report that I was wondering if its possible to make certain parts of it selectable to narrow down results. Ive attached a picture of the report. As you can see I would want it so they can select the employees name and it will filter all dates and info only that employee has worked. The same goes for the date, select the date and show all employees who have worked on that date and what they have done. Also want it to work for the modular unit serial number, select the number and show the employee and date that they worked on just that number.

Is this easier or more complicated then it seems? do I need to make more reports or is it just an on click procedure? my vba skills and overall access skill level is beginner, so any extra detail is appreciated. Thanks!

[June7]

So you want dynamic filter, not sort?

I would use a form for user to input criteria then VBA to build filter string and apply to report when it is opened.

Review http://www.allenbrowne.com/ser-62.html

The code could be modified with:

DoCmd.OpenReport "reportname", , , strWHERE

[redekopp]

okay thanks, so to clarify there is no way to make those fields selectable and use those to sort before changing to print preview?

[June7]

What you describe is filtering, not sorting. I open report to Print Preview.

DoCmd.OpenReport "reportname", , acViewPreview, strWHERE

Sorting can be programmatically dynamic, just a little trickier, especially if report has Sorting & Grouping settings.

[ItsMe]

I have never tried it before so I gave it go. As June mentioned, what you desire is not Sorting. What you are referring to is being able to change the Report's Rcordsource or Filter property. If you have grouping and sorting, you can access a group via its index. here is some example code for sorting (you are not asking about sorting).

If Me.GroupLevel(0).SortOrder = False Then
    Me.GroupLevel(0).SortOrder = True
Else
    Me.GroupLevel(0).SortOrder = False
End If

You could place code like this within a textbox control's DblClick event, for example. You could use differnet code, too. You could build code to affect the Filter property of the report. Here is an example. Also, you would probably want a command button in the header to remove the filter.

Dim strFilter As String
strFilter = ""
Me.FilterOn = False
Me.Filter = strFilter
If Not IsNull(Me.NameOfControl.Value) Then
    strFilter = "[NameOfField] = '" & Me.NameOfControl.Value & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
End If

[redekopp]

so what am I putting in the strFilter = "" , if I input that code and change my control and field name. and run it and click. it just makes a blank record that has no values, see below:

[June7]

The referenced link is code to set filter of a form. I already described in post 2 how to modify to apply filter to report (also described by Allen in the tutorial).

[ItsMe]

One thing you might try is to use a msgbox to understand what filter is being applied. Also, I notice that the DblClick event you are using is for a control different than ModUnitSerialNum. Perhaps ModUnitSerialNum does not have a value. Maybe place your code in the ModUnitSerialNum DblClick event.

Here is an example you can use for displaying what the filter criteria is that is being applied.

Dim strFilter As String
strFilter = ""
Me.FilterOn = False
Me.Filter = strFilter
If Not IsNull(Me.NameOfControl.Value) Then
    strFilter = "[NameOfField] = '" & Me.NameOfControl.Value & "'"
    Me.Filter = strFilter
    msgbox strFilter
    Me.FilterOn = True
End If

[ItsMe]

The referenced link is code to set filter of a form. I already described in post 2 how to modify to apply filter to report (also described by Allen in the tutorial).

It seems redekopp desires the user interact with a report in Report View. The OP also requests an explanation how the user might interact with a control on a report to change its appearance. I am not sure I would use Docmd when you can access the Filter property directly.

[June7]

Okay, I've never set up reports for ReportView and dynamic interaction.

ModUnitSerialNum a text field?

Hoping there is no lookups set in table.

[ItsMe]

Okay, I've never set up reports for ReportView and dynamic interaction....

Me either. And yah, not sure if they need text or numeric. I took a gamble. There is the link to Roger's for reference, anyway.

[redekopp]

thanks for your reponses guys, I took a different approach and made a form with tempvars to filter the report. Im having an issue doing a date search though.

My data is only a shift date, but obviously I need to search from a start date to an end date if I wanted everything in between. I know I made the form right and the tempvars correctly. Its doing the criteria in my qry im not sure about. I did it doing a criteria and a "or" such as the screenshot attached. It wont filter properly though. it still gives me dates before and after what I put into my tempvars. Do I need a different statement in my "criteria" only?

Thanks.

[June7]

You don't want OR, should be AND.

Try:

BETWEEN [TempVars]![tmpStartDate] AND [TempVars]![tmpEndDate]

[redekopp]

edit; I got it to work, thanks for your help!