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.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Furl
  • Reddit
  • Spurl
  • StumbleUpon
  • Technorati

5 Responses to this post

  1. rmrk_miles2590 on February 20, 2011 at 4:54 AM

    Hello, can you explain more to me on how does it works? because some of the codes there is hard to understand for me, like,
    -what is the use of "right"?
    ".Open "SELECT MAX (right(Position_ID,6)) _
    from EmployeePositions", Conn"

    -is there any format for this ".EOF <> True"?
    - and the very difficult to understand for me is this, "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"

    ---hehehe, tnx in advance, I hope you help me...

    and P.S.
    I agree with this, "The Hardest Thing to Do is to Do Nothing ^_^ "...
    God Bless...

  2. Adrian A. Panoy on February 20, 2011 at 3:40 PM

    hey dude thanks for dropping by,

    the use of "right" there is where to start the incrementation to become the ID number become auto-generated,"position_ID,6" is my database field name or my Id_Number,"6" there is the length of characters or the value like "000001" and for the next record is "000002","employeepeositions" is my table name,
    hope this will help on you

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

  3. rmrk_miles2590 on March 3, 2011 at 6:32 AM

    thanks for the reply... ^_^

    I see, hmmmn.. how about this line of code, I don't understand this, or how does this work?...

    "IIf(IsNull(rs.Fields(0)), 1, rs.Fields(0) + 1)"

    -thanks for the answer dude!

    you are right, absolutely,
    "The Hardest Thing to Do is to Do Nothing ^_^ "

  4. Adrian A. Panoy on March 3, 2011 at 6:47 AM

    thanks for continue reading and visiting my blog;

    "IIf(IsNull(rs.Fields(0)), 1, rs.Fields(0) + 1)"

    IIf(IsNull(rs.Fields(0)) this is where the field column is null or empty,then it will increment

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

  5. Anonymous on June 11, 2011 at 2:09 AM

    Employee_Positions
    hi can i ask, what is this code...

Leave a comment

Copyright @ Codes-47.Blogspot.Com. Powered by Blogger.