How to create a customized incremental record number in Visual Basic and MS access
Creating a id number is really not a problem in ms access because ms access has a Data type AutoNumber which will automatically assign a number for your record, but you can also create a custom record id or number that will fit your purpose, and this tutorial will teach us on how to do that.
Let us first create and design our database
1. We must first create a MS Access database and save it as Data.mdb. In this tutorial we need ms access database (2000-2003 format) (.mdb)
2. Create a new table and save it as tblCustomID.
3. Save the table and insert the following field:
Field name – Data Type
ID – Text
Note: set the ID field as Primary Key. Primary Key attribute will prohibit duplicate values on the field.
Creating the Visual Basic Project
We will skip the procedures on how to connect visual basic 6 to ms access, but if you have forgot that, kindly visit the tutorial on how to connect vb6 to ms access.
1. Open your visual basic 6 program and create a new project, select Standard EXE.
2. Let’s add a form to our project and save it as CustomIDFrm.
3. We need the following objects or controls:
2 TextBox and 1 Command Button
- Name the first textbox into txtNextID. This textbox will display the next ID that will be stored in the database.
- Name the second textbox into txtCurrentID. This will display the latest ID stored in the database.
- Name the command button as cmdNextID with a caption “Save Next ID” without quotes. The command button will trigger the system to save the next ID in the database.
4. After you have added the objects needed in our project, the form will be like:
5. We will not create a function that will return a string value. This function will get the current or latest value of ID that is stored in the database, if the result is null then the format of the value will depend on your formatting style, in this case we have set the value if null into five zeros (00000).
Right click CustomIDFrm, select View Code and paste the code below.
Function GetCurrentID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT Max(tblCustomID.ID) AS MaxOfID" & _
" FROM tblCustomID"
rs.Open sql, conn
If rs.EOF Or rs.BOF = True Then
GetCurrentID = Format(0, "00000")
Else
GetCurrentID = rs(0).Value
End If
End Function
6. The code will not execute unless we call it. Since the function returns a string value, we will pass that value to txtCurrentID.
Double click CustomIDFrm and paste the code below.
txtCurrentID.Text = GetCurrentID
7. Next is to display the next ID value to be stored in the database.
Function DisplayNextID() As String
If rs.State = adStateOpen Then rs.Close
sql = " SELECT Max(tblCustomID.ID) AS MaxOfID" & _
" FROM tblCustomID"
rs.Open sql, conn
If IsNull(rs(0).Value) Then
DisplayNextID = Format(1, "00000")
Else
DisplayNextID = Format(rs(0).Value + 1, "00000")
End If
End Function
The code above will retrieve the ID which has the maximum value and that value will be added by 1, it will be next value to be stored in the database.
8. The value will be passing then to txtNextID.
Double click CustomIDFrm and paste the code below.
txtNextID.Text = DisplayNextID
9. The next step is to create a procedure that will store the next ID to the database.
Double click cmdNextID button and paste the code below.
If rs.State = adStateOpen Then rs.Close
sql = "Select * From tblCustomID where ID='" & txtNextID.Text & "' "
rs.Open sql, conn
With rs
.AddNew
!ID = txtNextID.Text
.Update
End With
MsgBox "ID stored", vbInformation, ""
Unload Me
CustomIDFrm.Show
10. Save the Project and press F5 to test the project.
More tutorials in Visual Basic and in different programming languages to come.
