locked
Report order doesn't work RRS feed

  • Question

  • I'm trying to sort a report in VBA, but it's not sorting. I open the form in layout mode, set the OrderBy property = Loan_Amt DESC, and set OrderByOn=true. I've tried sorting on other columns but no change. What am I missing?

    DoCmd.OpenReport rptName, acViewLayout, , , acHidden   <br/>Set rpt = Reports(rptName)
    rpt.OrderByOn = True
    rpt.OrderBy = OrderParm 'WHY won't this work????????
    rpt.RecordSource = "SELECT * FROM " + stRptQuery <br/>DoCmd.OpenReport rptName, acViewPreview
    
    

    Darrell H Burns
    Thursday, May 26, 2011 8:25 PM

Answers

  • I'm trying to sort a report in VBA, but it's not sorting. I open the form in layout mode, set the OrderBy property = Loan_Amt DESC, and set OrderByOn=true. I've tried sorting on other columns but no change. What am I missing?

    DoCmd.OpenReport rptName, acViewLayout, , , acHidden  <br/>Set rpt = Reports(rptName)
    
    rpt.OrderByOn = True
    
    rpt.OrderBy = OrderParm 'WHY won't this work????????
    
    rpt.RecordSource = "SELECT * FROM " + stRptQuery <br/>DoCmd.OpenReport rptName, acViewPreview
    
    
    
    

    Darrell H Burns


    Does the report have any sorting or grouping defined internally?  That will always override the OrderBy property, or any default ordering specified by the query.

    Also, once the report's Open event is over, it's too late to be changing its RecordSource property.  It's my understanding that you *can* change the .OrderBy and .OrderByOn propeties, but doing so forces the report to be closed and reopened.

    To do the sort of dynamic report configuration you seem to be attempting, you probably should be passing arguments to the report via the OpenArgs argument of DoCmd.OpenReport, and then doing the configuration in the report's Open event procedure.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Thursday, May 26, 2011 8:33 PM

All replies

  • I'm trying to sort a report in VBA, but it's not sorting. I open the form in layout mode, set the OrderBy property = Loan_Amt DESC, and set OrderByOn=true. I've tried sorting on other columns but no change. What am I missing?

    DoCmd.OpenReport rptName, acViewLayout, , , acHidden  <br/>Set rpt = Reports(rptName)
    
    rpt.OrderByOn = True
    
    rpt.OrderBy = OrderParm 'WHY won't this work????????
    
    rpt.RecordSource = "SELECT * FROM " + stRptQuery <br/>DoCmd.OpenReport rptName, acViewPreview
    
    
    
    

    Darrell H Burns


    Does the report have any sorting or grouping defined internally?  That will always override the OrderBy property, or any default ordering specified by the query.

    Also, once the report's Open event is over, it's too late to be changing its RecordSource property.  It's my understanding that you *can* change the .OrderBy and .OrderByOn propeties, but doing so forces the report to be closed and reopened.

    To do the sort of dynamic report configuration you seem to be attempting, you probably should be passing arguments to the report via the OpenArgs argument of DoCmd.OpenReport, and then doing the configuration in the report's Open event procedure.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Thursday, May 26, 2011 8:33 PM
  • <<Does the report have any sorting or grouping defined internally?  That will always override the OrderBy property, or any default ordering specified by the query.>>

    Yes, it turned out that was part of the problem, so I eliminated the internal sorts. That left me able to control either the recordsource or the sort order, but not both.

    <<Also, once the report's Open event is over, it's too late to be changing its RecordSource property. >>

    This seems like a Catch-22 because I can't set a rpt object...SET rpt=Reports(rptName)...unless the report is already open.

    <<you probably should be passing arguments to the report via the OpenArgs >>

    This is the right answer. Thanx!


    Darrell H Burns
    Friday, May 27, 2011 6:48 PM