Avatar of ANTHONY CHRISTI
ANTHONY CHRISTI
Flag for United States of America asked on

Printing from a from in the same sort sequence

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?
Microsoft Access

Avatar of undefined
Last Comment
ANTHONY CHRISTI

8/22/2022 - Mon
Scott McDaniel (EE MVE )

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.
ANTHONY CHRISTI

ASKER
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 Coachman

<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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ANTHONY CHRISTI

ASKER
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 Fye

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 Coachman

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Dale Fye

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
ASKER CERTIFIED SOLUTION
ANTHONY CHRISTI

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

whatever works!

;-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

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 Coachman

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

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


;-)

Jeff
ANTHONY CHRISTI

ASKER
Both the Form & Report read the same Table.  Quick fix with no Groups or Sort in Report Object.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.