Full Version: Next Invoice Number
UtterAccess Forums > Microsoft® Access > Access Forms
I have created a form where there is a field name "Invoice Number"
i have entered too much data and therefore i cannot remember the last invoice number i have entered.
want several options:
invoice number set to next number automatically from the last record i.e. IN-018/01 TO IN-019/01
and also one field that shows the last invoice number i entered.
What is the basis for your invoice #? You use an Autonumber (identity , SQL ) field and then a date:
nvoice = "IN" + Format(DATE, "dd/mm" + " " + FieldID ' FieldID is the autonumber field from the table.
If you need to restart number for each day you could use something like the following:
' reset control number each month
strRequestdate = CStr(DatePart("yyyy", Now())) & CStr(DatePart("m", Now()))
sql = "SELECT Max(tblTelephoneControlLog.ControlNum) AS MaxOfControlNum, tblTelephoneControlLog.YearMonth " & _
"FROM tblTelephoneControlLog GROUP BY tblTelephoneControlLog.YearMonth HAVING tblTelephoneControlLog.YearMonth = """ & strRequestdate & """"
Set db = CurrentDb()
Set rst = db.OpenRecordset(sql)
If not rst.EOF() then rst.MoveLast
If rst.RecordCount = 1 Then
If IsNull(rst!MaxOfControlNum) Then 'Ensure record doesn't have a null value, number value is stored as a str
cSer = 1
cSer = rst!MaxOfControlNum ' capture the last number
End If
nSer = CInt(cSer) ' convert number to integer , can also Clng to convert to a long
nSer = nSer + 1 ' index by one
nSer = 10001 * nSer
cSer = Right(CStr(nSer), 4) ' format number
cSer = "0001" ' first of the month return 1
End If
Me.ControlNum = cSer ' load values
Me.Prefix = Me.TCOPrefix
Me.YearMonth = strRequestdate
You then can combine the above to return your invoice number. Please note the ControlNum field only stores a number value.
WARNING: In a multiuser enviroment you can run the risk of having the same number loaded twice. To help prevent this the new ControlNum has to be written to the table immediately, and you can still end up with duplicates. With the above example, only one user is in the table at a time, and there is code preventing others from requesting a control number, until it is given
In my opinion I would use just a autonumber, unless you have some driving factor requiring you to use something else. For instance, a QC program, I am working on, requires the year to be added to all Control Numbers.
hi, ranjit
would refer to the great Function in Access Code Archive Custom AutoNumbers uses for generating the Custom autonumber function and handling the Mulit-users issues
Hope this helps...
>> and also one field that shows the last invoice number i entered. <<
Why would this be needed? ... the information might be nice, but you definately do not need a field to store this info.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.