Generating Invoice number

[Stefanovski]

Hi all,

I have a small .mdb for invoicing. Till now I had to put invoice number, which had two parts, manually for the first part, for example 001 or 002 and automatically for the second part as Date part. So I had, for example 2006 or 2007. The complete number was, for example 001/2006 etc.

The problem was New Year. Now, all previous invoices from 2006 have 2007 extension and the first parts are not starting form 001 but they continue.

Do you know any better solution for this because this is actually no solution at all.

Thanks a lot.

 

[FoFa]

Typically you would STORE the two parts as either numbers or stings in your invoice. Then just concatenate them for display.

 

[Fifty2One]

there is a code that works at techonthenet to create cleaver sequential numbers...
http://www.techonthenet.com/access/modules/sequential_nbr2.php

 

[Dreamweaver]

Thats very simler to what I use but I also added a random number 1-99 on the end as have a few problems with a networked db

This is mine

Function GenCodeID(IntNum As Integer) As String
Dim IntProdNum As Long
Dim strProdLet As String
Dim intRandom As Integer
Dim rst As Recordset
Dim StrSQL As String
Dim recCode As Recordset
Dim DB As Database
Dim strSet As String

StrSQL = "SELECT * FROM StblSystemID WHERE ID =" & IntNum

Set DB = CurrentDb()
Set recCode = DB.OpenRecordset(StrSQL, dbOpenDynaset)

strProdLet = recCode("Letters")
IntProdNum = recCode("LastNumber")
Randomize
intRandom = Int((99 * Rnd) + 1)
GenCodeID = strProdLet & IntProdNum & "-" & intRandom

recCode.Edit
recCode("LastNumber") = IntProdNum + 1
recCode.Update
recCode.Close
DB.Close
End Function

 

[Stefanovski]

Thank you guys very much for your support but unfortunately it is not quite what I need.

According to the law in my country, invoices must have "001/2006" form or similar.

That means, you have to be able to see from which year is an invoice by simply looking at its invoice number. But as I explained above, it is impossible for me to do it by using AutoNumber (for the first part, that is way I input data manually) and DatePart (for the second part because year changes every year).

The linked sample database helps me a little with the first part but how can I use the second part ("2006" or "06") and how can I make it that with each new year the first part starts from "001", for example.

Thank you again

 

[John.Woody]

In the post below I have placed some code which will increment invoice numbers by 1. If your invoice numbers are split into number field and year field You could modify this to look at the max for a particular year and increment by one. If it were null it would start at 1 again.
http://www.access-programmers.co.uk/forums/showthread.php?t=119368

HTH
John

 

[Bat17]

You wont even need to store the year as you can pull it directly from the invoice date!

you combine your number and year together for display and printed reports

Peter

 

[Dennisk]

Hi,
If you want to generate your own autonumbers, then create a table with one column to hold the number and use the following function.

Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long)


Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblAutoNumber", dbOpenDynaset, dbDenyRead)

rst.Edit
rst!AutoNumber = rst!AutoNumber + 1

'GetNextAutoNumber = Right$("0000" & rst!AutoNumber, LEN_OF_CASENOTE_NUMBER)
GetNextAutoNumber = rst!AutoNumber
rst.Update

Clean_Exit:
rst.Close
db.Close
Exit Function

Error_Handler:
If Err = TABLE_LOCKED Then
' Pause and try again
Sleep 20
Resume ' try to open the table again
Else
MsgBox "Error " & Err & " " & Err.Description
Resume Clean_Exit
End If

End Function


Don't use Dmax(lastinvoiceNumber) in a multi user environment as there is always the chance of the same number being returned from two different users. - Seen this happen.

 

[John.Woody]

Study my post. It gets round the multi user issue and avoids a seperate table for storing the invoice number. Peter's right if you do it correctly you need not store the year as long as you have a field with your invoice date in it.

 

[Stefanovski]

In the post below I have placed some code which will increment invoice numbers by 1. If your invoice numbers are split into number field and year field You could modify this to look at the max for a particular year and increment by one. If it were null it would start at 1 again.
http://www.access-programmers.co.uk/forums/showthread.php?t=119368

HTH
John

Thanx John, but how can I call this procedure? On which event?

 

[John.Woody]

The proceedure is placed in a module. You need to call the code when the form is updated and you want the number to stick. My system creates a quote assuming the customer is going to buy some products. If they decide not to buy the record is deleted when the form is closed. If they decide to buy then the user presses a button to create the invoice, that button calls the procedure

NextInvoiceNo
'and then opens the report previewing the invoice

HTH
John