Generating Invoice number (1 Viewer)

Stefanovski

Registered User.
Local time
Today, 14:49
Joined
Jan 7, 2005
Messages
21
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

Registered User.
Local time
Today, 16:49
Joined
Jan 29, 2003
Messages
3,672
Typically you would STORE the two parts as either numbers or stings in your invoice. Then just concatenate them for display.
 

Dreamweaver

Well-known member
Local time
Today, 21:49
Joined
Nov 28, 2005
Messages
2,466
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

Code:
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
 
Last edited:

Stefanovski

Registered User.
Local time
Today, 14:49
Joined
Jan 7, 2005
Messages
21
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
 

Bat17

Registered User.
Local time
Today, 21:49
Joined
Sep 24, 2004
Messages
1,687
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

AWF VIP
Local time
Today, 21:49
Joined
Jul 22, 2004
Messages
1,649
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

Registered User.
Local time
Today, 21:49
Joined
Sep 10, 2001
Messages
354
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

Registered User.
Local time
Today, 14:49
Joined
Jan 7, 2005
Messages
21
John.Woody said:
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

Registered User.
Local time
Today, 21:49
Joined
Sep 10, 2001
Messages
354
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
 

Users who are viewing this thread

Top Bottom