Starting Invoice Number!

[Bob]

I have 2 tables tbInvoice and tbCompanyInfo which has a form CompanyInfo
I know it I go to tbInvoice and type in 1000 in InvoiceNo. all my Invoice
incriminate from there. is it possible to have a text box in frmCompanyInfo
that I can type in a number to start my Invoices from scratch


Thanks in advance.........Bob Vance

 

[strive4peace]

Hi Bob,

yes, that is possible... but Autonumbers should not be used for user
data. They will skip -- and you will wonder what happened to those
invoices!

It is better to have an autonumber InvoiceID for linking your data, and
then a user InvoiceNo (don't use periods in field names) that is
sequentially assigned for the users to use.

For instance, on the form BeforeUpdate event:

'~~~~~~~~~~~~~~~

dim StartValue as long
StartValue = nz(forms!frmCompanyInfo!StartValue_controlname, 999)

me.InvoiceNo = nz(dMax("InvoiceNo","tbInvoice"),StartValue-1) + 1

'~~~~~~~~~~~~~~~

just in case there are multiple users, you may want to repeat this on
the form BeforeUpdate event so, in case the InvoiceNo you have already
calculated is used, it gets a new one...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Bob]

The problem with that is some invoices have a zero invoice number, you have
an option to assign a invoice number or not.
thanks Bob

 

[strive4peace]

Hi Bob,

The ability to generate invoices without a number is another reason to
use an autonumber InvoiceID to link records and just generate InvoiceNo
for your records. InvoiceNo will not be necessary for the invoice
record unless you make it a rule -- so invoices with no number would be
okay.

Regardless of whether or not there is an InvoiceNo, there would always
be an InvoiceID. since it would be an autonumber field used behind the
scenes.

With a few exceptions, every table should have an autonumber field that
is used for linking and positioning the record pointer, in my opinion.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*