Solved

invoice numbering system

Posted on 2001-08-23
24
2,143 Views
Last Modified: 2012-05-04
High everyone I was wondering which was the best way to create an invoice numbering system.  I have built a database based on the order entry Microsoft template although it is now bears little resemblance and has grown considerable.  I have basically left the invoice report, orders by customs form and orders form alone.  The problem I have is creating a number invoice system for tax purposes as I have never done this before I would appreciate some guidance as to the best way to go.  My first thought was to use a auto number column set as the index key in a separate table, but this has the drawback that if anything should go wrong with the creation of an invoice it leaves the gap in the numbering sequence and the only way out of it would be to compact and repair the database.  Then I thought that you lads and lassies must have met this problem before. And could possibly point be in the right direction

thanks tony
0
Comment
Question by:tonyrees
  • 7
  • 5
  • 4
  • +5
24 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6417227
Hi tonyrees,

If not using Autonumber, then the better way is save the "number" in some way (database, registry, or a file), then retrieve it and add 1 to it to become new invoice number ?
0
 
LVL 5

Expert Comment

by:cekman
ID: 6417569
Try this,

In the BEFORE INSERT of your invoice get the next available number by using the DMax command.

Say your table is named 'tblInvoice' and the invoice number field is InvoiceNbr

x=dmax("invoicenbr","tblInvoice")

NewInvoiceNbr = X+1

Good Luck
CEKMAN
0
 
LVL 4

Expert Comment

by:abaldwin
ID: 6417672
The Dmax function will work for you but can cause problems in a multiuser environment.  

On closing the invoice form you may receive an error stating that a duplicate entry is trying to be made.  In which case I would account for adding another 1 to the previous calculated invoice and trying again.

Make sure you get a successful save before printing the invoice to insure the correct number being on the printed material.

Andy
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6417864
Normally, for control purposes in this type of application, pre-numbered pre-printed forms are used.. kinda like the Checks in your checkbook. The problem is to match the ACTUAL form number to the data printed on it in the Database.

Customarily what I do.. is somewhere on the preprinted form I print the AUTONUMBER field of the associated reacord.. and then in my print routine I add a form that requests the following AFTER the printing is done.

Beginning AUTONUMBER -> 99999
Ending AUTONUMBER -> 99999

Beginning DOCUMENT Number -> 99999
Ending DOCUMENT Number -> 99999

With that data.. I verify the ranges.. and then use the AUTONUMBERs entered to go back in and UPDATE the records with the ACTUAL DOCUMENT numbers used.

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 150 total points
ID: 6417944
I created a module that keeps track of the highest invoicenumber in a separate table.
The number is constructed in the module like yyyy##### allowing the user to set the (fiscal?)year when he wants.
As the value is stored in one table and "handed over" by the module, no duplicates will appear, even in a multiuser environment.

Drop me a line if you want a sample .mdb. (nico5038 "at" hotmail.com)

Nic;o)
0
 
LVL 6

Expert Comment

by:devtha
ID: 6417996
NICO exactly my idea..
0
 
LVL 5

Expert Comment

by:cekman
ID: 6418823
abaldwin,
No matter what method you use - you will have to make sure you don't have duplicates. This can be easily handled through your error routine - trap for the dup record error and keep adding 1 to x until you don't get a duplicate.

NICO & DEVTHA - My personal preference  is not to use a separate table - but that's just me....

CEKMAN
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6418922
Hi CEKMAN,

The main advantage of such a table is that the user is in control of the numbering.
He could e.g. decide to start each month/quarter at a new number.

On the other hand it's also possible without any number or table. By taking the Date/time (including seconds) and the UserID as Unique key for an invoice, duplicates are as good as impossible. (Haven't seen a user yet entering two invoices within a second!)

Nic;o)
0
 
LVL 6

Expert Comment

by:devtha
ID: 6418967
well we use to use InvNumber-AccountNumebr-Billdate combination...
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6419624
Adding to Nico5038's comment of "(Haven't
seen a user yet entering two invoices within a second!)".. And if they did.. upon the duplicate key error.. just change (update) the date-time and resubmit it again.. it won't take long for the record to be accepted.. <smile>.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6419706
I used that trick too in the past wsh2. But that was in a batch process on a mainframe where the datetime precision was set to a one thousands of a second...

Nic;o)
0
 

Author Comment

by:tonyrees
ID: 6419718
Hi, everyone
Well it amazes me that there are that there seems to be so many ways and opinions to doing something that every business has to do.  I did think in my naivety that there would be a standard foolproof way of achieving a consecutive number sequence for invoices, ticket numbering if only for the village fete etc.  It appears everyone has his or her own opinion.  Another thing that amazes me is that Microsoft does not have much to say on the topic, I would have thought they would have built-in some examples into their templates. It will take me a little time to evaluate the suggestions so far. I'm going to start looking at the DMax as suggested by Cekman
and nico suggestions. By way Cekman is there any reason why I should not put the Dmax field into the orders table of the database

Thank you all for your contributions so far, I will be back as fast as I can.

Tony
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:archery
ID: 6420635
I go along with ceckman's suggestion (but also optionally include an option for invoices to be numbered according to the year, as suggested ny nico5038).

I suggest that you don't actually generate the invoice number until the record is actually about to be created to allow for multiple users, otherwise an invoice number that you may have displayed on your form may not be the actual number finally generated.

I have taken a portion of some existing code that I use to demonstrate a possible solution for you. (You can ignore the actual SQL_String shown. It is only there to show you that IF the Currentdb.Execute command fails, the error routine is run and the loop continues until a valid number is generated, allowing for multiple users)

** N.B. glb_System is a hidden form that I have open
       
        tbl_Invoice contains (at least):
              InvoiceId      (Autonumber)    
              InvoiceNumber  (Long)

Dim Valid_Invoice As String * 1
Dim SQL_String AS String, LinkCriteria AS String
Valid_Invoice = " "
Do
    Valid_Invoice = "Y"

    ' Check if "year" forms part of Invoice # generation

    If glb_System.FiscalYear = 0 then
      glb_System.New_Invoice_Number = DMax
        ("InvoiceNumber", "tbl_Invoice") + 1
    Else
      LinkCriteria = "InvoiceYear = " &
        glb_System.FiscalYear
      glb_System.New_Invoice_Number = DMax
        ("InvoiceNumber", "tbl_Invoice", LinkCriteria) + 1
    End If

    On Error GoTo Invoice_Exists

    SQL_String = "INSERT INTO tbl_Invoice "
    SQL_String = SQL_String & "(InterimInvoice,
       InvoiceNumber, CustomerID, EmployeeID, "
     SQL_String = SQL_String & "InvoiceDate, SaleID,
       TermID, "
     SQL_String = SQL_String & "InvoiceTotal, "
     SQL_String = SQL_String & "InvoiceComments ) "
     SQL_String = SQL_String & "SELECT
       local_Invoice.InterimInvoice, "
     SQL_String = SQL_String &
       glb_System.New_Invoice_Number
     SQL_String = SQL_String & " AS Expr1, "
     SQL_String = SQL_String & "local_Invoice.CustomerID,
       local_Invoice.EmployeeID, "
     SQL_String = SQL_String & "local_Invoice.InvoiceDate,
       local_Invoice.SaleID , local_Invoice.TermID, "
     SQL_String = SQL_String
       & "local_Invoice.InvoiceTotal, "
     SQL_String = SQL_String
       & "local_Invoice.InvoiceComments "
     SQL_String = SQL_String & "FROM local_Invoice"
     SQL_String = SQL_String & ";"
                                   
     CurrentDb.Execute SQL_String
                   
Loop Until Valid_Invoice = "Y"

' Get the (AutoNumber) ID of the newly created invoice for use as the fireign key in associated tables
               
LinkCriteria = "InvoiceNumber = " &
     glb_System.New_Invoice_Number
               
     glb_System.New_Invoice_Id = DLookup
       ("InvoiceID", "tbl_Invoice", LinkCriteria)


 .......... lots of other code here

Exit Sub

Invoice_Exists:

    Valid_Invoice = "N"
    Resume Next
0
 
LVL 5

Expert Comment

by:cekman
ID: 6421919
tonyrees,
DMAX is a function - not a field - so it does not 'go' in the invoice table itself.  Rather - it is a function that will return the highest invoice number currently loaded in your table.

Regarding other comments that would allow to create an invoice by year - you can also do this with the DMAX function - just create your criteria....

From my previous example...

Say your table is named 'tblInvoice' and the invoice number field is InvoiceNbr, and the year is InvYr
Dim a new variable called strFIND

dim strFind as string

strFind = "[InvYr] = " & yearyouareworkingin

                     x=dmax("invoicenbr","tblInvoice",strFind)  'This will now return the highest invoice number for the selected year

                     NewInvoiceNbr = X+1

Hope this helps
CEKMAN
0
 

Author Comment

by:tonyrees
ID: 6422279
Sorry cekman

I did not make myself very clear I am aware that it is a function what I meant was could you see any problem in carrying out the DMax function on a field called invoice number set in the orders table.  The only reason I asked was the cause the SQL which the invoice report is generated from is already choker block and the thought occurred to me that if I could generate the invoice numbers in an existing table it would save overloading this query as I said in the original question this database was originally based on order entry Microsoft template

Thanks for your help so far
I am following it all ?just? %-)

tony


0
 
LVL 5

Expert Comment

by:cekman
ID: 6422705
Sorry for the misunderstanding....

The DMAX function shouldn't need to go in your query - IF I'm understanding what you need.  Here's my take on the your question....

You have a form that allows the user to create an invoice. As part of that creation you need a new invoice number created - but you don't want gaps if the user 'cancels' the invoice creation.

If this is corrrect - then my suggestion is to not obtain the new invoice number until the invoice is actually saved.

If your form has a SAVE button - get the invoice number via the DMAX function as the last step of the save.

If the invoice is saved when the user moves off the record (ie automatically saved when the form closes etc) then the DMAX function could be put in the BEFORE INSERT event of the form. This would then be the last thing performed before the new record is created.

There is a variety of ways to approach this - but I can't think of any that would use the DMAX function as part of the query itself.

If I'm way off here I apologize!
CEKMAN
0
 

Author Comment

by:tonyrees
ID: 6423521
Hi all

OK the way the Order Entry Microsoft template works as far as the orders and invoicing is concerned is when you make up a new order you fill out the customer details (customer table)you then open the order form and pick from a list of your products (Products Table) and so on and so on, I am sure you get the idea.
You can then cick a button called Preview invoice and up pops a REPORT called surprise surprise Invoice, This is a REPORT not a form, the SQL which makes up this REPORT already has 7 tables with more fields than Bill Clinton had girl friends, which I think is a bit over the top, but it works well enough,(the report that is, not Bill!) I just thought better not add another table called invoice_No.  then I thought why not add another field (Invoice_No.)to a table which was aready in the SQL of the report, the obvious candidate is the order details table. Anyway the idea is, at the same time the preview report opens so would a form with a button (Create invoice number)only if you are happy with the preview. This number and only this number would then always be associated with only that invoice. I live in the UK and the Taxman goes mad if invoice numbers have gaps, they think you might not be telling them all, and you have large off-shore bank accounts. (I WISH)

Sorry if I have confused you. 8-*

Thanks Tony  
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6423722
tonyrees..

Again.. these numbering document controls are almost always based on PRE-NUMBERED PRE-PRINTED forms. If this is the case, then many of the above recommendations will NOT satisfy this requirement.

I strongly recommend that you consult with your Accounting people before proceeding further.
0
 

Author Comment

by:tonyrees
ID: 6424385
Hi all

OK the way the Order Entry Microsoft template works as far as the orders and invoicing is concerned is when you make up a new order you fill out the customer details (customer table)you then open the order form and pick from a list of your products (Products Table) and so on and so on, I am sure you get the idea.
You can then cick a button called Preview invoice and up pops a REPORT called surprise surprise Invoice, This is a REPORT not a form, the SQL which makes up this REPORT already has 7 tables with more fields than Bill Clinton had girl friends, which I think is a bit over the top, but it works well enough,(the report that is, not Bill!) I just thought better not add another table called invoice_No.  then I thought why not add another field (Invoice_No.)to a table which was aready in the SQL of the report, the obvious candidate is the order details table. Anyway the idea is, at the same time the preview report opens so would a form with a button (Create invoice number)only if you are happy with the preview. This number and only this number would then always be associated with only that invoice. I live in the UK and the Taxman goes mad if invoice numbers have gaps, they think you might not be telling them all, and you have large off-shore bank accounts. (I WISH)

Sorry if I have confused you. 8-*

Thanks Tony  
0
 
LVL 5

Expert Comment

by:cekman
ID: 6428456
Couldn't  you preview your invoice - and if you like it - close the invoice preview and return to your input form. Then on your input form you could have a button called 'ASSIGN INVOICE NUMBER'. This could then go get the next number, assign it to the invoice record and save it.

Just a thought?
Good Luck
CEKMAN
0
 

Author Comment

by:tonyrees
ID: 6442808
Hi all,

Right cracked it! thanks to just about everyone that's contributed.  What everybody has suggested is correct in different environments.  The particular application that this question was aimed at was actually for a small high value low volume part time business which might generate 20 or 30 invoices a month which makes pre-printed pre-numbered invoices not cost effective but this does not make wsh2 comments invalid in a busier environment.  

Nico5038 was kind enough to e-mail me her demo from which I've learnt a lot for which I thank her.  Nico your comment that really it is 'horses for courses' I think is very true.

I have ended up using bits from everyone's contributions and therefore will be posting extra points in this topic area for various contributors.  Watch this space!
I let the topic go outside my original remit as a lot of comments were made which hopefully will be useful to other people who will view this question in the future.

I am going to award the original points to Nico as the work and the commented demo she sent me was far and beyond the call of duty however, as I said above I will be awarding extra points.

Thanking you all again
Tony
0
 

Author Comment

by:tonyrees
ID: 6442815
Hi all,

Right cracked it! thanks to just about everyone that's contributed.  What everybody has suggested is correct in different environments.  The particular application that this question was aimed at was actually for a small high value low volume part time business which might generate 20 or 30 invoices a month which makes pre-printed pre-numbered invoices not cost effective but this does not make wsh2 comments invalid in a busier environment.  

Nico5038 was kind enough to e-mail me her demo from which I've learnt a lot for which I thank her.  Nico your comment that really it is 'horses for courses' I think is very true.

I have ended up using bits from everyone's contributions and therefore will be posting extra points in this topic area for various contributors.  Watch this space!
I let the topic go outside my original remit as a lot of comments were made which hopefully will be useful to other people who will view this question in the future.

I am going to award the original points to Nico as the work and the commented demo she sent me was far and beyond the call of duty however, as I said above I will be awarding extra points.

Thanking you all again
Tony
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6442908
Thanks Tony,

Just a bit strange to read of my sexchange.
I hope my wife won't throw me out of the house ;-)

But glad to hear that the sample gave you some new insights!

C U

Nic;o)
0
 

Author Comment

by:tonyrees
ID: 6447965
Sorry to have mixed up your gender

I have increased the points to 150 as compensation %-)

I keep having trouble submiting comments to this site I press the submit button and nothing happens even after 5 min, hence some of my comments pop up twice.

Once again

Thank ONE AND ALL

Tony (Male);-)

 
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now