Printing from a from in the same sort sequence

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.

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

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

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,

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

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

sample:
Access-EEQ27727579--Sort-Report-.mdb

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

whatever works!

;-)

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

;-)

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

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


;-)

Jeff

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