Creating an auto invoice no!

[Nightman]

I am creating an Invoice and I wish to add a invoice no. to every invoice I
print.

Following condtions should be met.

- Click on a button from the order form will create an invoice report
- Invoice no. should be unique and bound to the field orders.InvoiceNo
- Invoice no. should be automatically generated and incremented for every
invoice I print
- Invoice no. can be overwriten by the user in the order form. (invoice no.
should not be incremented for this Invoice, if the invoice no is already
exist) only the InvoiceNr value "0" or null allow an increment and adding a
invoice no.

Is there any simple way to do it?

Thank you

regards
Nathan

 

[TC]

Why do you want the user to be able to manually enter an Invoice
Number?

Is it because you want the user to be able to edit an existing Invoice?

TC

 

[Nightman]

This is because, in case the the printed invoice containg any wrong data, it
should be reprinted with the same invoice no. I do not want it incremented
in this case.

 

[John Vinson]

I am creating an Invoice and I wish to add a invoice no. to every invoice I
print.

Following condtions should be met.

- Click on a button from the order form will create an invoice report
- Invoice no. should be unique and bound to the field orders.InvoiceNo
- Invoice no. should be automatically generated and incremented for every
invoice I print
- Invoice no. can be overwriten by the user in the order form. (invoice no.
should not be incremented for this Invoice, if the invoice no is already
exist) only the InvoiceNr value "0" or null allow an increment and adding a
invoice no.

Is there any simple way to do it?

Do all your data entry with a Form, of course.

In the Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Orders]")) + 1
End Sub

assuming that the form control txtInvoiceNo is bound to the invoice
number table field.


John W. Vinson[MVP]

 

[Nightman]

John,
The increment is working fine. But it allow the same InvoiceNo to be assign
to multiple records. I need it to be unique. Any help?

regards
Nathan

I am creating an Invoice and I wish to add a invoice no. to every invoice
I
print.

Following condtions should be met.

- Click on a button from the order form will create an invoice report
- Invoice no. should be unique and bound to the field orders.InvoiceNo
- Invoice no. should be automatically generated and incremented for every
invoice I print
- Invoice no. can be overwriten by the user in the order form. (invoice
no.
should not be incremented for this Invoice, if the invoice no is already
exist) only the InvoiceNr value "0" or null allow an increment and adding
a
invoice no.

Is there any simple way to do it?

Do all your data entry with a Form, of course.

In the Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Orders]")) + 1
End Sub

assuming that the form control txtInvoiceNo is bound to the invoice
number table field.


John W. Vinson[MVP]

 

[Rick Brandt]

John,
The increment is working fine. But it allow the same InvoiceNo to be
assign to multiple records. I need it to be unique. Any help?

BeforeInsert is not an appropriate event in a multi-user environment. Use
BeforeUpdate instead.

 

[TC]

But if you want to edit an Invoice, you should go to a form which lets
you recall that Invoice, by Invoice Number (for example), and lets you
edit the relevant fields, /but not/ edit the invoice number.

You are going the wrong way, IMHO, if you let a user edit an /invoice
number/. When they create a new invoice, the system should detemine
that number automatically. When they edit an existing invoice, the
system should not let them edit that number, at all.

HTH,
TC

 

[John Vinson]

John,
The increment is working fine. But it allow the same InvoiceNo to be assign
to multiple records. I need it to be unique. Any help?

Make InvoiceNo the Primary Key of the invoice table (preferable), or
use the Index property of the field to uniquely index it.

John W. Vinson[MVP]

 

[Nightman]

What I am trying to do is ofcourse not tring to edit the invoice, but rather
the invoice no. in the order form.
I do not want to put the Invoice no. manually. I wanted the script to
generate the no. and the no. should existing in continues form. so every
time I need to find a highest no. and add one to that, so the Invoice no is
printed on the invoice.

If I find out for any reason the invoice containing a wrong data, I need to
go back to the order form, correct the data and print the new invoice. in
this case my invoice no. should remain the same.

There is another problem as well, since not all orders have invoices(if the
coustomer not paid or the cancelled), I am difficult to find a way to
generate an invoice no. only when the invoice is printed. if no invoice been
printed, the invoice no. should remain blank.

 

[Nightman]

Hi,
I am the only user. so there will be no issues with the multi user problems.

regards
Nathan

 

[Nightman]

Hi,
There are no invoice table. only the order table the promary key is already
assigned to orderID
I tried to use the index property, but access not allowing me to do it since
there other relation involved.

 

[TC]

You say you need to be able to correct the order data and then print
the new invoice; "in this case my invoice no. should remain the same".

Sure - I understand that. But you suggested in your original post, that
the user had to do this manually; ie. they have some degree of /manual/
control over the value of the invoice number. "Invoice no. can be
overwriten by the user in the order form."

In my opinion, there is no case where the user should /ever/ have
manual control of an Invoice # field, except when they are entering an
invoice # to do a query on. In all other cases, the invoice # should be
managed automatically, by the software, without any user intervention.
If a new invoice # is required, eg. when a new invoice has been
generated, the software should do that automatically. But /conversely/,
if a new invoice # is /not/ required, eg. when an existing invoice is
being reprinted, the software should recognize that case, and just not
generate a new number.

In other words, it shouldn't be up to the user to decide whether he
will or will not overwrite the invoice number. You should neither
require, nor allow, him to do that, IMHO.

Cheers,
TC

 

[TC]

Er - how can you have an invoice in a single table?

Typically, an Invoice requires at least /four/ tables: InvoiceHeader,
InvoiceDetail, Customer, and Product.

What table structure are you using? What is the primary key of each
table?

HTH,
TC

 

[John Vinson]

Hi,
There are no invoice table. only the order table the promary key is already
assigned to orderID
I tried to use the index property, but access not allowing me to do it since
there other relation involved.

Well, I'm with TC then - what is the structure of your database? If
you want to have a unique invoice number, and if you're recording
information about invoices (e.g. date issued), I don't see how you can
AVOID having an Invoice table, at least not without violating database
integrity!

John W. Vinson[MVP]

 

[TC]

My bet is, his (one?) table actually contains an Order, using repeating
fields for the line-items. Then, if necessary, he turns it into an
invoice, by setting a value into the Invoice # field. Or somesuch!


TC