Creating a report that uses a parameter for Order By

  • Thread starter Thread starter BWD
  • Start date Start date
B

BWD

Hello Group

I am trying to create a report based on a query that will allow the
report results to be ordered by a field that is selected by the
user.

I am envisioning creating a combo box that lists all the queries field
values and then letting the user select from that list as the sort by
field. However, I do not know how to get the query to accept my
parameter value for the sort on field value.

As a test I have tried manipulating the actual query through SQL view
and entering the parameter brackets in the Order By part of the code.
Although I get prompted for the field value the sort order does not
seem to work.

Any thoughts?

Thanks
 
Create a Combo box on your form and set the 'Row Source Type' to Field List,
then set the 'Row Source' to your Table name, in this case I am using Table1.

Create a function like the one below to set the Record Source on your report.

Public Function SqlOrderBy(fldName) As String
SqlOrderBy = "SELECT Table1.ID,Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 " & _
"ORDER BY Table1." & fldName
End Function


Open your report in design view and remove the Record Source from it. Should
be blank. Set the On Open property to the Function you created. See below.

Private Sub Report_Open(Cancel As Integer)
Dim strOrderBy As String
strOrderBy = Forms!Form1!cmbOrderBy
MsgBox strOrderBy
Me.RecordSource = SqlOrderBy(strOrderBy)
End Sub
 
Back
Top