Printing from a from in the same sort sequence

Startrac98
Startrac98 used Ask the Experts™
on
I display a form with 5 fields.  Then I select the field that I want sorted.  After the sort I need to print the 10 pages by the same sequence as I selected on the form.  What's the simpleiest way to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Forms were never really meant to be printed, so you'll find that you can't do many simple things when you try to do this.

In the long run you'll be much better off creating a report and using that to print. You can open a report with the same filters and sort orders as your form, and then print that (and have a LOT more control over the print operation).

To open a report from your form:

DoCmd.OpenReport "YourReport", acViewPreview, , "Your Where Clause"

Note that sorting and ordering options may be largely controlled by the report, depending on how you build it - for example Grouping always take precedence over Order By, so be aware of that.

Author

Commented:
Can I capture the Sort Parameters for the Form and put in the  "Your Where Clause" below?  The sort may change on the form & I want it to be the same in Printout..

DoCmd.OpenReport "YourReport", acViewPreview, , "Your Where Clause"

Thanks
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I display a form with 5 fields.  Then I select the field that I want sorted.  After the sort I need to print the 10 pages by the same sequence as I selected on the form.  What's the simpleiest way to do this? >

Are you saying that you have two questions here:
1. How do I select a field in a form and sort by it (you did not specify ascending or descending)
2. Print a report in the same order
(note that only one question is allowed per post)


You can do what you are asking but it is messy and you need to have a good background in VBA.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
The form gets displayed and the user can select a sort.   That information is placed in the order by of the Form.  That's ok.

I need to print the report in the same order.  Can I pick up the order by from the form and put it in the order by of the report.   Print & Form read the same Table.  And then call the print report.  This is what I am have a problem with.  Can you help.

Thanks,
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
As stated by LSM, report sorting and grouping is generally controlled by the report itself.

However, if you want to control the printing process, you could open the report in PrintPreview mode, then loop through your forms recordset, filter the report and print the current record, then move to the next record in the recordset.

It would look something like (untested and without Access to give me intellisense):

Dim rs as DAO.Recordset
Dim rpt as Report

docmd.OpenReport "reportname", acViewPreview    'you could add acHidden as the mode
set rpt = reports("reportname")
with rpt

set rs = me.recordsetclone

While not rs.eof

    'Getting the following right is critical.  You need to use the primary key field
    'of the form and recordset so that there is only one record printed from the
    'report for each record in your forms recordset
    .filter = "[SomeField] = " & rs!SomeField
    .filteron = true

    docmd.Printout

    rs.movenext
Wend
rs.close
set rs = nothing

end with
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Do this on the field's label click event to sort:

'(Will not toggle fast enough if you click too fast)
    If Me.OrderBy = "YourField Desc" Then
        Me.OrderBy = "YourField Asc"
    Else
        Me.OrderBy = "YourField desc"
    End If


Then do this on your button (on the same form) to open the report:

Dim strOrderBy As String
    strOrderBy = Me.OrderBy
    DoCmd.OpenReport "YourReport", acViewPreview, , , , strOrderBy

Then do this on the Load event of the report:

    If Me.OpenArgs <> "" Then
        Me.OrderBy = Me.OpenArgs
        Me.OrderByOn = True
    End If

Finally note that you will have to monitor this as you may have to resort the form/report after this system runs


But in any event, it works fine for me...


JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Jeff,

That only seems to work if no sort order has been defined in the reports Sorting and Grouping.  At least that is my experience in 2003 and 2007.  But it does appear to work if there is grouping (although the sorting is done within each group, which is automatically sorted).

Dale
ok, this may not be the best way, but it seems to have done what I needed.

Me.OrderByOn = True
Dim z As Variant
Dim LPosition As Integer
Dim RPosition As Integer
Dim wLength As Integer
z = Forms!frm_DisplayFinancialContracts.OrderBy
LPosition = InStr(32, z, "[")
RPosition = InStr(LPosition, z, "]") + 1
wLength = RPosition - LPosition
Me.OrderBy = Mid(z, LPosition, wLength)
DoCmd.Requery
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
whatever works!

;-)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Dale,

<That only seems to work if no sort order has been defined in the reports Sorting and Grouping. >
Yeah, thats why I put the little blurb at the end of my post:
"Finally note that you will have to monitor this as you may have to resort the form/report after this system runs"

I was not sure what the OP's setup was, or even if the report existed yet...

This is actually the first time I've done something like that, so for me, it just another sample db in my toolbox...
;-)

I can't say I understand the OP's solution, ...but like you..
Hey, if it works, ...roll with it...

;-)

Jeff

;-)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Startrac98,
AFAIC, you can accept your own post here as the solution...

(You may want to add some comments to your code though...)


;-)

Jeff

Author

Commented:
Both the Form & Report read the same Table.  Quick fix with no Groups or Sort in Report Object.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial