In this article, we will learn to create an application in Visual Basic 6.0 for Employee Record Management in an organization.
The application does following tasks
- Insert, Delete, Update employee records.
- Compute the Gross Pay and Net Pay of each employee.
- Maintain records of departments for each employee.
- Each employee should be able to login to the system with own password.
The development of the application – Employees Record Management is done in following ways.
- Develop System Model.
- Define the Data Model (E-R Diagram)
- Design the User Interface in VB 6.
- Rename the components.
- Write codes for components.
- Test and verify Output.
System Model

E-R Diagram for Employee Record Management

Working with MS Access Database
Based on the E-R Model you can create a New Access Database and save it as the emp.mdb file. To create a blank database
Open Microsoft Office Access 2007 ( Check MS office documentation for other versions) > Click “Blank Database”.

Create three table for the emp.mdb database.
Dependent Table Schema

Employee Table Schema

Login Table in Design View

Note: Each Table should have at least one field as Primary Key.
Relationship between Tables

Accessing Access Database from Visual Basic 6
You can use a common module from visual basic to connect to the access database. Functions from this module will be called from all Forms in the visual basic project, so that we do not need to rewrite the same code for database connectivity.
Add the ADODB components in the VB Toolbox.

Add an Adodb component, [Right Click] on the Control Object > Click Properties.

Under the Adodc properties windows > Click Build.


Under the Connection tab > Select the Database you created previously.

Now Click “Test Connection” to check if the database connection is successful. Click on the “Provider” tab again and we now have our connection string.

This new Connection String will be used in Module.Bas file for accessing the database at run-time. The existing ADODC control is no longer required and you can delete it.
Creating Module1 for Database Connectivity
To create a new database connection at run-time, do the following. Under Project.vbp windows > Project1 [Right-Click] > Add [Select] > Module [Click].
A new module will open as shown in the following figure.

Type the following code in the Module Code Editor.
Public con As New adodb.Connection Public rs As New adodb.Recordset Public constr As String Public Sub loadcon() constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Peter\Documents\emp.mdb;Persist Security Info=False" con.Open constr MsgBox ("connected") End Sub

Labels
Name: lblEMPMANAGE Caption: EMPLOYEE RECORD MANAGEMENT
Buttons
Name: cmdLogin Caption: Login
Code for frmEMPMANAGE
Private Sub cmdLogin_Click() Unload Me frmLogin.Show End Sub
Components of frmLogin

Labels
Name: lblUserID Caption:UserID
Name: lblPassword Caption: Password
Textboxes
Name: txtUserID Text: 'leave blank'
Name: txtPassword Text; 'leave blank'
Buttons
Name; vbOK Caption: Login
Name: vbCancel Caption: Cancel
Code for frmLogin
Option Explicit Public LoginSucceeded As Boolean Private Sub cmdCancel_Click() 'set the global var to false 'to denote a failed login LoginSucceeded = False Me.Hide End Sub Private Sub cmdOK_Click() With rs .Open "SELECT * FROM LOGIN", con, adOpenDynamic, adLockOptimistic 'check the username .MoveFirst While Not .EOF If txtUserID = !UserID And txtPassword = !Password Then frmMenu.Show LoginSucceeded = True con.Close MsgBox ("Connection Closed") Exit Sub Unload Me ElseIf txtUserID <> !UserID Then .MoveNext Else MsgBox "Invalid Password, try again!", , "Login" txtPassword.SetFocus SendKeys "{Home}+{End}" Exit Sub End If Wend .Close End With End Sub Private Sub Form_Load() Call loadcon Unload frmEMPMANAGE End Sub
Components for frmEmpDetails

Labels
Name: lblEmpID Caption: EMPID
Name: lblFirstname Caption: FirstName
Name: lblLastname Caption: LastName
Name: lblHRA Caption: HRA
TextBoxes
Name: txtEmpID Text: 'leave blank'
Name: txtFirstname Text: 'leave blank'
Name: txtLastname Text: 'leave blank'
Name: txtHRA Text: 'leave blank'
Name: txtDA Text: 'leave blank'
Name: txtBasic Text: 'leave blank'
Buttons
Name: cmdAdd Caption: &ADD
Name: cmdDel Caption: &DELETE
Name: cmdUpdate Caption: &Update
Name: cmdClr Caption: &Clear
Name: cmdExit Caption: &Exit
Name: cmdFirstname Caption: &FirstName
Name: cmdLastname Capttion: &LastName
Name: cmdNext Caption: Next Record
Name: cmdPrev Caption: Previous Record
Code for frmEmpDetails
Private Sub cmdAdd_Click() con.Execute "INSERT INTO emp(EmpID,FirstName, LastName,Basic, HRA, DA) VALUES (" & txtEmpID & ",'" & txtFirstname & "','" & txtLastname & "'," & txtBasic & "," & txtHRA & "," & txtDA & " );" MsgBox ("Record Inserted") End Sub Private Sub cmdCLR_Click() txtEmpID = "" txtFirstname = "" txtLastname = "" txtBasic = "" txtHRA = "" txtDA = "" End Sub Private Sub cmdDel_Click() con.Execute "DELETE * FROM emp WHERE EmpID = " & txtEmpID & "" MsgBox ("Record Deleted") txtEmpID = "" End Sub Private Sub cmdExit_Click() Unload Me con.Close End Sub Private Sub cmdUpdate_Click() con.Execute "UPDATE emp SET FirstName = '" & txtFirstname & "', LastName = ' " & txtLastname & " ' , Basic = " & txtBasic & " , HRA = " & txtHRA & " , DA = " & txtDA & " WHERE EmpID = " & txtEmpID.Text & "" End Sub Private Sub Form_Load() Call loadcon End Sub
Component for frmSalary

Labels
Name: lblCalculateS Option: CALCULATE SALARY
Name: lblEmpID Caption: ENTER EMPLOYEE ID
Name: lblGrossP Caption: GROSS PAY
Name: lblNetP Caption: NET PAY
Textboxes
Name: txtEmpID Text: 'leave blank'
Name: txtGross Text: 'leave blank'
Name: txtNet Text: 'leave blank'
Buttons
Name : cmdGross Caption: GROSS PAY
Name: cmdNet Caption: NET PAY
Name: cmdExit Caption: EXIT
Code for frmSalary
Private Sub cmdExit_Click() Unload Me con.Close End Sub Private Sub cmdGross_Click() Dim gross As Double Dim net As Double Dim tax As Double With rs rs.Open "SELECT * FROM emp WHERE EmpID = " & txtEmpID & "", con, adOpenDynamic, adLockPessimistic gross = !Basic + !HRA + !DA End With txtGross.Text = gross con.Execute "UPDATE emp SET GrossPay = ' " & gross & " ' WHERE EmpID = " & txtEmpID & "" rs.Close End Sub Private Sub cmdNet_Click() Dim gross As Double Dim net As Double Dim tax As Double With rs rs.Open "SELECT * FROM emp WHERE EmpID = " & txtEmpID & "", con, adOpenDynamic, adLockPessimistic gross = !Basic + !HRA + !DA tax = gross * 10 / 100 net = gross - tax End With rs.Close txtNet.Text = net con.Execute "UPDATE emp SET NetPay = ' " & net & " ' WHERE EmpID = " & txtEmpID & "" End Sub Private Sub Form_Load() Call loadcon End Sub
Components for frmDependent

Labels
Name: lblDID Caption: Department ID
Name: lblDname Caption: Department Name
Name: lblRelation Caption: Relation
Name: lblDependent Caption: Employee Dependent
Textboxes
Name: txtDID Text: 'leave blank'
Name: txtDname Text: 'leave blank'
Name: txtRel Text: 'leave blank'
Name: txtEmpID Text: 'leave blank'
Buttons
Name: cmdAdd Caption: &ADD
Name: cmdDel Caption: &DELETE
Name: cmdUpdate Caption: &UPDATE
Name: cmdClr Caption: &CLEAR
Caption: &EXIT Name: cmdExit
Code for frmDependent
Private Sub cmdAdd_Click() con.Execute "INSERT INTO Dependent(DependentID, DependentName,Relation,EmpID ) VALUES (" & txtDID & ", '" & txtDName & "','" & txtRel & "'," & txtEmpID & ");" txtDID.Text = "" txtDName.Text = "" txtRel.Text = "" txtEmpID = "" con.Execute "DELETE * FROM Dependent WHERE DependentID = " & txtDID & "" End Sub Private Sub cmdClr_Click() txtDID.Text = "" txtDName.Text = "" txtRel.Text = "" txtEmpID = "" End Sub Private Sub cmdExit_Click() Unload Me End Sub Private Sub cmdUpdate_Click() con.Execute "UPDATE Dependent SET DependentName = ' " & txtDName & " ' , Relation = ' " & txtRel & " ' WHERE DependentID = " & txtDID & " " MsgBox ("Record Updated") End Sub Private Sub Form_Load() Call loadcon End Sub
frmMenu [MDI Form]
MDI Form are Multiple-Document Interface in which you can open multiple forms. All our previously created will be child form of MDI.
This form is called frmMENU.
This form has menu editor to add different menu items and each menu item is linked to one of the forms created earlier. Before we get started with MDI form, you must set the MDI Child property of following Forms to ‘True’.
- frmEmpDetails
- frmSalary
- frmDependent
Create a New MDI Form
To create a new MDI From, go to Project1.vbp > [Right Click] and then [Select ] Add > [Click] MDI Form.
Right Click the form and you will see the Menu Editor, using the editor create following menu items.
Caption : Employee Details Name : employee Index : 0
Caption : Salary Calculation Name : salary Index : 1
Caption : Dependents Name : dependents Index : 2
Caption : Exit Name : exit Index : 3
Code for frmMENU
The code for frmMENU is simple, you click on one of the menu item and a new form opens up, however the new form does not open in a separate windows, but it will open within frmMENU as MDI Child form.
Private Sub depend_Click(Index As Integer) frmDependent.Show End Sub Private Sub empdetails_Click(Index As Integer) frmEmpDetails.Show End Sub Private Sub exit_Click(Index As Integer) Unload Me Unload frmLogin End Sub Private Sub salary_Click(Index As Integer) frmSalary.Show End Sub