(Repeated) "Detail" section in report should be in same order as form

[Bob Quintal]

Is there any way to control the order in which items appear on a
report (in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the same order as I
enter in the form. In fact, it is often the opposite order. The items
only have a name and a price; no natural order as far as I can think.

Thanks in advance for your patience with a noobie!

Without a key upon which to sort, if you repoen the form you may
find the invoice lines in a different order than that entered.

you will need to add an incrementing number to the invoice lines
table.
There are several options. You can use a hidden autonumber field, or
use a hidden date/time field with the default value of now(). Since
you cannot update records quickly enough to get the same value
twice, this will work for manual entry.

You can also put a visible or invisible InvoiceLine field in hte
table and on the form and use code to get the maximum number for
that particular invoice, add one if it exists and put 1 if it does
not exist yet.

 

[paulkaye]

Is there any way to control the order in which items appear on a
report (in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the same order as I
enter in the form. In fact, it is often the opposite order. The items
only have a name and a price; no natural order as far as I can think.

Thanks in advance for your patience with a noobie!

 

[fredg]

Is there any way to control the order in which items appear on a
report (in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the same order as I
enter in the form. In fact, it is often the opposite order. The items
only have a name and a price; no natural order as far as I can think.

Thanks in advance for your patience with a noobie!

You'll have to add a [TimeEntered] field (DateTime datatype) to the
table.
Set it's DefaultValue property to = Now()


As you enter each value in the form the time of the entry will be
saved to the table along with your entry.

Then use this field in the Report's Sorting and Grouping dialog to
sort by. The entries will be printed in the order entered.

 

[paulkaye]

Thank you both - I'm sure I'm getting there. However when I used the
sorting and grouping dialogue I ended up with each item on a different
page with its own header and footer. I figured I should set the "Group
Header" and "Group Footer" to 'No' but received a warning that I was
going to delete something and got all nervous! Is there a way to sort
but not to group? There will be a different number of items on each
invoice so imagine I don't want to set a fixed 'Interval'.

Thanks in advance!

Is there any way to control the order in which items appear on a
report (in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the same order as I
enter in the form. In fact, it is often the opposite order. The items
only have a name and a price; no natural order as far as I can think.

Thanks in advance for your patience with a noobie!

You'll have to add a [TimeEntered] field (DateTime datatype) to the
table.
Set it's DefaultValue property to = Now()

As you enter each value in the form the time of the entry will be
saved to the table along with your entry.

Then use this field in the Report's Sorting and Grouping dialog to
sort by. The entries will be printed in the order entered.

 

[fredg]

Thank you both - I'm sure I'm getting there. However when I used the
sorting and grouping dialogue I ended up with each item on a different
page with its own header and footer. I figured I should set the "Group
Header" and "Group Footer" to 'No' but received a warning that I was
going to delete something and got all nervous! Is there a way to sort
but not to group? There will be a different number of items on each
invoice so imagine I don't want to set a fixed 'Interval'.

Thanks in advance!

Is there any way to control the order in which items appear on a
report (in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the same order as I
enter in the form. In fact, it is often the opposite order. The items
only have a name and a price; no natural order as far as I can think.

Thanks in advance for your patience with a noobie!

You'll have to add a [TimeEntered] field (DateTime datatype) to the
table.
Set it's DefaultValue property to = Now()

As you enter each value in the form the time of the entry will be
saved to the table along with your entry.

Then use this field in the Report's Sorting and Grouping dialog to
sort by. The entries will be printed in the order entered.

If you just wish to sort the records according to the Time field, you
set the sort to the time field and leave Group Header and Footer set
to No.

However as this is for Invoices, you do want the Invoices sorted first
according to the CustomerID field (you don't want customer Jones's
items to be printed along with customer Smith's). So, as the first
sort, enter the CustomerID. Set it's Group Header to Yes. The Group
Footer can be Yes or No, depending upon your situation.
As the next sort, enter the Time field. Set it's Group Header and
Footer to No.

Next, set the CustomerID Header's ForceNewPage property (it's on the
Group's property sheet's Format tab) to BeforeSection.

Each Customer's Invoice will start on a new page. The detail will
print in the order the items were entered.

 

[paulkaye]

I have a feeling I haven't explained enough so let me expand here:

I have a form which I use to create invoices and a subform within that
which lists items to go on that invoice. I have created a 'button' on
the form which will send the open invoice record to a report and print
the report just for that invoice, including items on that invoice. The
original problem was that the items on the invoice were randomly
ordered. After the first replies to my question I realised that I had
an autonumber set up in the "Items" table and by including a
visible=no text box in the "Detail" section of the report, there would
be something to sort by (i.e. the order in which items are added).
However, when I sorted by this field, the printed report put each item
in the open invoice on a separate page. This is what I am trying to
resolve. Forgive me if I haven't understood your answer but it seemed
like I hadn't explained myself properly. I do not need to sort by
customer or anything else as only one invoice is sent to print. It is
just that I am getting a separate invoice for each item!

Thanks again - I look forward to your reply!

Paul

Thank you both - I'm sure I'm getting there. However when I used the
sorting and grouping dialogue I ended up with each item on a different
page with its own header and footer. I figured I should set the "Group
Header" and "Group Footer" to 'No' but received a warning that I was
going to delete something and got all nervous! Is there a way to sort
but not to group? There will be a different number of items on each
invoice so imagine I don't want to set a fixed 'Interval'.

Thanks in advance!

On Sun, 26 Aug 2007 11:06:08 -0700, paulkaye wrote:
Is there any way to control the order in which items appear on a
report (in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the same order as I
enter in the form. In fact, it is often the opposite order. The items
only have a name and a price; no natural order as far as I can think.
Thanks in advance for your patience with a noobie!
You'll have to add a [TimeEntered] field (DateTime datatype) to the
table.
Set it's DefaultValue property to = Now()
As you enter each value in the form the time of the entry will be
saved to the table along with your entry.
Then use this field in the Report's Sorting and Grouping dialog to
sort by. The entries will be printed in the order entered.

If you just wish to sort the records according to the Time field, you
set the sort to the time field and leave Group Header and Footer set
to No.

However as this is for Invoices, you do want the Invoices sorted first
according to the CustomerID field (you don't want customer Jones's
items to be printed along with customer Smith's). So, as the first
sort, enter the CustomerID. Set it's Group Header to Yes. The Group
Footer can be Yes or No, depending upon your situation.
As the next sort, enter the Time field. Set it's Group Header and
Footer to No.

Next, set the CustomerID Header's ForceNewPage property (it's on the
Group's property sheet's Format tab) to BeforeSection.

Each Customer's Invoice will start on a new page. The detail will
print in the order the items were entered.

 

[paulkaye]

I think I've worked something out. There HAS to be grouping in order
to use the header and footer that I've made. The original group was
"Invoice number" and all items with that same invoice number would
appear on one invoice page with its header and footer. What I'm trying
to do is to change the order of the individual items within the
Details section of that page.

Does that make any more sense now?

I have a feeling I haven't explained enough so let me expand here:

I have a form which I use to create invoices and a subform within that
which lists items to go on that invoice. I have created a 'button' on
the form which will send the open invoice record to areportand print
thereportjust for that invoice, including items on that invoice. The
original problem was that the items on the invoice were randomly
ordered. After the first replies to my question I realised that I had
an autonumber set up in the "Items" table and by including a
visible=no text box in the "Detail" section of thereport, there would
be something to sort by (i.e. theorderin which items are added).
However, when I sorted by this field, the printedreportput each item
in the open invoice on a separate page. This is what I am trying to
resolve. Forgive me if I haven't understood your answer but it seemed
like I hadn't explained myself properly. I do not need to sort by
customer or anything else as only one invoice is sent to print. It is
just that I am getting a separate invoice for each item!

Thanks again - I look forward to your reply!

Paul

Thank you both - I'm sure I'm getting there. However when I used the
sorting and grouping dialogue I ended up with each item on a different
page with its own header and footer. I figured I should set the "Group
Header" and "Group Footer" to 'No' but received a warning that I was
going to delete something and got all nervous! Is there a way to sort
but not to group? There will be a different number of items on each
invoice so imagine I don't want to set a fixed 'Interval'.
Thanks in advance!
On Sun, 26 Aug 2007 11:06:08 -0700, paulkaye wrote:
Is there any way to control theorderin which items appear on a
report(in this case, an invoice). The "details" section repeats for
each item but it does not necessarily appear in the sameorderas I
enter in the form. In fact, it is often the oppositeorder. The items
only have a name and a price; no naturalorderas far as I can think.
Thanks in advance for your patience with a noobie!
You'll have to add a [TimeEntered] field (DateTime datatype) to the
table.
Set it's DefaultValue property to = Now()
As you enter each value in the form the time of the entry will be
saved to the table along with your entry.
Then use this field in theReport'sSorting and Grouping dialog to
sort by. The entries will be printed in theorderentered.

If you just wish to sort the records according to the Time field, you
set the sort to the time field and leave Group Header and Footer set
to No.

However as this is for Invoices, you do want the Invoices sorted first
according to the CustomerID field (you don't want customer Jones's
items to be printed along with customer Smith's). So, as the first
sort, enter the CustomerID. Set it's Group Header to Yes. The Group
Footer can be Yes or No, depending upon your situation.
As the next sort, enter the Time field. Set it's Group Header and
Footer to No.

Next, set the CustomerID Header's ForceNewPage property (it's on the
Group's property sheet's Format tab) to BeforeSection.

Each Customer's Invoice will start on a new page. The detail will
print in theorderthe items were entered.