How to create a Autogenerated ID Number

Hello guys,this is sample code on how you to generate a Auto-Generated ID Number or a
Primary Key, because many programmers like me are want the ID Number or Primary Key
become Dynamic, so we can do the basic solution like this:

In my case i put it on a Sub Procedure that i placed it on a Module,so the this is the Code:

Sub EmployeePosition_ID()

Set rs=New Adodb.Recordset

    If rs.State = adStateOpen Then rs.Close

        rs.Open "SELECT MAX (right(Position_ID,6)) _

            from EmployeePositions", Conn

        If rs.EOF <> True Then

           Employee_Positions.txtID.Text = "SPC"  + "-"
         + Right("000000" _

            & IIf(IsNull(rs.Fields(0)), 1, rs.Fields(0) + 1), 6)

        End If

End Sub

How does it Works??

If rs.State = adStateOpen Then rs.Close, this will check your connection if it is open,then
it will close your connection, rs.Open "SELECT MAX (right(Position_ID,6)) from
EmployeePositions", Conn,this will read the database ID Number or Primary Key,stored
on your database column (Position_ID,6) this is my database field,which length is 6,you

can customize it how long you want ;

If rs.EOF <> True Then

      Employee_Positions.txtID.Text = "SPC"  + "-" + Right
       ("000000" & IIf(IsNull(rs.Fields(0)), 1, rs.Fields(0) + 1), 6)

  End If

this will check the list of data that stored on my Position_ID column, checking the last data
or the End of File(EOF),then it will display it my txtID control, so if the field are empty or
null,the ID Number or a Primary Key will increment and show like this SPC-000001;

Hope this will help!

The Hardest Thing to Do is to Do Nothing ^_^

These icons link to social bookmarking sites where readers can share and discover new web pages.

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

Tags: Database, VB6 Functions, Visual Basic 6.0