-
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
-
Re: Auto Number
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:
-
Re: Auto Number
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.
-
Re: Auto Number
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.... !
-
Re: Auto Number
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.
-
Re: Auto Number
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.
-
Re: Auto Number
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 !
-
Re: Auto Number
hi,
thank u so much it's working