Sort a report based on options on a form

[Angel_G]

I need to sort a Report (Itemssold_rpt) based on what a user selects in a
form (ItemsSold_frm with SortBy option group). The report contains Item.
Item Desc, Date, Etc. The sortBy option groups stores the values in numeric
format for example Sort by Item is equal to 1 and Sort by Description is
equal to 2.
Can I put some kind of code in the Sort option field for the report that
will sort the report accordingly?
Can someone help me with the right code format for this field?
I have tried it myself but no luck.
Thanks in advance

 

[Sprinks]

Hi, Angel.

See VBA Help on the form's OrderBy property. Based on selections in your
form fields, you will need to build a valid OrderBy string expression, assign
the OrderBy property, the OrderByOn property to True, and open the report:

Dim stDocName As String
Dim stFilter As String

stDocName = "YourForm"
stFilter = 'Assign filter here
With Reports(stDocName)
.OrderBy = stFilter
.OrderByOn = True
End With
DoCmd.OpenReport stDocName, acPreview

Hope that helps.
Sprinks

 

[Marshall Barton]

I need to sort a Report (Itemssold_rpt) based on what a user selects in a
form (ItemsSold_frm with SortBy option group). The report contains Item.
Item Desc, Date, Etc. The sortBy option groups stores the values in numeric
format for example Sort by Item is equal to 1 and Sort by Description is
equal to 2.
Can I put some kind of code in the Sort option field for the report that
will sort the report accordingly?

Kind of crude, but you can use the expression:

=IIf(Forms!ItemsSold_frm.SortBy = 1, Item, Description)

A better approach would be to use a little code in the
report's Open event procedure that copies the name of the
sort field (not a code number) from a form control to the
appropriate GroulLevel entry's control source.

Me.GroupLevel(0?).ControlSource = Forms!ItemsSold_frm.SortBy

 

[Duane Hookom]

I find the method from Allen Browne's web site much more robust
http://members.iinet.net.au/~allenbrowne/ser-33.html. If you have any
sorting and grouping set, attempting to set the OderBy property will be
fruitless.

 

[Sprinks]

Duane,

Thanks for the post. This approach is much more flexible and powerful.

Sprinks

 

[Angel_G]

Thank you Guys! This is great
Blessings to all!

Duane,

Thanks for the post. This approach is much more flexible and powerful.

Sprinks