Auto Generate Key

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

Submitted byAnonymous (not verified)on Wed, 11/04/2009 - 14:16

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.
Submitted byAnonymous (not verified)on Mon, 07/12/2010 - 17:22

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