Auto Number

Hi,
Can anyone tell me how to generate auto number.i'm doing a project in vb6.i have a field called "Product Id".the product id should be like this "PI001".i should not enter every time,it should be generated automatically whenever i add a record.plz help me with the full coding.thank u

hi dp_suganya

welcome to codeguru !

I have posted the following code for you which have a dynamic function

Code:

---


Private Function GenerateSerial(pStrsql As String, pPrefix As String) As String
On Error GoTo errh
 
        Dim lCounter As String
        Dim lconn As ADODB.Connection
        Dim lrs As ADODB.Recordset
        Set lconn = OpenConection(lconn)
 
        lconn.BeginTrans
                Set lrs = lconn.Execute(pStrsql)
        lconn.CommitTrans
 
        If lrs.EOF Or lrs.BOF Then
                lCounter = pPrefix & "001"
                GenerateSerial = lCounter
        Else
 
                lrs.MoveLast
                lCounter = IIf(IsNull(lrs.Fields(0).Value), 1, lrs.Fields(0).Value)
                lCounter = lCounter + 1
                GenerateSerial = pPrefix & Format(lCounter, "000")
        End If
 
        Exit Function
errh:
        MsgBox Err.Description, vbCritical, "Message"
        Exit Function
        Resume
End Function

---

Now, just call it , like


GenerateSerial ("Select YourAutoNumberFieldName from YourTablename", "PI")

where chnage the YourAutoNumberFieldName to with auto number field and YourTablename to with your table name ! You can also specify dynamic prefix like "PI" or "PS"...

In the avobe code openconection is a another function to set the connection object for adodb.connection.

I hope that it will be helpful to you !

tell me if you have any other queries..

Regards !:wave:

Hi Rahul,

IMHO your code is not very safe:
- It assumes that you always have a ascending sort by the field of the primary key (-> Auto Number) as a part of the query.
- It is completely unusable for a multi-user environment.
- It ends at Prefix & 999.

Hi Patzer

Hurry, hurry and hurry... why dear ... why u r in so hurry to post ur msg ?

look here your answers are ,

- It is requirement in my project , that i have to increment in asc order.... ! iF you want it in another order , you can just change it according to your need.
- My project already works in multi-user environment, using msaccess record locking and temp table locking !!! )
- You can use Format function to increase your "0000...." formatting.

like Format(nNumber, "00000") for 5 digits or Format(nNumber, "0000000") for 7 digits and so more. Format is just converted to in a specific format it is not used data type..

Data type is depend on your auto number field's data type..

so dear , do not show hurry , while replying to post... !
Remember, in a forum, you can also have a some concept which can be useful to make complete your requirements.. it is not required that you will always recieved full solutions from the users... so if dp_suganya wants to change the code according to his/her requirements , then it can be possible..


so enjoy.... !

HI,
thanks for the reply.i'm new to vb.so can u tell me any other simple method for this.i don't know how to solve this.

Just a thought:

Your database may allow you to set an auto-indexing field. I know Access will allow this. Whenever you add a record to a table, it uses the next number. I know this isn't exactly what you asked for, but I mention it because it is so simple to do.

Just an idea.

Here are the Openconection coding

Code:

---


Public Function OpenConection(lconn As ADODB.Connection) As ADODB.Connection
        On Error GoTo erh
       
                Set lconn = New ADODB.Connection
                lconn.provider = <<your provider >>
                lconn.ConnectionString = <<your datapath>>
                lconn.Mode = adModeReadWrite
                lconn.CursorLocation = adUseClient
                lconn.Open
                Set OpenConection = lconn
               
                Exit Function
 
End function

---

Now when you want to generate a new Autonumber then simply just call the function like this :

Code:

---


Dim newSerialNo as String
newSerialNo = GenerateSerial("Select Product_Id from MyTable","P")
txtSerialNo.text = newSerialNo

---

:thumb: WOWW! !

You code is ready now.... just serve it to your application..

Regards !

hi,
thank u so much it's working