Auto Generate Key

Submitted by admin on Wednesday, September 9, 2009 - 13:37.

This tutorial will teach you on how to create a key automatically as a primary key in your table. It is sometimes needed to create your own key to format it on your own needs. Say for example you’d like to format it to start as four (4) digits rather than starting from one (1).

The code herein is based on the code that I have already submitted on some of my program like hotel reservation system. But these time a simplified one. I created this tutorial because there are some visitors in this website who asked this before.

In order to create an auto generated key for your table, all you need is a table that will hold the last key of the record that is auto generated. Below is the example of the table that we need using Microsoft Access.

[inline:Table.jpg=Table Schema]

Example of data within the “Key Generator” table.

[inline:Auto Generate Key Data.jpg=Auto Generate Key Data Sample]

As you can see at the above example every table has its own key for the next value.

The above record is incremented by one (1) once called.

The Code

In the form load event type the following code as shown below:

        Dim CustomerNo As Integer
 
        CustomerNo = GetIndex("Customers")
        txtCustomerNo.Text = CustomerNo

This will call a function called GetIndex and return the next value of the NextNo field.

The following is the code to return and increment a value for NextNo field:

    Public Function GetIndex(ByVal srcTable As String) As Long
        Dim cnCustomers As OleDbConnection
        'create connection
        cnCustomers = New OleDbConnection
 
        With cnCustomers
            If .State = ConnectionState.Open Then .Close()
 
            .ConnectionString = cnString
            .Open()
        End With
 
        Dim sqlQRY As String = "SELECT * FROM [KEY GENERATOR] WHERE TableName = '" & srcTable & "'"
 
        'create commands
        Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, cnCustomers)
 
        Try
            'create data reader
            Dim rdr As OleDbDataReader = cmd.ExecuteReader
            Dim intNextNo As Integer
 
            'loop through result set
            While (rdr.Read)
                intNextNo = rdr("NextNo")
            End While
 
            intNextNo = intNextNo + 1
 
            'define update statement
            'this will update the table "key generator" to a new value
            Dim sqlUpdate As String = "UPDATE [Key Generator] SET [Key Generator].NextNo = " & intNextNo & " WHERE TableName='" & srcTable & "'"
 
            Dim cmdUpdate As OleDbCommand = New OleDbCommand(sqlUpdate, cnCustomers)
 
            'execute nonquery to update an index
            cmdUpdate.ExecuteNonQuery()
 
            GetIndex = intNextNo
        Catch ex As OleDbException
            MsgBox(ex, MsgBoxStyle.Information)
        Finally
            ' Close connection
            cnCustomers.Close()
            Console.WriteLine("Connection closed.")
        End Try
    End Function

I hope this will help some of you who do not know yet how to generate a key automatically.

Comments

hi

Good day thanks am enjoy ur programs pls can u send me the cods of a simple calculator,and a table on banking system.and the cods.thanks have a nice day.

UPDATING AN ACCESS TABLE

Good morning. I really find your codes very helpfull. But please i have a problem. I need a code to automatically upload information form excel to access table. The code that i tried would do taht but replaces the data that already exists in the table, it doesnt update or add on to the already existing data. Thanks in advance OITSILE MAPITSE (BOTSWANA) [email protected]

Add new comment

Add new comment
106 views