Can a form used to filter report be used to change the sorting? (1 Viewer)

[gojets1721]

This is a bit of a forms, reports, and VBA question. I have a form that is used to filter down a report. It lets the user select a date range, etc.

I was curious if there's a way to allow the form to change the sorting of the report as well. For instance, right now, the report is automatically set to sort from newest records to oldest. And the only way to change that, is to manually change the sorting in the design view. Or have two separate reports with different sorting (kinda clunky).

Any suggestions?

 

[MajP]

You can then pass the order by through openargs or modify the reports querydef.
Make sure you do not have sorting and grouping. If you do then your code has to modify the group level.sortorder not the reports order by. This can be done but could be tricky if you have multiple groups

Private Sub Report_Open(Cancel As Integer)
  If Me.OpenArgs & "" <> "" Then
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
    Me.OrderByOnLoad = True
  End If
End Sub

 

[MajP]

If you do have multiple sort fields and sorting&grouping levels you might have to post your db. The code would be must trickier

 

[gojets1721]

If you do have multiple sort fields and sorting&grouping levels you might have to post your db. The code would be must trickier

Sorry for the delay!! Could you demonstrate your solution in the attached example DB? I'm not sure how to implement what you are recommending.

I only want to have one sort. Ascending or descending by time (with the default being descending).

 

[MajP]

See demo using OpenArgs. There is no error checking and I only did the code for the rptbasic.

 

[gojets1721]

See demo using OpenArgs. There is no error checking and I only did the code for the rptbasic.

That worked!! Quick question though...let's say I wanted to have another filter in the form as well. Like a 'last name' field? Would that change anything?

 

[MajP]

No. You are passing the filter in the "where" argument. You are passing the Sort in the "openargs" argument. They are therefore independent.

 

[MajP]

I missed the point that you only wanted to sort on time, so my table and form are overkill. The technique would be the same but you would only need a way to select ascending or descending. Then pass that value (asc, desc) in openargs.

 

[gojets1721]

I missed the point that you only wanted to sort on time, so my table and form are overkill. The technique would be the same but you would only need a way to select ascending or descending. Then pass that value (asc, desc) in openargs.

Thank you! This is all working flawlessly. I really appreciate the help