Adding consecutive numbers to a field in a report page header

[Wendywoo]

I am creating a report which generates all the invoices for a specified
period of time. I have set each company's invoice to appear in the Page
Header and want to include a field which will show the invoice number. When
the report is run, I want to be able to enter the last invoice number and
then for Access to automatically increase each company's invoice number by
one.

 

[Marshall Barton]

I am creating a report which generates all the invoices for a specified
period of time. I have set each company's invoice to appear in the Page
Header and want to include a field which will show the invoice number. When
the report is run, I want to be able to enter the last invoice number and
then for Access to automatically increase each company's invoice number by
one.

You should have the invoice number entered with the data for
the invoice, not just printed on a piece of paper.
Entering/calculating it when the report is printed can cause
all kinds of difficulties when the printer jams or runs out
of paper or ink.

You can calculate a "next available" invoice number in the
BeforeUpdate event of the form used to create a new
invioce's data:

Me.invoicenumberfield =
Nz(DMax("invoicenumberfield","invoicestable"),0)+1

 

[Wendywoo]

Thanks for the advice. Are you able to explain more fully what you mean by
"You should have the invoice number entered with the data for the invoice,
not just printed on a piece of paper" (but can appreciate your concern about
problems with paper jams.)

My report that generates the invoices is based on a query, which in turn is
based on the fields from 3 tables (2 of which are the basis for a form and
subform) - none of which have an invoice number field.

I have used a text box in the Page header of the report and set the Control
Source to Invoicenumber, and followed your instructions for the Before Update
event on the form but have ended up with the same result I got when I entered
=[Invoice No]+1 in the Control Source of my original Invoice No text box,
which is a Paremeter Value prompt for an invoice number when I run the
invoice report (which I want so I can type in the last invoice number used),
but when I have tried it both your way and my way I get the same invoice
number appear on every invoice in the report. What I don't want to have to
do is to create a separate report for each invoice, as this seems an
unnecessary duplication of work.

Any further advice would be most appreciated. Thanks

 

[Marshall Barton]

Thanks for the advice. Are you able to explain more fully what you mean by
"You should have the invoice number entered with the data for the invoice,
not just printed on a piece of paper" (but can appreciate your concern about
problems with paper jams.)

My report that generates the invoices is based on a query, which in turn is
based on the fields from 3 tables (2 of which are the basis for a form and
subform) - none of which have an invoice number field.

The problem is that the invoices table needs to have an
invoicenumber field. Then the form with the invoice header
information can use its BeforeUpdate event to set the field.

You can scrap the invoices table's autonumber field and use
the invoicenumber field as its primary key. The invoice
details table would then use the invoicenumberfield's value
in its foreign key field through the details subform
control's Link Master/Child properties.