Employees Record Management using VB 6.0 and MS Access Database

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,

  1. Insert, Delete, Update employee records.
  2. Compute the Gross Pay and Net Pay of each employee.
  3. Maintain records of departments for each employee.
  4. 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

System Model - Employee Record Management
System Model – Employee Record Management

E-R Diagram for Employee Record Management

E-R Diagram - Employee Record Management
E-R Diagram – 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”.

Click Blank Database
Click Blank Database

Create three table for the emp.mdb database.

Dependent Table Schema

Dependent Table in Design View
Dependent Table in Design View

Employee Table Schema

Employee Table in Design View
Employee Table in Design View

Login Table in Design View

Login Table in Design View
Login Table in Design View

 

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

Relationship between Tables

Relationship Between Tables
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 ADOBD component
Add ADOBD component

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

Advertisements


ADODB Component
ADODB Component

Under the Adodc properties windows > Click Build.

ADODC Properties
ADODC Properties

 

Select the Provider
Select the Provider

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

Select the Database
Select the Database

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.

Connection String
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.

Create a New Module
Create a New Module

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

Modules used in Employee Record Management

Name: frmEMPMANAGE

Caption: EMPLOYEE DETAILS MANAGEMENT

 

Name: frmLogin

Caption: LOGIN

 

Name: frmMenu [MDI Form]

Caption: MENU

 

Name: frmSalary

Caption: CALCULATE SALARY

 

Name: EMPLOYEE DETAILS

Caption: EMPLOYEE DETAILS

 

Name: frmDependent

Caption:  DEPENDENT

 

Name: Connection [Module1.Bas]

 

Components for frmEMPMANAGE

FORM EMPLOYEE DETAILS MANAGEMENT
FORM EMPLOYEE DETAILS MANAGEMENT

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

Form Login
Form Login

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

Form Employee Details
Form Employee Details

Labels

Name: lblEmpID

Caption: EMPID

 

Name: lblFirstname

Caption: FirstName

 

Name: lblLastname

Caption: LastName

 

Name: lblHRA

Caption: HRA

 

Name: lblDA

Caption: DA

 

Name: lblBasic

Caption: Basic

 

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

Form Salary - Employee Record Management
Form Salary – Employee Record Management

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

Form Dependent - Employee Record Management System
Form Dependent – Employee Record Management System

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

 

Name: cmdExit

Caption: &EXIT

 

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 have 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
Advertisements