In our previous tutorial, we have discussed the step by step procedure on how to connect visual basic to ms access. This time we’re going to learn how to insert a record in our database.
We will provide you the step by step procedure on how to do that and source code will be provided for your reference.
Since this is the continuation of the previous project, kindly download the source code of the previous tutorial. (How to connect Visual Basic to MS Access).
After you have downloaded the source code, kindly follow the instructions below:
1. Open the database (Data.mdb) and create a new table (in the menu, select Create and click the Table icon).
2. Save the table as tblStudent and enter the following field:
Field Name Data Type
ID – Autonumber (this will auto increment by 1)
studentName – Text
studentAge – Number
studentContact – Number
studentAddress – Text
3. Open Project1.vbp and add a new form.
4. To add a new form, click Project in the menu bar and click Add Form select Form and click Open. Another way of adding new form is to right click the project explorer and select Add and click Form.
5. Rename the form into InsertRecordFrm.
6. Open the form and add 4 Textbox and 4 Labels and 1 Command Button.
7. Rename the Textbox into: txtStudentName, txtStudentAge, txtStudentContact, txtStudentAddress
8. Rename the Command Button into cmdSave and change the caption into Save.
9. Insert the code below in the form. (right click the InsertRecordFrm and click View Code and paste the code below.)
Sub InsertRecord()
If rs.State = adStateOpen Then rs.Close
sql = "Select * from tblStudents Where studentName='" & txtStudentName.Text & "'"
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate Name Found.Please enter another name", vbInformation, ""
Exit Sub
End If
With rs
.AddNew
!studentName = txtStudentName.Text
!studentAge = txtStudentAge.Text
!studentContact = txtStudentContact.Text
!studentAddress = txtStudentAddress.Text
.Update
End With
MsgBox "New Record Successfully added", vbInformation, ""
End Sub
Note 1: The code above will add a new record to our tblStudent.
sql = "Select * from tblStudents Where studentName='" & txtStudentName.Text & "'"
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox "Duplicate Name Found.Please enter another name", vbInformation, ""
Exit Sub
End If
The line of codes states that the database will not accept the record if the name of the student is already in the database and will prompt a message “Duplicate Name Found. Please enter another name” and ignore the rest of the code.
10. The InsertRecord is a procedure or a sub routine in visual basic, this function will not be executed unless this function will be call. We’re going to place the function in our Command Button click event
The code will look like this:
Private Sub cmdSave_Click()
InsertRecord
End Sub
11. We are now ready to test the application. Kindly press F5.
12. Fill up all the textbox. The message will appear “New Record Successfully added” indicating that we have added new record in our database.
13. Open the tblStudent and notice that the data encoded in the form has shown in the table.
Note 2: to prevent errors, kindly close the table(s) in your ms access database.
Hi, Where I will add this code:
Please add some screenshots thanks
sql = “Select * from tblStudents Where studentName='” & txtStudentName.Text & “‘”
rs.Open sql, conn
If rs.RecordCount >= 1 Then
MsgBox “Duplicate Name Found.Please enter another name”, vbInformation, “”
Exit Sub
End If
Good day
you don’t need to paste it anymore because it’s already in the InsertRecord procedure.
that line of codes prevent duplication of records in the database
happy coding