By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,796 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Print Report based on Form Sort Order

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command11_Click()
  2.     Dim strWhere As String
  3.     If Me.Dirty Then Me.Dirty = False 'save any edits
  4.     If Me.FilterOn Then strWhere = Me.Filter
  5.     DoCmd.OpenReport "rpt_PtNoBeginsWith", acViewPreview, , strWhere
  7.     End Sub
I don't know what else to do and I'm not that experienced with VBA.
Nov 7 '11 #1
Share this Question
Share on Google+
12 Replies

Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  3. If Me.Dirty Then Me.Dirty = False
  5. If Me.FilterOn Then
  6.   strWhere = Me.Filter
  7.     DoCmd.OpenReport "rptEmployees", acViewDesign, , , acHidden
  8.     Reports![rptEmployees].RecordSource = "SELECT * FROM tblEmployees WHERE " & strWhere & _
  9.                                           "ORDER BY [Last] Desc"
  10.     DoCmd.OpenReport "rptEmployees", acViewPreview, , , acWindowNormal
  11. End If
Nov 7 '11 #2

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rpt_PtNoBeginsWith", acViewPreview, , strWhere
  2. With Reports("rpt_PtNoBeginsWith")
  3.   .OrderBy = "[Your Field Name]"
  4.   .OrderByOn = True
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. If Me.OrderByOn then
  2.   With Reports("rpt_PtNoBeginsWith")
  3.     .OrderBy = Me.OrderBy
  4.     .OrderByOn = True
  5.   End With
  6. End If
Nov 7 '11 #3

P: 8
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?
Nov 8 '11 #4

Expert Mod 2.5K+
P: 2,545
The additional lines of code follow on immediately after the line calling DoCmd.OpenReport on your form's print button.

Nov 8 '11 #5

P: 8
@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?
Nov 9 '11 #6

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. 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.

Nov 9 '11 #7

P: 8
@Stewart Ross
I get the following message:

Orderby= OrderByOn= False
Nov 10 '11 #8

Expert Mod 2.5K+
P: 2,545
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.

Nov 10 '11 #9

P: 8
@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.
Nov 10 '11 #10

Expert Mod 2.5K+
P: 2,545
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!

Nov 10 '11 #11

P: 8
@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".
Nov 10 '11 #12

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. [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.

Nov 10 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.