Student Records Management System in VB 6.0 with Access Database

In this post, we will create an application for Student Record Management System in Visual Basic 6 with Microsoft Access Database.The list of Modules and Forms used in the program as follows.

(A) frmStudentManagement System

Caption : STUDENT RECORD MANAGEMENT SYSTEM

(B) frmLogin

Caption : LOGIN

(C) frmCourse

Caption : COURSE DETAILS

(D) frmStudentDetails

Caption : STUDENT DETAILS

(E) Module1.Bas

 System Design

First we will design the System Model and simple high level diagram of each module will be enough. There are many kinds of diagram to model a system especially when you are using Object-Oriented design.These models are specified in Unified Modelling Language (UML) which not scope of this post.

Student must Log In to access the System
Student must Log In to access the System

The Administrator have to log in to the system with user name and password provided during registration process. No one is allowed to log in without a proper User ID and Password.

Administrators must provide valid Username and Password
Administrators must provide valid Username and Password

Once the Administrators logged in they can see form for Student Details where they can Insert, Delete, Update and Clear the Student Records.They also have option to go to First Record, Last Record, Next Record and Previous Record.

Maintain Student Record using Student Details Form
Maintain Student Record using Student Details Form

Next there is an option to maintain the Course Details , when you click a Specific buttons. We will discuss that later.

Advertisements


Course Details
Course Details

Data Model

ER Model

ER Diagram for Student Management
ER Diagram for Student Management

Tables

First steps after creating the data model is to make sure that you create a MS Access Database called StudentDB.mdb. If you have MS Access 2007 or higher version, save your database with file extension (.mdb).

Create following table with correct data types as shown in the following figures.

Course Table in Design View
Course Table in Design View
Login Table in Design View
Login Table in Design View

Components of frmStudentManagent System

Form Student Management System
Form Student Management System
Labels

Name :lblStudentManagement

Caption : Student Management System

Font: MS Sans Serif

Font-Size: 24 PX

Font-Weight : Bold

Buttons

Name : btnLogin

Caption: Log in

Font: MS-Sans-Serif

Font-weight:Bold

Font-Size: 10px

Code for frmStudentManagement System

Private Sub Login_Click()

    frmLogin.Show

End Sub

Components of frmLogin

 

Login Form - Student Management
Login Form – Student Management
Labels

Name: lblUserID

Caption : User Name:

Name : lblPassword

Caption: Password

Text-Boxes

Name : txtUserID

Text : ‘leave blank’

Name : txtPassword

Text : ‘leave blank’

Buttons

Name : cmdOK

Caption : Log in

Name : cmdCancel

Caption : Cancel

Code for frmLogin

Option Explicit
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim constr As String

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()

    Dim userfound As Boolean

    With rs .Open "SELECT * FROM LOGIN",
    con, adOpenDynamic, adLockOptimistic

    'check the username

    .MoveFirst While Not .EOF
    If txtUserName = !RollNo And
        txtPassword = !Password Then

        frmSTUDREC.Show
        LoginSucceeded = True
        Exit Sub
        Me.Hide

    ElseIf

        txtUserName <> !RollNo 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
    MsgBox ("connected")
End Sub

Components of frmCourse

Form Course Details - Student Management
Form Course Details – Student Management

Labels

Name : lblCID

Caption: Course ID

Name : lblCname

Caption : Course Namev

Name : lblDuration

Caption : Duration(Semester):

Text-Boxes

Name : txtCID

Text : ‘leave blank’

Name : txtCname

Text : ‘leave blank’

Name : txtDurationv

Text : ‘leave blank’

Buttons

Name : cmdAdd

Caption : &ADD NEW COURSE

Name : cmdDel

Caption : &DELETE  COURSE

Name : cmdDisplay

Caption : &DISPLAY ENROLLED STUDENTS

Name : cmdExit

Caption : &EXIT

Name : cmdFirst

Caption : First Record

Name : cmdLast

Caption : :Last Record

Name : cmdNext

Caption : Next Record

Name ; cmdPrev

Caption : Previous Record

Code for frmCourse

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim constr As String

Private Sub cmdAddCourse_Click()

    con.Execute "INSERT INTO
    Course(CourseID, CourseName, Duration)
    VALUES ('"
+ txtCID.Text + "','" +
    txtCNAME.Text + "','" + txtDURATION.Text + "')"
    txtCID.Text = "" txtCNAME.Text = "" txtDURATION.Text = ""

    con.Close

End Sub

Private Sub cmdDelCourse_Click()

    con.Execute "DELETE * FROM COURSE
    WHERE CourseID = "
&
    txtCID & ""

End Sub

Private Sub cmdDisplay_Click()

    Dim rsGrid As New ADODB.Recordset
    con.CursorLocation = adUseClient rsGrid.CursorLocation
    = adUseClient rsGrid.CursorType = adOpenStatic

    rsGrid.Open "SELECT * FROM Student_Records
    WHERE CourseID = "
&
    txtCID & "",
    con, adOpenForwardOnly, adLockPessimistic

    Set DataGrid1.DataSource = rsGrid

End Sub

Private Sub cmdFirst_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF
    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration

    .MoveFirst
    Wend
    End With

End Sub

Private Sub cmdLast_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF
    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration
    .MoveLast

    Wend
    End With

End Sub

Private Sub cmdNext_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF

    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration

    .MoveNext
    Wend
    End With
End Sub

Private Sub cmdPrev_Click()

    With rs .Open "SELECT * FROM Course",  
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF
        txtCID.Text = rs!CourseID
        txtCNAME.Text = rs!CourseName
        txtDURATION.Text = rs!Duration

        .MovePrevious
        Wend
        End With

End Sub

Private Sub Form_Load()

    Call loadcon
    MsgBox ("Connected")

End Sub

 

Components of frmStudentDetails

Form Student Record Details - Student Management
Form Student Record Details – Student Management
Labels

Name : lblStudentID

Caption : STUDENT ID

Name : lblFirstName

Caption : FIRST NAME:

Name : lblLastName

Caption : LAST NAME:

Name : lblCourseID

Caption : COURSE ID

Name : lblGrade

Caption : GRADE

Text-Boxes

Name : txtStudentID

Text : ‘leave blank’

Name : txtFirstName

Text : ‘leave blank’

Name : txtLastName

Text : ‘leave blank’

Name : txtCourseID

Text : ‘leave blank’

Name : txtGrade

Text : ‘leave blank’

Buttons

Name : cmdAdd

Caption : &ADD

Name : cmdDel

Caption: &DELETE

Name : cmdUpdatev

Caption : &UPDATE

Name : cmdClear

Caption : &CLEAR

Name : cmdExit

Caption : &EXIT

Name : cmdDisplay

Caption : &DISPLAY

Name : cmdStudentCourses

Name: cmdNext

Caption : &Next Record

Name : cmdPrev

Caption : &Previous Record

Name : cmdFirst

Caption : &First Record

Name : cmdLast

Caption : &Last Record

Data Grid Control

Name : DataGrid1

Code for frmStudentDetails

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim constr As String

Private Sub btnCourse_Click()
    Unload Me
    frmCourse.Show
End Sub

Private Sub cmdData_Click()
    Dim rsGrid As New ADODB.Recordset
    con.CursorLocation = adUseClient
    rsGrid.CursorLocation = adUseClient
    rsGrid.CursorType = adOpenStatic
    rsGrid.Open "SELECT * FROM Student_Records", con, adOpenForwardOnly, adLockPessimistic
    Set DataGrid1.DataSource = rsGrid
End Sub

Private Sub cmdDel_Click()

    con.Execute "DELETE * FROM
    STUDENT_RECORDS WHERE RollNo = "
&
    (Text1.Text) & ""
    MsgBox ("Record Deleted Successfully")


End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdFirst_Click()

    With rs
        .Open "SELECT * FROM STUDENT_RECORDS", con, adOpenDynamic, adLockOptimistic

        Text1.Text = rs!RollNo
        Text2.Text = rs!FirstName
        Text3.Text = rs!LastName
        Text4.Text = rs!CourseID
        Text5.Text = rs!Grade
        .MoveFirst
        .Close

        End With

End Sub

Private Sub cmdLast_Click()
    With rs
        .Open "SELECT * FROM STUDENT_RECORDS", con, adOpenDynamic, adLockOptimistic
        Text1.Text = rs!RollNo
        Text2.Text = rs!FirstName
        Text3.Text = rs!LastName
        Text4.Text = rs!CourseID
        Text5.Text = rs!Grade

        .MoveLast
        End With

End Sub

Private Sub cmdNext_Click()

    With rs
        .Open "SELECT * FROM STUDENT_RECORDS", con, adOpenDynamic, adLockOptimistic
        While Not .EOF

        Text1.Text = rs!RollNo
        Text2.Text = rs!FirstName
        Text3.Text = rs!LastName
        Text4.Text = rs!CourseID
        Text5.Text = rs!Grade
        .MoveNext
         Wend

        .Close

    End With

End Sub

Private Sub cmdPrev_Click()
    With rs
        .Open "SELECT * FROM STUDENT_RECORDS",
        con, adOpenDynamic, adLockOptimistic

        While Not .EOF

        Text1.Text = rs!RollNo
        Text2.Text = rs!FirstName
        Text3.Text = rs!LastName
        Text4.Text = rs!CourseID
        Text5.Text = rs!Grade

        .MovePrevious
        Wend

     End With

End Sub



Private Sub cmdSave_Click()

    con.Execute "UPDATE STUDENT_RECORDS
    SET FirstName = '"
& Text2 & "',
    LastName = '"
& Text3 & "',
    CourseID ="
& Text4 & ",  
    Grade = '"
& Text5 & "'
    WHERE RollNo = "
& Text1 & ""

    MsgBox ("Record Updated")

End Sub

Private Sub Form_Load()

   constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
   Source=C:UsersGirishDocumentsStudentDB.mdb;
   Persist Security Info=False"

   con.Open constr

End Sub


Private Sub cmdAdd_Click()

    con.Execute "INSERT INTO
    STUDENT_RECORDS(RollNo,FirstName,LastName,CourseID,Grade)
    VALUES ('"
+ Text1.Text + "','" +
    Text2.Text + "','" +
    Text3.Text + "','" +
    Text4.Text + "','" +
    Text5.Text + "')"

    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    Text5.Text = ""

    con.Close

End Sub

 

Code for Module1.Bas

The Module1.Bas is the code for connection to Microsoft Access Database. Since  you may not want to define this connection on each form that is used in the application.

 
Public Sub loadcon()
    constr = "Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=C:UsersGirishDocumentsStudentDB.mdb;
    Persist Security Info=False"

    con.Open constr
End Sub

Note : The C:\Users\Peter\Documents\StudentDB.mdb; is path to the Access database , you need to change is according to path of your Student Database.

Advertisements