Sorting and Grouping question

[flaviooooo]

Hi,

I hope the following is possible:

I have a report containing ordernumber. It is sorted on a sequencenumber.
Each order has a Remark ID.

Now we would like to start a new page every time a new Remark ID starts. i tried fiddling with the Sorting and Grouping, but can't get it going.
I sort on the seqnr and then on Remark, setting a page break in the footer of the Remark. But that way it starts a new page after each order...

Example:

order seq remark

1000 1 1a
1001 2 1a
1005 3 1b (a new page should start)
1003 4 1c (a new page should start)
1002 5 1c
1006 6 1a (a new page should start)


Thanks in advance for your advice

 

[THWatson]

flaviooooo
In Sorting and Grouping, make a Footer for your RemarkID. (It doesn't have to have anything in it, so you can set its height to 0 unless you wish to have something in it.) In that Footer's properties set it to Force a New Page after section.

Tom

 

[flaviooooo]

Hi Tom,

thansk for the feedback, but this would put every order on a different page.
In the example, the 2 first orders should be on the first page, etc...

 

[THWatson]

flaviooooo
If you put the RemarkID as the first in the sort order?

Tom

 

[flaviooooo]

Hi,

then the new page-thing wold work. But the orders need to be sorted on the Sequence number first.

 

[dhookom]

You can set the report's record source to something like:

SELECT tblOrders.*, Nz((SELECT TOP 1 remark FROM tblOrders O WHERE O.seq<tblOrders.seq ORDER BY O.seq DESC),[remark]) AS PrevRemark FROM tblOrders;

Make sure you set your report's sorting and grouping to the sequence and add bound controls in the detail section to [remark] and [PrevRemark].

Add a pagebreak control to the very top of the detail section and add this code to the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.PageBreak.Visible = Me.Remark <> Me.PrevRemark
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[flaviooooo]

Hi Duane,

thanks for the code to get the previous remark, that will come in handy every once in a while.

Unfortunately, the pagebreak still appears on each remark, maybe this has something to do with my access version? I use Access97 to create this report.

Thanks in advance for your feedback.

 

[dhookom]

I'm surprised it didn't work for you regardless of your Access version. I created a table like yours and built the query and report. It displayed exactly like you wanted.

When you look at your record source query, do you see what you expect in the query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[flaviooooo]

Yes, in the query I see the Previous Remark ID filled in perfectly.

I have now solved this with a workaround, adding an extra field into the table whcih will hold an extra sequence number.
When the report opens, a small script starts which checks every line and fills in the extra seq, which I then use to group my report on.

It's a workaround, but it gets the job done.

Thanks!