Invoice Numbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a bit of a brian-fart, but here goes.
I am looking for a way to generate invoice numbers without ever repeating.
What I think is the best way is to generate a table, call it
lastinvoicenumber. That table will have only one field, lastinvoicenumber.
That way I can write a code to pull that number when I open a jobform,
increment it by 1, write it to the invoice table when I save it, then
re-write the new number back to the lastinvoicenumber table for safekeeping.

Sounds easy, but for some reason, I cannot get it to work that way.

Can someone help me with the basic code?
 
you can do that, but it might be just as easy to run a DMax() function to
create your new invoice number, as

DMax("InvoiceNumberField", "InvoiceTableName") + 1

hth
 
I might suggest the following:

Create a table called "tblInvoiceNumbers". Have 2 fields
Field name: InvoiceID, Field type: Autonumber
Field name: dt, Field type: Date/Time

(dt is short for date/time stamp)

When you need to acquire a new invoice number:

1. Set a variable to the function Now() (this is the current date/time on
your system)

2. Create a query that inserts a new record into the table
"tblInvoiceNumbers"
example:

Public Sub InsertRecord( Byval dt as DateTime )

Dim dbs as DAO.Database

Set dbs = CurrentDB
dbs.Execute "Insert Into tblInvoiceNumbers (dt) Values (" & dt & ")"

set dbs = nothing

End Function

When a record is inserted, the record is assigned a unique number because
of the autonumber feature.

3. To retrieve what the autonumber value is:

Public Function GetInvoiceNumber (byval dt as DateTime)

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset ("Select InvoiceID From
tblInvoiceNumbers Where dt = '" & dt & '")"

GetInvoiceNumber = rst("InvoiceID")

Set rst = Nothing
Set dbs = Nothing

End Function


Now, in the event you need to start the invoice numbers at a different
number:

Have Autonumber field start from a value different from 1
http://www.mvps.org/access/tables/tbl0005.htm

HTH

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
 
Thanks Tina,

I am very NEW at coding access and though I can do the simple stuff, getting
a value from a table.. pulling it into the form.. then writing it back to the
table .. is where I am a bit lost.

I read the other post and thought it generates a unique number, it's not
exactly what I was looking for.

I was thinking of :
Dim nextinvoice as integer
dim invoicenumber as database
set invoicenumber = tblnextinvoice

Then I go fetch the invoice number.. store it in nextinvoice...
then increment by 1
post it to the form
save the record to the invoice table
rewrite the new invoice number ( which was incrimented by 1) back to the
invoicenumber table for the next retrieval.

Did that make sense?
 
yes, i understood your premise. but that's a lot of coding to do a very
simple thing:
What I think is the best way is to generate a table, call it
lastinvoicenumber. That table will have only one field,
Click to expand...
lastinvoicenumber.

so you have to create a table, whose sole purpose (if i can use "who" for a
That way I can write a code to pull that number when I open a jobform,
Click to expand...

in the data entry form, now you have to write code to retrieve the value
that's in the above described table. that means either using a DLookup()
function, or opening a DAO.Recordset, in VBA.
increment it by 1, write it to the invoice table when I save it,
Click to expand...

then assign the new, calculated value to the new record in your form.
then
re-write the new number back to the lastinvoicenumber table for
Click to expand...
safekeeping.

and now update the single-record table with the new value *that's already
stored in the data table.* that means either running an Update query, or
executing a SQL statement, or again opening a DAO.Recordset, in VBA. the
whole of the above operation will take several lines of code, plus the
overhead of an additional table in your database that you *don't need*.

wouldn't it be a whole lot easier to just retrieve the highest number from
your data (invoice) table with a DMax() function, add 1 to it, and assign
the calculated value to the new record in your form? example:

Me!InvoiceNumberField = DMax("InvoiceNumberField", "InvoiceTableName") +
1

(the above all goes on one line of code, of course.) that's it - one line,
and you're done. and there's absolutely no advantage that i can see of using
an extra table and all those extra steps, over the one simple line of code
above.

hth
 
Yes, it is a possible solution to find the highest value and then increment
it by one and then write it back.

However... (although it could be slim), if for some reason the value does
NOT get written back then you risk applying duplicate invoice numbers into
your system.

Yes, the approach I gave you does have some coding involved. At the very
least, you can at least be guarenteed unique values for your invoice
numbers.

The luxury of newsgroups is the assessment of responses and solutions. Given
the information, make the choice you feel best provides your needs.

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
 
Rob, you are correct, which Is why I am asking for help.

Tina, if I understand your method, but pulling it once. incrementing, then
how do I save the new number to incriment by one for the next time? are you
suggestinng I just assing the first number manually in the invoice table..
then nab the highest one it finds?
 
Tina, if I understand your method, but pulling it once. incrementing,
then
how do I save the new number to incriment by one for the next time?
Click to expand...

Optomyst, you're already saving each incremented invoice number *in the
invoice table*. why do you think you need to save it somewhere else as well?
and since you would have to retrieve it from that "somewhere else", why do
you think that you can't just as easily retrieve it from the invoice table?
are you
suggestinng I just assing the first number manually in the invoice table..
then nab the highest one it finds?
Click to expand...

you have to "hard-code" the "first" invoice number anyway - regardless of
whether you enter it manually in a LastInvoiceNumber table, or write it into
the code, as

Me!InvoiceNumberField = Nz(DMax("InvoiceNumberField",
"InvoiceTableName"), 0) + 1

(again, the above is all on one line.)

look, just think through your process:
you enter a value of 0 in your LastInvoiceNumber table.
you enter the first invoice record in a form, where you run a DLookUp()
function to retrieve the value, which is currently 0.
you increment the value by 1, so 0 + 1 = 1.
you assign the incremented value 1 to the invoice record in the form.
you run an Update query to write the new value 1 back to table
LastInvoiceNumber.

you enter another new invoice record in the form, and run a DLookUp()
function to retrieve the value from table LastInvoiceNumber, which is
currently 1.
you increment the value by 1, so 1 + 1 = 2.
you assign the incremented value 2 to the invoice record in the form.
you run an Update query to write the new value 2 back to table
LastInvoiceNumber.

you enter another new invoice record in the form, and run a DLookUp()
function to retrieve the value from table LastInvoiceNumber, which is
currently 2.
you increment the value by 1, so 2 + 1 = 3.
you assign the incremented value 3 to the invoice record in the form.
you run an Update query to write the new value 3 back to table
LastInvoiceNumber.

you continue doing this, each time incrementing the last invoice number by
one, to assign the new invoice number. you could just as easily have
manually entered 499 in the LastInvoiceNumber table, to start your invoice
numbers at 500.

okay, now think through my process:
you enter the first invoice record in a form, where you calculate the value
of the invoice number from the expression

Nz(DMax("InvoiceNumberField", "InvoiceTableName"), 0) + 1

because it's the first invoice record, there is no "maximum" invoice number
in the table. so the Nz() function returns 0, to which 1 is added. so 0 + 1
= 1.
the invoice record is saved with an invoice number value of 1.

you enter another new invoice record in the form, where you assign the
invoice number from the expression

Nz(DMax("InvoiceNumberField", "InvoiceTableName"), 0) + 1

the DMax() function returns the highest invoice number in the table, which
is 1, to which 1 is added. so 1 + 1 = 2.
the invoice record is saved with an invoice number value of 2.

you enter another new invoice record in the form, where you assign the
invoice number from the expression

Nz(DMax("InvoiceNumberField", "InvoiceTableName"), 0) + 1

the DMax() function returns the highest invoice number in the table, which
is 2, to which 1 is added. so 2 + 1 = 3.
the invoice record is saved with an invoice number value of 3.

you continue doing this, each time incrementing the last invoice number by
one, to assign the new invoice number. you could just as easily have written
the expression to return 499 on the first "run", to start your invoice
numbers at 500, as

Nz(DMax("InvoiceNumberField", "InvoiceTableName"), 499) + 1

by comparing the two processes, step by step, you see that each one gives
you a invoice number of 1 for the first invoice record, the next invoice
record is assigned an invoice number of 2, the next invoice record gets a 3,
etc, etc, etc, etc. it doesn't matter if you want your invoice records'
invoice numbers to start with 1, or 100, or 50,000. you still have to "seed"
the first record in the invoices table, so you can seed it with whatever
beginning value you choose. and then each new invoice record is assigned an
invoice number that's incremented by 1, which is what you've been saying
that you want.

your choice is *not* between using one line of code *or* getting the
incremented invoice numbers you want. your choice is between getting the
incremented invoice numbers you want by using one line of code *or* by using
several lines of code and an additional table in the database.

hth
 
Tina,

The "auto number" attached to an ID field, is not the same. I want to start
the invoice numbers other than 1..2..3.. I don't believe there is a way to
start the 'autonumber" from 4000, which is where I want to start. Secondly,
as I grow this database, I may add work order numbers, sales order numbers,
which I would like to store the last number used in the same table as the
last invoice number used. That way I can implement and incorporate my data
starting from any number I so desire, so long as I update the "last number"
used table.

Confused yet :)
 
i haven't mentioned the Autonumber data type, and i don't see that it is
connected to the discussion we've been having. i've explained this to you
the best way i know how, i'm sorry if it's not clear. there's nothing new i
can say on the subject, so i'll just say "good luck with your project". :)
 
Back
Top