Sort order or reports at runtime 1

[apkohn]

I have a button that opens a report via VBA code.

    DoCmd.OpenReport strReport, acViewPreview, , strFilter

,
where strFilter is a string with a Where Condition, eg;

    "[Meeting] = 3"

Now, I want to be able to take the contents of a combo box on the form, that specifies a field name in the report, and use that to determine the sort order of the report. That is I want to replicate the SQL (note that the SELECT statement isn't a simple SELECT * FROM..., but you get the idea);

     SELECT * FROM tblTableName WHERE [Meeting] = 3 ORDER BY [FieldName];

Is this possible? The DoCmd.OpenReport method does not seem to support this. I had also considered setting the OrderBy property of the report to the field, but this can only be done after I open the report, meaning I can't send the ordered report direct to the printer (eg using acViewNormal).

     DoCmd.OpenReport strReport, acViewPreview, , strFilter
     Reports!strReport.Report.OrderBy = [FieldName]
     Reports!strReport.Report.OrderByOn = True

Thanks in advance.

 

[danvlas]

Try this in the Open event of the report:

Private Sub Report_Open(Cancel As Integer)
OrderByOn = True
OrderBy = Forms("YourFormName&quot("ComboBoxName&quot
End Sub


Hope this helps,

Dan

 

[apkohn]

Thanks danvlas, that works a charm. I hadn't thought out of the box to make the report set its own sort order as opposed to have the form set it.

One slight refinement I have added though, is to let users still open the report via the database window without the form open, with default values. eg:

    strOrderBy = Forms!frmName.cmbOrderField
        With Me
        .OrderBy = strOrderBy
        .OrderByOn = True
    End With
    Exit Sub
Report_Open_Error:
    Select Case Err.Number
        Case 2450 'Form not found
            strOrderBy = "[DeafultSortOrder]"
            Resume Next
        Case Else
            MsgBox Err.Description
    End Select