Sorting Reports

L

Luke Bailey

Is there any way to change the sorting of a report through code as it is
being opened? I want to have a form that has some options for filtering a
report and also include some sorting options. Then when user clicks an "OK"
button on the form, have the report open up.
 
D

Dennis

Put some code similar to this in the On open event of the report

If [Forms]![FormName]![FieldName] = "Some Data" Then
Me.RecordSource = "SELECT * FROM [YourQuery] ORDER BY [FieldName]"
else
' some other sort order
end if
 
A

Allen Browne

Actually, the ORDER BY clause won't work - unless it's a really basic
report, and then it won't be reliable.

You can use the Open event of the report to set the ControlSource of the
GroupLevel. Details in:
Sorting Records in a Report at run-time
at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dennis said:
Put some code similar to this in the On open event of the report

If [Forms]![FormName]![FieldName] = "Some Data" Then
Me.RecordSource = "SELECT * FROM [YourQuery] ORDER BY [FieldName]"
else
' some other sort order
end if

Luke Bailey said:
Is there any way to change the sorting of a report through code as it is
being opened? I want to have a form that has some options for filtering
a
report and also include some sorting options. Then when user clicks an
"OK"
button on the form, have the report open up.
Click to expand...
Click to expand...
 
M

Marshall Barton

Luke said:
Is there any way to change the sorting of a report through code as it is
being opened? I want to have a form that has some options for filtering a
report and also include some sorting options. Then when user clicks an "OK"
button on the form, have the report open up.
Click to expand...

The only reliable way to sort a report is to use Sorting and
Grouping (View menu).

You can modify the field specified in a group level in the
report's Open event procedure with code like:

Me.GroupLevel(x).ControlSource = Forms!theform.thetextbox
 
Top