help in auto increment id

[Cally_Law]

Hi,I'm currently using vb8. i need help in coding.
How can I increment a number in a textbox everytime when the form is loaded?
I want that first time when the form gets loaded Company_id should appear in the textbox as NEW010101.
Second time it should appear as NEW010102.
Third time NEW010103
Please help.

i use this coding which also copy a post from daniweb.com and edit

Private Function GetNextID(ByVal sID As String) As String
        Dim i As Integer
        Dim sTmp As String

        sTmp = Mid(sID, 6)
        i = Val(sTmp) + 1
        sTmp = "NEW" & Format(i, "01010#")
        GetNextID = sTmp

    End Function

if i textbox1.text = GetNextID("NEW01010")
the first time i add can work ,become NEW010101, but second cant work.
how what the full coding can i read the max company_id in database and increment the id?

Please Help.

[debasisdas]

You need to use database for this.

new value = max of current value + 1

[Cally_Law]

You need to use database for this.

new value = max of current value + 1

sorry i not understand because i'm newbie to use vb , but final project needed, so have to learn.you mean put new value at which part ? and the problem at where?

[debasisdas]

store the value that is being generated in a database and read back the same and add one to generate the new value.

[Cally_Law]

store the value that is being generated in a database and read back the same and add one to generate the new value.

Can show me exactly code? how can i read the last company_ID ?

[adam_k]

What are you doing with the Company_ID or with any other data in your form ? Are you saving them in a db?
If you do query the database :

select max(right(Company_ID,len(Company_ID)-3)) from table

and you'll get the last number used. Add 1 to this and you've got your new number.

PS: This will only work if NEW doesn't change in the Company_ID.
PS2: Why do you need NEW in Company_ID?

[Cally_Law]

What are you doing with the Company_ID or with any other data in your form ? Are you saving them in a db?
If you do query the database :

select max(right(Company_ID,len(Company_ID)-3)) from table

and you'll get the last number used. Add 1 to this and you've got your new number.

PS: This will only work if NEW doesn't change in the Company_ID.
PS2: Why do you need NEW in Company_ID?

yea..i'm saving in database.
The select max is put on where?

reply PS : yea, the NEW doesn't change , the number change only.
reply PS2: Just let me easy to know what ID is it. because the ID combined with char and digit , so i do not know how to +1 in the company_ID

my project is i need to create new company record, then i need company_ID.then i cant auto generate new ID

[debasisdas]

You can maintain two fields
1. Id-------number
2. company_id-----string

suppose i have 1 record in the database
so
Id = 1
company_id = NEW1

next time you want to generate a new company id

read the max of id
i.e.--1
increment the same by 1
so
new Id = 2
new company_id = NEW2

don't forget to make the ID field unique and the Company_id field primary key of the table.

i think that is clear enough.

[adam_k]

The select statement needs to run on the db. Open a connection to the db, run the select and the add 1 to the value that the query will return. Since it's only going to be 1 value you can use ExecuteScalar to get the value in a variable.

[Cally_Law]

The select statement needs to run on the db. Open a connection to the db, run the select and the add 1 to the value that the query will return. Since it's only going to be 1 value you can use ExecuteScalar to get the value in a variable.

how to run the select ? can show me example coding ?

[Netcode]

adam_K already gave you that in one of his response above

[ryan311]

here it is cally

put this code to your load_form

rs.open "Select CODE from db_table ORDER BY CODE DESC", cn, 3, 3

textbox1.text = rs!code

GetnewID()

End Sub

Sub GetnewID()

if textbox1.text = "" then

textbox1.text = "NEW01010"

else

textbox1.text = "NEW" & Format(Right(textbox1.text, 5) + 1)

end if

End sub

the next ID will generate like this

NEW01011

[adam_k]

You are connected to a database right? Why do you need code for this? How are you saving / quering for records?
Anyway:

Dim con As New SqlClient.SqlConnection
  Dim cmd As New SqlClient.SqlCommand 
  Dim NewID as Integer

        con.ConnectionString = "Your connection string here " 'Change to the correct string

        cmd.CommandText = "select max(right(Company_ID,len(Company_ID)-3)) from table" 'Change table name to the correct one 

        cmd.Connection = con
        con.Open()

NewID = cmd.ExecuteScalar 'This gets the maximum number into NewID 
NewID += 1 ' This increments it by one

[Cally_Law]

here it is cally

put this code to your load_form

rs.open "Select CODE from db_table ORDER BY CODE DESC", cn, 3, 3

textbox1.text = rs!code

GetnewID()

End Sub

Sub GetnewID()

if textbox1.text = "" then

textbox1.text = "NEW01010"

else

textbox1.text = "NEW" & Format(Right(textbox1.text, 5) + 1)

end if

End sub

the next ID will generate like this

NEW01011

the Sub GetNewID need private ?
and what mean of "Select CODE from db_table ORDER BY CODE DESC", cn, 3, 3"?
now my table name is Company_Detail , and my column is Company_ID and my dataset is Detaildataset
so CODE izit my company_ID? and db_table is company_detail? and what about CODE DESC? and last is 3,3?

[Cally_Law]

You are connected to a database right? Why do you need code for this? How are you saving / quering for records?
Anyway:

Dim con As New SqlClient.SqlConnection
  Dim cmd As New SqlClient.SqlCommand 
  Dim NewID as Integer

        con.ConnectionString = "Your connection string here " 'Change to the correct string

        cmd.CommandText = "select max(right(Company_ID,len(Company_ID)-3)) from table" 'Change table name to the correct one 

        cmd.Connection = con
        con.Open()

NewID = cmd.ExecuteScalar 'This gets the maximum number into NewID 
NewID += 1 ' This increments it by one

yea ..i'm connected database,I need for create new ID but don't wan create one by one manually.but i'm using microsoft access to do database not sql. this coding izit can work if i using microsoft access?

[adam_k]

yea ..i'm connected database,I need for create new ID but don't wan create one by one manually.but i'm using microsoft access to do database not sql. this coding izit can work if i using microsoft access?

No, my sample code will work for SQL server, but I believe that the query will work in access as well.

[Cally_Law]

No, my sample code will work for SQL server, but I believe that the query will work in access as well.

actually which part is query ?

[Netcode]

actually which part is query ?

Are you asking what a query is?

[Cally_Law]

Are you asking what a query is?

i not understand what is query in the coding.

[Cally_Law]

here it is cally

put this code to your load_form

rs.open "Select CODE from db_table ORDER BY CODE DESC", cn, 3, 3

textbox1.text = rs!code

GetnewID()

End Sub

Sub GetnewID()

if textbox1.text = "" then

textbox1.text = "NEW01010"

else

textbox1.text = "NEW" & Format(Right(textbox1.text, 5) + 1)

end if

End sub

the next ID will generate like this

NEW01011

what is rs.open ?

[debasisdas]

Are you asking what a query is?

He is asking for spoon feeding.