Click here to Skip to main content
12,999,081 members (48,796 online)
Rate this:
Please Sign up or sign in to vote.
hello masters,

I want to ask the help of the master, I'd like an AutoNumber like this in the source code for the final lecture using or vb 6.0

text1. text = "0207110001"

02 = the introduction of a number of documents
07 = introduction of months
11 = the introduction of year
0001 = sorting number

so while still in the same month a sorting number will grow "1" and if the month increased "1" and the year also increased "1" then back to the beginning of sorting number = "0001"

Please help and enlightenment from the masters love my source code that has a connection to the database and there are checks to the database
Posted 11-Jul-11 16:52pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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
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
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!")
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
End Sub

I hope this will assist you.

ilham zamzami from jakarta 17-Jul-11 22:54pm
thank you for your help
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.
ilham zamzami from jakarta 11-Jul-11 23:00pm
can you give me example of code ??? I was ignorant to this problem
Christian Graus 11-Jul-11 23:07pm
For your convenience, I've deleted where you asked this twice. You store the 'sorting number' in a table, by itself. You get the date using the DATEPART method. You pass in the number of documents, or get it from the DB. You increment the sorting number every time and reset it to 0 or 1 when the month or year changes ( which I guess means you want to store the date and year you're using in the table with the sorting number ). This is pretty easy stuff, and really you should try to do your own work and ask us for help, instead of asking us to do your job for you, without even trying.
ilham zamzami from jakarta 11-Jul-11 23:51pm
I apologize if you think so, but sesungguhnay I've tried it myself but still have not seen the results, so I ask you please to you. if you could help me with a happy heart I thank you very much. if not, it was not a problem

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170622.1 | Last Updated 13 Jul 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100