Sort a report based on options on a form

  • Thread starter Thread starter Angel_G
  • Start date Start date
A

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
 
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
 
Angel_G said:
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?
Click to expand...


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
 
Back
Top