Auto Number

[dp_suganya]

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

[rahul.kul]

hi dp_suganya

welcome to codeguru !

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

 
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 !

[Patzer]

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.

[rahul.kul]

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.... !

[dp_suganya]

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.

[Mishlai]

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.

[rahul.kul]

Here are the Openconection coding

 
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 :

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

WOWW! !

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

Regards !

[dp_suganya]

hi,
thank u so much it's working