Assuming you have a table called ‘NextNumber’ where you store your incremental number. Assume your table has the following fields(columns) ("DocPrefix","YearPrefix","MonthPrefix","NextNo","mCounter")
you can try this code segments:
‘
Public Function GetNextAutoNo() As String
Dim mstrDocPrefix, mstrMonthPrefix, mstrNextNo As String
Dim intYearPrefix, intmCounter, intNewmCounter As Integer
Dim cmdNextAutoNumber As OleDbCommand = gbConn.CreateCommand
Dim sqlGetNextAutoNo As String = "SELECT * FROM NextAutoNo"
Dim drDataReader As OleDbDataReader
'*********Initialise Variables
GetNextAutoNo = ""
'
gstrDocPrefix = "" : mstrDocPrefix = ""
gbstrMonthPrefix = "" : mstrMonthPrefix = ""
gbstrNextNo = "" : mstrNextNo = ""
gbintYearPrefix = 0 : intYearPrefix = 0
gbintCounter = 0 : intmCounter = 0
'Note: gstrDocPrefix,gbstrMonthPrefix,gbstrNextNo,gbIntYearPrefix, and gbintCounter are global variables declared outside this public function
Try
OpenDBConnection()
With cmdNextAutoNumber
.Connection = gbConn
.CommandText = sqlGetNextAutoNo
drDataReader = .ExecuteReader
If drDataReader.HasRows Then
While (drDataReader.Read())
mstrDocPrefix = IIf(Not IsDBNull(drDataReader.Item("DocPrefix").ToString), drDataReader.Item("DocPrefix").ToString, “01”)
intYearPrefix = IIf(Not IsDBNull(drDataReader.Item("YearPrefix").ToString), CInt(drDataReader.Item("YearPrefix").ToString), Year(Today))
mstrMonthPrefix = IIf(Not IsDBNull(drDataReader.Item("MonthPrefix").ToString), drDataReader.Item("MonthPrefix").ToString, Month(Today))
mstrNextNo = IIf(Not IsDBNull(drDataReader.Item("NextNo").ToString), drDataReader.Item("NextNo").ToString, "0001")
intmCounter = IIf(Not IsDBNull(drDataReader.Item("mCounter").ToString), CInt(drDataReader.Item("mCounter").ToString), 1)
End While
Else
mstrDocPrefix = "01"
intYearPrefix = Year(Today)
mstrMonthPrefix = Month(Today)
mstrNextNo = "0001"
intmCounter = 1
End If
End With
'
'intNewmCounter = intmCounter + 1
If Not (intYearPrefix = Year(Today)) Then
intYearPrefix = Year(Today)
intNewmCounter = 1
End If
'
mstrCurrentMonth = Month(Today)
mstrCurrentMonth = IIf(Len(mstrCurrentMonth) < 2, "0" & mstrCurrentMonth, mstrCurrentMonth)
If Not (mstrMonthPrefix.Trim = mstrCurrentMonth.Trim) Then
mstrMonthPrefix = mstrCurrentMonth
intNewmCounter = 1
End If
'
If (intNewmCounter = 1) Or (Len(mstrNextNo) < 4) Then
intmCounter = intNewmCounter
mstrNextNo = GenerateNextNumber(intNewmCounter)
End If
'
GetNextAutoNo = mstrDocPrefix.Trim & mstrMonthPrefix.Trim & intYearPrefix.ToString & mstrNextNo.Trim
intmCounter += 1
mstrNextNo = GenerateNextNumber(intmCounter)
'Transfer values to global variables
‘***the variables on the left are global variables declared outside this sub routine
gstrDocPrefix = mstrDocPrefix
gbstrMonthPrefix = mstrMonthPrefix
gbstrNextNo = mstrNextNo
gbintYearPrefix = intYearPrefix
gbintCounter = intmCounter
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Auto Number Error!")
Finally
CloseDBConnection()
End Try
Return GetNextAutoNo
End Function
Public Function GenerateNextNumber(ByVal NextCounter As Integer) As String
GenerateNextNumber = ""
Select Case NextCounter
Case 1 To 9
GenerateNextNumber = "000" & NextCounter
Case 10 To 99
GenerateNextNumber = "00" & NextCounter
Case 100 To 999
GenerateNextNumber = "0" & NextCounter
Case 1000 To 9999
GenerateNextNumber = NextCounter
End Select
Return GenerateNextNumber
End Function
In your form you can add this code:
Private Sub Button_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
Text1.Text=GetNextAutoNo
End Sub
I hope this will assist you.
Thanks
The best way to do this is to store your sequence number in the DB, and then reset it every time the other numbers increase. The overall number would be built in code, or in the DB, you could write a proc to do it and increment the number as needed.