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.OpenReport 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.OpenReport 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.

[Stewart Ross]

Please post the on-click code involved in your user sorting. It may well be that a custom SQL recordsource is built and used, like ADezii's solution in post #2. If that is the case you may be better to use ADezii's solution than mine, which was intended for a really simple approach as you had advised you knew little of VBA programming.

I have reset the best answer post at present as it may well not be mine which is!

-Stewart

[MsTGordon]

@Stewart Ross
I appreciate your efforts. I am not using a code for the user to sort but the standard MS Access sort buttons. I've arranged custom tool bars to allow the stadard "A-Z" button and "Z-A".

[Stewart Ross]

Can I just check that when you tried the Msgbox to report the status of OrderBy the form was custom-ordered at the time?

I have tried using the toolbar sort facilities on forms in the Northwind sample database, and when I do so the OrderBy property reflects the field on which the sort is taking place, as I would expect it to.

Here, for instance, is what the OrderBy property shows for the Northwind Products form after two custom sorts are tasked from the toolbar:

[Lookup_SupplierID].[CompanyName] DESC, [Products].[ProductName]

If all else fails, you could upload to a new post a version of the database as a ZIP file (removing any confidential information first of course) so we can get the chance to see exactly what is happening with your current database.

-Stewart