Sorting Report

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

I have a report that has 9 item in each line. I would like
to sort this report by anyone of those 9 names. I made a
form that is a drop down and you can choose 1 of the 9
categories. This is then sent to a table ("Trenton BP")
How can I tell the report to sort by the name that is in
the Trenton BP table? Only 1 item can be in the table at a
time.Example - in the form I click "Order Date"
then "order Date" is sent to the Trenton BP table. Now I
need the report to sort by the "Order Date". Please help.
Thanks
 
Use the report's Open event procedure to set its OrderBy property.
For example, if the form is named "Form1", and the dropdown list contains
the name of the field to sort by:

Private Sub Report_Open(Cancel As Integer)
With Forms![Form1]![Combo1]
If Not IsNull(.Value) Then
Me.OrderBy = .Value
Me.OrderByOn = True
End If
End With
End Sub

If you have anything in the report's Sorting and Grouping dialog, that will
override the OrderBy property. However you can reassign the ControlSource of
the GroupLevel in Report_Open. Details:
http://allenbrowne.com/ser-33.html
 
JT said:
I have a report that has 9 item in each line. I would like
to sort this report by anyone of those 9 names. I made a
form that is a drop down and you can choose 1 of the 9
categories. This is then sent to a table ("Trenton BP")
How can I tell the report to sort by the name that is in
the Trenton BP table? Only 1 item can be in the table at a
time.Example - in the form I click "Order Date"
then "order Date" is sent to the Trenton BP table. Now I
need the report to sort by the "Order Date".
Click to expand...


First, you need to have to specify a field, any field, for
the report to sort on using the report's Sorting and
Grouping window. Then add code to the report's Open event
to set the grouplevel's field:

Me.GroupLevel(0).ControlSource = DLookup("sortfld",
"Trenton BP")

If you have other sorting and/or grouping in the report,
then change the 0 to the appropriate number. You didn't
mention the name of the field in the Trenton BP table, so
replace sortfld with that name.
 
Back
Top