Set Report's sorting and Grouping in VB

[MarionD]

Hi all,

I have a from with Button to print a report. On the form I have an option field to sort by a.) alpha b.) by number or c.) by date. The report and data are exactly the same but I'm having a problem setting the report's sorting and grouping properties in VBA.
I have solved this by creating 3 identical reports and setting the properties manually but I'm sure one must be able to print the same report and set the sort (order by?) properties in VBA. Can someone tell me how?

Thanks a lot
Marion

 

[wkalo]

Can You send A sapmle Database, i Faced The Similar Problem Not Long Time Ago And I Solved, May Be You Have The Same Problem !

 

[RuralGuy]

You could pass the sort parameters in the OpenArgs and use them in the report like the following:

Private Sub Report_Open(Cancel As Integer)
Dim Args As Variant, x As Integer

If Not IsNull(Me.OpenArgs) Then
    '-- Report is being opened from a form passing Sort Parameters
    Args = Split(Me.OpenArgs, ";")
    For x= LBound(Args) To UBound(Args) 
        Me.GroupLevel(x).ControlSource = Args(x)
    Next x
End If

   '-- Example of setting up our own sorting manually
   'Me.GroupLevel(0).ControlSource = Args(0)
   'Me.GroupLevel(1).ControlSource = Args(1)
   'Me.GroupLevel(2).ControlSource = "InvDate"
   'Me.GroupLevel(3).ControlSource = "DDT_Type"
   'Me.GroupLevel(3).SortOrder = True               '-- Descending
End Sub

 

[MarionD]

Hi there, sorry for only replying now, I've had a couple of days off. Thanks for the help. I think I will probably keep 2 reports for the moment as I'm under a bit of time pressure... it looks more complicated than I thought. But I will try understanding your code and eventually be able to use it! Thanks again!
Marion

 

[chumporn]

I had easier way to sorting report by coding

I have an simple way to sorting report by VBA script without more complicated much. Its my adaptation this case to the same purpose which got guide line from RuralGuy.

Thanks RuralGuy.

When you just want to sorting only one field in your report. You should insert one field, any field for dummy such as InvDate, by add in "Sorting and Grouping" pop up menu.

And then, you write code in "On Open" event of report as following sample;

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0).ControlSource = "DDT_Type"
Me.GroupLevel(0).ControlSource = False '-- True=Descending, False=Ascending

You can try to change value "DDT_Type" to reference value from your attention form's object which will vairable sorting as you need.

 

[MarionD]

Hi tehere - thanks a lot .. this looks like the solution I've been looking for. Will test after Christmas.. off on holiday in South Africa till the 19 Jan!

Merry christmas!