Print Report based on Form Sort Order

[MsTGordon]

I have a form based on a query where the user can filter and sort in any of the fields of the form. I am able to print preview a report once the user filters the report but if the user sorts the report it does not sort on the report.
I have a button on my form with the following code on a print button which opens the report:

Private Sub Command11_Click()
    Dim strWhere As String
    If Me.Dirty Then Me.Dirty = False 'save any edits
    If Me.FilterOn Then strWhere = Me.Filter
    DoCmd.OpenReport "rpt_PtNoBeginsWith", acViewPreview, , strWhere
 
    End Sub

I don't know what else to do and I'm not that experienced with VBA.

[ADezii]

The specific Field Sort on the Form is not part of the Form's Filter, and as such, will not reflect on the Report when it Opens. If you know what Fields may be Sorted you can Open the Report in Design/Hidden, then modify the RecordSource Property to reflect the Form's Filter as well as any Sorting, as in:

Dim strWhere As String
 
If Me.Dirty Then Me.Dirty = False
 
If Me.FilterOn Then
  strWhere = Me.Filter
    DoCmd.OpenReport "rptEmployees", acViewDesign, , , acHidden
    Reports![rptEmployees].RecordSource = "SELECT * FROM tblEmployees WHERE " & strWhere & _
                                          "ORDER BY [Last] Desc"
    DoCmd.OpenReport "rptEmployees", acViewPreview, , , acWindowNormal
End If

[Stewart Ross]

If your user form can tell you which field you want to sort on there is a way to do so you may find easier to apply than building a new SQL statement recordsource method, although it is not as flexible. It uses the OrderBy property of the report, like this:

DoCmd.OpenReport "rpt_PtNoBeginsWith", acViewPreview, , strWhere
With Reports("rpt_PtNoBeginsWith")
  .OrderBy = "[Your Field Name]"
  .OrderByOn = True
End With

If you have manually applied sorting to fields displayed on the form, and these have the same name as those used in the report, you can get the field name to sort the report on by referring to the same OrderBy property of the form itself:

If Me.OrderByOn then
  With Reports("rpt_PtNoBeginsWith")
    .OrderBy = Me.OrderBy
    .OrderByOn = True
  End With
End If

-Stewart

[MsTGordon]

Thank you Stewart for your response. Your second solution seems to be the one that may work for me considering the fields are manually sorted and the same on the report. Where should I put the code you listed? On the print command button or on the "on open" event of the report?

[Stewart Ross]

The additional lines of code follow on immediately after the line calling DoCmd.OpenRepor t on your form's print button.

-Stewart

[MsTGordon]

@Stewart Ross
I almost there. I posted the code exactly where you told me on the print button on the form. However when the report opens in Print Preview it is still not sorted like the form.

Should I have a code on the "On Open" event of my report?

[Stewart Ross]

Should be no need to use the On Open event of your form. Could you check what is being ordered? One way to do so is to add a line of code which will list the value of the OrderBy property. Please add the following line just before your DoCmd.OpenRepor t line then post back what you see in the messagebox:

msgbox "Orderby = " & Me.OrderBy & " OrderByOn = " & Me.OrderByOn

If OrderBy lists a particular field then the same field with the same name must exists in the underlying recordsource for the report if we are to use the solution I suggested. If not there may well be an alternative way to determine what field to use instead.

-Stewart

[MsTGordon]

@Stewart Ross
I get the following message:

Orderby= OrderByOn= False

[Stewart Ross]

Please advise how you are changing the sort order of the form under user control.

From the absence of any SortBy content and the SortByOn property being False when you tested as asked you are clearly not applying a sort to the controls on the form using the menus or a right-click shortcut action. Because there is no OrderBy applying to your form, attempting to apply this property to your report cannot work.

If you know which field the form is being sorted on you can apply that knowledge to the OrderBy property of the report, as shown in post #3 above.

-Stewart

[MsTGordon]

@Stewart Ross
I'm sorry this is so confusing. My form is based on a qry which is set to sort by Part Number then Manufacturer by default. The user can then click in any one of the 8 fields on the form to sort and filter should they desire. The print button is a command button on the form which opens a report built on the same qry as the form.
If the user filters the form and hits the print button, the report reproduces the form. The user wants to be able to do the same thing if they sort.