# Rolling Dice Program

In this post, I will teach you to create a rolling dice program in visual basic 6. The program will consist of a single dice with six dots representing numbers between 1 to 6 and button to roll the dice. After you have clicked the rolling button, the dice will give you a random number between 1 to 6.

## Prerequisites

To create this program, you need to have a computer that runs at least Windows XP Sp3 or above and Visual basic 6.0. I am running a virtual machine with Windows XP Sp3 which will also work, in case, you have a higher version of windows system.

## Design Form for Rolling Dice program

The first step is to open the Visual basic 6 program and create a new standard.exe form. In this form we will create shapes and buttons. Assuming that you know about toolbar, click on the Shape object from the toolbar and create a rectangle shape.

Form Properties

Name: frmRollDIce
Caption: Rolling Dice
Height: 7350
Width:9750

Now, you can set the properties of the above shape as given below.

Name: Dice
Fillcolor: &H00FFFFFF&
Fillstyle: 0-Solid
Shape: 4-Rounded Rectangle
Height: 4215
Width: 4215

Now, you can create black round shapes to simulate an actual dice. Since, we cannot show all the face of the dice, show only one face with 6 dots, that is, the maximum number that the dice can get when you roll it.

Click on the shape object again and create a single small rectangle inside dice shape you created earlier. Set the properties of this object as follows.

Name: Shape1
Fillcolor: &H00000000&
Fillstyle: 0-Solid
Shape: 3-Circle
Height:735
Width:735

Copy the black round shape and paste in the dice shape 5 more times. When you try to paste the shape object, you should receive a warning shown below.

Click on Yes and continue to copy the black round shape 5 more times and arrange them in the following order. You should end up with following form design.

The arrangement of dice is up to you, and you may use the Format tab on Visual basic 6 for align the back circles.

## Button to Roll Dice

Now it is time to add button for rolling the dice and getting a random number. Add a button object to the form and set the following properties.

Name: cmdRoll
Caption: Roll Dice
Backcolor: &H0000FFFF&
Style: 1-Graphical
Font: MS Sans Serief 14 Bold

Your final form should look like the following.

I have added a background color to the main form, you can also change the look and feel of your form design or leave it as it is.

## Code for Rolling Dice Program

The only code required here is for the button – Roll Dice because all action is going to happen when you click on the button only.

Add the following code to the button control.

Private Sub cmdRoll_Click()
n = Int(1 + Rnd * 6)
For i = 0 To 6
Shape1(i).Visible = False
Next

If n = 1 Then
Shape1(6).Visible = True
End If

If n = 2 Then
Shape1(1).Visible = True
Shape1(4).Visible = True
End If

If n = 3 Then
Shape1(0).Visible = True
Shape1(6).Visible = True
Shape1(5).Visible = True
End If

If n = 4 Then
Shape1(0).Visible = True
Shape1(2).Visible = True
Shape1(3).Visible = True
Shape1(5).Visible = True
End If

If n = 5 Then
Shape1(0).Visible = True
Shape1(2).Visible = True
Shape1(3).Visible = True
Shape1(5).Visible = True
Shape1(6).Visible = True
End If

If n = 6 Then
Shape1(0).Visible = True
Shape1(1).Visible = True
Shape1(2).Visible = True
Shape1(4).Visible = True
Shape1(5).Visible = True
Shape1(3).Visible = True
End If
End Sub


You program is completed and now you can try to roll the dice and see how it works. If there is a problem, recheck the code.

# VB 6 Projects

Visual Basic 6 is a GUI based object-oriented programming language.

You can develop software with visual basic much faster than other programming languages. Visual basic follow the rapid application model also known as RAD model.

Doing projects is a great way to learn visual basic programming. A visual basic project not only gives you an idea about software development, but also teaches you project planning and process involved in finishing the project successfully.

## Prerequisites

To take maximize your learning, you must complete these prerequisites because visual basic seems to be an easy programming language, but it is not.

• VB 6 Tutorial – you must be familiar with VB IDE and coding practices before creating your first project. You can do this in two ways – one, visit our VB tutorial page or read a visual programming book.
• VB 6 IDE – install visual basic 6 software to create your project. Read the Visual basic 6.0 installation guide.
• Microsoft Access – this is necessary for connecting your project to a data source, only if its a project requirement. For some projects you can use Microsoft Excel or SQL as the data sources.

## Recommended Books

Whether you are a student or self-learner, it is hard to learn programming without a good book. I recommend few good books here which has detailed information about Visual Basic 6.0 programming. It is also necessary because Microsoft has stopped supporting Visual basic 6; now they have more advanced version of visual basic in the form of VB.Net and other versions. However, the language of modern visual basic is more or less the same.

# 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

(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 kind of the 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.

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 the option to go to First Record, Last Record, Next Record and Previous Record.

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

ER Model

## Tables

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

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

## Components of frmStudentManagent System

Labels

Name :lblStudentManagement
Caption : Student Management System
Font: MS Sans Serif
Font-Size: 24 PX
Font-Weight : Bold

Buttons

Name : btnLogin
Font: MS-Sans-Serif
Font-weight:Bold
Font-Size: 10px

## Code for frmStudentManagement System

Private Sub Login_Click()

End Sub

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

Option Explicit
Dim constr As String
Private Sub cmdCancel_Click()
'set the global var to false '
'to denote a failed login '
Me.Hide
End Sub

Private Sub cmdOK_Click()
Dim userfound As Boolean
With rs .Open "SELECT * FROM LOGIN",
.MoveFirst While Not .EOF
frmSTUDREC.Show
Exit Sub
Me.Hide
ElseIf
.MoveNext
Else
MsgBox "Invalid Password, try again!", ,
SendKeys "{Home}+{End}"
Exit Sub
End If
Wend
.Close
End With
End Sub

MsgBox ("connected")
End Sub

## Components of frmCourse

Labels

Name : lblCID
Caption: Course ID
Name : lblCname
Caption : Course Name
Name : lblDuration
Caption : Duration(Semester):

Text-Boxes

Name : txtCID
Text : 'leave blank'
Name : txtCname
Text : 'leave blank'
Name : txtDuration
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 constr As String

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

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

Set DataGrid1.DataSource = rsGrid

End Sub

Private Sub cmdFirst_Click()

With rs .Open "SELECT * FROM Course",

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",

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",

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",

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

.MovePrevious
Wend
End With

End Sub

MsgBox ("Connected")

End Sub

## Components of frmStudentDetails

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
Caption : Student Courses
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 constr As String
Private Sub btnCourse_Click()
frmCourse.Show
End Sub
Private Sub cmdData_Click()
rsGrid.Open "SELECT * FROM Student_Records",
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()
End Sub
Private Sub cmdFirst_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
.MoveFirst
.Close
End With
End Sub
Private Sub cmdLast_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
.MoveLast
End With
End Sub
Private Sub cmdNext_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
While Not .EOF
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
.MoveNext
Wend
.Close
End With
End Sub
Private Sub cmdPrev_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
While Not .EOF
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
.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
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:UsersGirishDocumentsStudentDB.mdb;
Persist Security Info=False"
con.Open constr
End Sub
con.Execute "INSERT INTO
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.

# Library Management System using VB 6 with MS Access Database

### User Activities

Since, this is a medium size project we don’t need use- case diagrams. However, you are free to use them and modify the project. The main activities of the library management system are:

• Managing members information that includes adding, deleting, updating, and displaying member records.
• Each library member including admin must login to perform any transaction. Therefore, a user ID and password is given to every member.
• Managing book details in the library such as adding , deleting, updating, and displaying book information. Each book has member ID of the person who borrowed the book recently. Therefore, looking at book details we know who has the book from the library.
• Every member can check the availability of the book before borrowing it.

The library management system you develop must contain the following modules.

1. frmLibraryManagement (Main Form)
3. frmBook (Book Form)
4. frmMember (Member Form)
5. frmBorrow (Book Borrow Form)
7. Module1.bas (Module)

### System Design

The library management main form with login button is displayed to the users. The users login to access the menu (MDI Form). The users can manage books or members at anytime within menu form . Also,members can check availability of books and borrow it if available.

The system diagram shows how the overall system works.

### Data Model – ER Diagram

The ER diagram or entity-relationship diagram begins with identifying all entities of the project. You can write activities or use an activity diagram to identify entities.

• Member has user ID and password.
• Member can borrow books and return after use.
• A single book is allocated to only one member.
• Member can check availability of a book.
• Book details and Catalog of all books in library.

From above description we identified three entities and their relationships.

• Borrow and Return
• Library member
• Book Details

The ER diagram is below shows relationship between all entities.

Location of the Database

In the next section, you will be creating a database in MS Access 2003; therefore, you must create the database in following location – C:/VBProjects/Library/library.mdb for this project to work. If the path is incorrect, the program will not run successfully.

### Relational Model – Tables

Now we need to convert the ER Model to a Relational Model which means creating a table for each entity in the ER Model.

Book Table

The book table in Access Design View is given below. One of the Field Name is “Primary Key”, in this case, BookID.

Member Table

The screenshot for the Member table in Microsoft Access Design View is given below. The Field Name “MemberID” is the “Primary Key” for this table”.

Borrow Table

The third table in MS Access Design View is Borrow. This table has multiple fields that serve as the “Primary Key”. The primary key are BookID, MemberID, and Return.

Note: Primary Key is a field that uniquely identifies each row in the table.

Relationship

A relationship is MS Access is similar to the e-r diagram we created previously. We can use the e-r diagram to create relationships between tables.

To view or create relationships among tables, go to Database Tool from the Toolbar > [Click] Relationship under Show/Hide.

The relationship between Member and Borrow is called One-to-Many and the relationship between Book and Borrow is One-to-Many.

### Connect to MS Access Database Using Module1.bas

All form need to connect to Microsoft Access Database but you may have to write the code for connection in each form. It there is a large number of forms in the project, say 10, then these extra line of codes become redundant. To save us from this trouble, create Module1.bas file from Project1.vbp windows.The code for Module is given below.

Public con As New ADODB.Connection
Public constr As String
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBProjects\Library\library.mdb;Persist Security Info=False"
con.Open constr
End Sub

### Components for frmLibraryManagement

You can design the interface for each of the forms in Visual Basic now. However, you must rename them properly for sake of coding. Let’s create components for frmLibraryManagement as follows and rename them.

Form Details

Name:frmLibraryManagement
Caption :LIBRARY MANAGEMENT SYSTEM
BackColor :Window Text
ForeColor : Highlight Text

Now add a frame control and add all other controls on top of it.

Frame

Name:frameLibraryManagement
Caption :Library Management
BackColor :Window Text
ForeColor : Highlight Text

Labels

Name : lblLibraryManagement
Caption : LIBRARY MANAGEMENT SYSTEM
BackColor :Window Text
ForeColor : Highlight Text
Font :Arimo 18px Bold
Name : lblCompany
Caption : NotesforMSc
BackColor :Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 12px Bold
Name : lblCopyright
BackColor :Window Text
ForeColor : Highlight Text
Font : MS Sans-serif px Bold

Buttons

Name : cmdLogin
BackColor : Button Highlight
Font : MS Sans-serif 12px Bold
Style :1-Graphical

#### Code for frmLibraryManagement

Private Sub cmdLogin_Click()
End Sub

The login screen is different because you want password to be hidden when you type them. To create Login dialog, {Right-Click] Project.vbp > Select Add > [Click] Form. The new form dialog box will appear, select Log in Dialog.

Now we must rename and change the code for frmLogin.

Form Details

Name : frmLogin
BackColor: WindowText
ForeColor : Highlight Text

Labels

Name : lblUserID
Caption : UserID
BackColor : Window Text
ForeColor : Highlight Text
Name : lblPassword
BackColor : Window text
ForeColor : Highlight Text

Text-Boxes

Name: txtUserID
Text : 'leave blank'
Name : txtPassword
Text : 'leave blank'

Buttons

Name : cmdOk
Caption : OK
Name: cmdCancel
Caption : Cancel

Option Explicit

Private Sub cmdCancel_Click()
'set the global var to false
Me.Hide
End Sub

Private Sub cmdOK_Click()

While Not rs.EOF
If rs!MemberID = Val(txtUserID) Then

'place code to here to pass the '
'success to the calling sub'
'setting a global var is the easiest'

Me.Hide
con.Close
Exit Sub

Else

SendKeys "{Home}+{End}"

End If
End If

rs.MoveNext
Wend
End Sub

End Sub

### Components for frmBook

All details of books are available from this form.You can add new books, delete a book, update book information, and view the entire books in the library from this form.

Form Details

Name : frmBook
Caption : Book Details
BackColor : Window Text
ForeColor : Highlight Text

First create a single frame control and add all other controls on top of it.

Name : frameBook
Caption : Book Details
BackColor : Window Text
ForeColor : Highlight Text

Labels

Name : lblBookMain
Alignment : Center
Caption : Book Details
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 18px Bold
Name : lblBookID
Alignment : Center
Caption : BOOK ID
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBookTitle
Alignment : Center
Caption : BOOK TITLE
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblAuthor
Alignment : Center
Caption : AUTHOR
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBNote
Alignment : Center
Caption : NOTE:
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBInstruction
Alignment : Center
Caption : Only use BOOK ID for DISPLAY
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold

Textboxes

Name : txtBookID
Text :'leave empty'
Name : txtBookTitle
Text :'leave empty'
Name :txtAuthor
Text: 'leave empty'

Buttons

Name : cmdBAdd
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdBDelete
Caption : &DELETE
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdBUpdate
Caption : &UPDATE
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdBDisplay
Caption : &DISPLAY
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdClear
Caption : &CLEAR
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdBExit
Caption : &EXIT
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdBCatalog
Caption : &CATALOG
BackColor : Button Highlight
Style : 1-Graphical

DataGrid Control

Name : BookFinder

#### Code for frmBook

Option Explicit

con.Execute "INSERT INTO Book (BookID, BookTitle, Author) VALUES (" & txtBookID & ",'" & txtBookTitle & "','" & txtAuthor & "')"
MsgBox ("Record Inserted Successfully")
con.Close
End Sub

Private Sub cmdBCatalog_Click()
If Dir$("C:/VBProjects/Library/savebookquery.xml") <> "" Then Kill "C:/VBProjects/Library/savebookquery.xml" End If rs.Save "C:/VBProjects/Library/savebookquery.xml", adPersistXML rs.Close con.Close Set rs = Nothing rs.Open "C:/VBProjects/Library/savebookquery.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile Set BookFinder.DataSource = rs Set rs = Nothing End Sub Private Sub cmdBDelete_Click() Call loadcon con.Execute "DELETE * FROM Book WHERE BookID = " & txtBookID & "" MsgBox ("Record Deleted Successfully") con.Close End Sub Private Sub cmdBDisplay_Click() Call loadcon rs.CursorLocation = adUseClient rs.Open "SELECT Book.[BookID], Book.[BookTitle],Borrow.[MemberID],Member.[MemberName],Borrow.[StartDate] FROM Book,Borrow,Member WHERE Book.[BookID] = " & txtBookID & " And Book.[BookID]= Borrow.[BookID]And Borrow.[MemberID]=Member.[MemberID] And Borrow.[Return] = 0", con, adOpenDynamic, adLockOptimistic If Dir$("C:/VBProjects/Library/savebookdisplay.xml") <> "" Then
Kill "C:/VBProjects/Library/savebookdisplay.xml"
End If
rs.Close
con.Close
Set rs = Nothing
Set BookFinder.DataSource = rs
Set rs = Nothing
End Sub

Private Sub cmdBExit_Click()
End Sub

Private Sub cmdBUpdate_Click()
con.Execute "UPDATE Book SET BookTitle = '" & txtBookTitle & "', Author = '" & txtAuthor & "' WHERE BookID = " & txtBookID & ""
MsgBox ("Record Updated Successfully")
con.Close
End Sub

Private Sub cmdClear_Click()
txtBookID.Text = " "
txtBookTitle.Text = " "
txtAuthor.Text = " "
End Sub

### Components for frmMember

This form will provide information about library members including the book they currently borrowed. You can create new members, delete members, and update their information except except MemberID.

Form Details

Name : frmMember
Caption : Member
BackColor : Window Text
ForeColor : Highlight Text

Frame Control

Name : frameMember
Caption : Member Registration
BackColor : Window Text
ForeColor : Highlight Text

Now you can start adding all the other control on top of frame. You want to make controls visible on frame,then right click frame and click send to back.

Labels

Name :lblMemberMain
Alignment :Center
Caption : Member Information
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 18px Bold
Name :lblMID
Caption : Member ID
BackColor : Window Text
ForeColor : Highlight Text
Font :MS Sans-serif 8px Bold
Name :lblMName
Caption : Member Name
BackColor : Window Text
ForeColor : Highlight Text
Font :MS Sans-serif 8px Bold
Name :lblMNote
Caption : NOTE:
BackColor : Window Text
ForeColor : Highlight Text
Font :MS Sans-serif 8px Bold
Name :lblMInstruction
Caption : Only use Member ID before DISPLAY
BackColor : Window Text
ForeColor : Highlight Text
Font :MS Sans-serif 8px Bold

Textboxes

Name: txtMID
Text :'leave blank'
Name: txtMName
Text :'leave blank'
Name: txtMPass
Text :'leave blank'

Buttons

Name :cmdMAdd
BackColor : Window Text
Style : 1-Graphical
Name :cmdMDel
Caption : &DELETE
BackColor : Window Text
Style : 1-Graphical
Name :cmdMUpdate
Caption : &UPDATE
BackColor : Window Text
Style : 1-Graphical
Name :cmdMDisplay
Caption : &DISPLAY
BackColor : Window Text
Style : 1-Graphical
Name :cmdMClear
Caption : &CLEAR
BackColor : Window Text
Style : 1-Graphical
Name :cmdMExit
Caption : &EXIT
BackColor : Window Text
Style : 1-Graphical
Name :cmdMAllMember
Caption : &Show Members
BackColor : Window Text
Style : 1-Graphical

DataGrid Control

Name :MemberFinder

#### Code for frmMember

Private Sub cmdAllMember_Click()
If Dir$("C:/VBProjects/Library/saveshowmembers.xml") <> "" Then Kill "C:/VBProjects/Library/saveshowmembers.xml" End If rs.Save "C:/VBProjects/Library/saveshowmembers.xml", adPersistXML rs.Close con.Close Set rs = Nothing rs.Open "C:/VBProjects/Library/saveshowmembers.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile Set MemberFinder.DataSource = rs Set rs = Nothing End Sub Private Sub cmdMAdd_Click() Call loadcon con.Execute "INSERT INTO Member (MemberID,MemberName,Pass) VALUES (" & txtMID & ",'" & txtMName & "','" & txtMPass & "')" MsgBox ("Record Inserted Successfully") con.Close End Sub Private Sub cmdMClear_Click() txtMID.Text = " " txtMName.Text = " " txtMPass.Text = " " End Sub Private Sub cmdMDel_Click() Call loadcon con.Execute "DELETE * FROM Member WHERE MemberID = " & txtMID & "" MsgBox ("Record Deleted Successully") con.Close End Sub Private Sub cmdMDisplay_Click() Call loadcon rs.CursorLocation = adUseClient rs.Open "SELECT Member.[MemberID],Member.[MemberName],Borrow.[Bookid],Borrow.[bookTitle]FROM Member, Borrow WHERE Member.[MemberID]= " & txtMID & " And Member.[MemberID] = Borrow.[MemberID] And Borrow.[Return]= 0", con, adOpenDynamic, adLockOptimistic If Dir$("C:/VBProjects/Library/savememberdisplay.xml") <> "" Then
Kill "C:/VBProjects/Library/savememberdisplay.xml"
End If
rs.Close
con.Close
Set rs = Nothing
Set MemberFinder.DataSource = rs
Set rs = Nothing
End Sub

Private Sub cmdMExit_Click()
End Sub

Private Sub cmdMUpdate_Click()
con.Execute "UPDATE Member SET MemberName = '" & txtMName & "',Pass = '" & txtMPass & "' WHERE MemberID = " & txtMID & ""
MsgBox ("Record Updated Successfully")
con.Close
End Sub

### Components for frmBorrow

The form Borrow allows you to check the availability of books, and if the book is available you can borrow it. If member wants to return the book they can enter details of the book and return it immediately.

Form Borrow Details

Name : frmBorrow
Caption : Book Borrow
BackColor : Window Text
ForeColor : Highlight Text

Frame Controls

Name : frameBookBorrow
Caption : Book Borrow
BackColor : Window Text
ForeColor : Highlight Text
Name : frameAvailibility
Caption : Availibility
BackColor : Window Text
ForeColor : Highlight Text

Once you have created a frame control, add other controls on top of it. Also right click frame and send to back the frame.

Under the frame book borrow add following controls.

Labels

Name : lblBorrowMain
Caption : Book Borrow Details
BackColor : Window Text
ForeColor :Highlight Text
Font : Arimo 18px Bold
Name : lblBBookID
Caption : Book ID
BackColor : Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBTitle
Caption : Book Title
BackColor : Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBMemberID
Caption : Member ID
BackColor : Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBorrowNote
Caption : NOTE:
BackColor : Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBInstructions
Caption : Always CLEAR before any Query or Update
BackColor : Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold

Textboxes

Name : txtBBookID
Text : 'leave blank'
Name : txtBTitle
Text : 'leave blank'
Name : txtBMemberID
Text : 'leave blank'

Buttons

Name : cmdBorrow
Caption :&Borrow
BackColor : Window Text
ForeColor : Highlight Text
Style :1-Graphical
Name : cmdReturn
Caption :&Return
BackColor : Window Text
ForeColor : Highlight Text
Style :1-Graphical
Name : cmdBClear
Caption :&Clear
BackColor : Window Text
ForeColor : Highlight Text
Style :1-Graphical
Name : cmdExit
Caption :&Exit
BackColor : Window Text
ForeColor : Highlight Text
Style :1-Graphical

Now you can add controls under Check Availability frame.

Labels under Check Availability

Name : lblBBookTitle
Caption : Book Title
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold
Name : lblBResult
Caption : Result
BackColor : Window Text
ForeColor : Highlight Text
Font : MS Sans-serif 8px Bold

Textboxes

Name :txtBBookTitle
Text : 'leave blank'
Name :txtBResult
Text : 'leave blank'

Buttons

Name : cmdAvailibility
Caption :&Check Availibility
BackColor : Window Text
ForeColor : Highlight Text
Style :1-Graphical

#### Code for frmBorrow

Private Sub cmdAvailibility_Click()
rs.Open "SELECT * FROM Borrow WHERE BookTitle = '" & txtBBookTitle & "' And Return = 0", con, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
txtBResult.Text = "Not Available"
Else
txtBResult.Text = "Available"
End If
rs.Close
con.Close
End Sub

Private Sub cmdBClear_Click()
txtBBookID.Text = ""
txtBMemberID.Text = " "
txtBBookTitle.Text = " "
txtBResult.Text = ""
txtBTitle.Text = " "
End Sub

Private Sub cmdBorrow_Click()
rs.Open "INSERT INTO Borrow (BookID, MemberID, BookTitle,StartDate) VALUES (" & txtBBookID & "," & txtBMemberID & ",'" & txtBTitle & "',#" & Format(Now, "mm/dd/yyyy") & "#)", con, adOpenDynamic, adLockOptimistic
MsgBox ("Book Borrowed Successully")
con.Close

End Sub

Private Sub cmdExit_Click()
con.Close
End If
End Sub

Private Sub cmdReturn_Click()
rs.Open "UPDATE Borrow SET Return = 1 WHERE MemberID = " & txtBMemberID & " And BookID = " & txtBBookID & "", con, adOpenStatic, adLockReadOnly
MsgBox "Book Returned Successfully"
con.Close
End Sub

### Components for frmMENU – MDI form

To create a MDI form, [Right-Click] Project1.vbp > Select Add > [Click] MDI Form. Create following menu items using Menu Editor.

You have to create four menu items with following values. Do not change any other settings. However, you can use arrow buttons to move item up and down.

Borrow Book

Name : borrow
Caption : Borrow Book
Index: 3

Member Details

Name : members
Caption : Member Details
Index : 1

Book Details

Name : books
Caption : Book Details
Index : 2

Exit

Name : exit
Caption : Exit
Index : 0

Before adding code for frmMENU, make sure you have created all other forms of the Library Management Project,

When you click on any menu item it will open another form within the parent MDI form. The other form that must open within MDI form should have their MDI child property set to True.

Private Sub books_Click(Index As Integer)
frmBook.Show
End Sub

Private Sub borrow_Click(Index As Integer)
frmBorrow.Show
End Sub

Private Sub exit_Click(Index As Integer)
End Sub

Private Sub members_Click(Index As Integer)
frmMember.Show
End Sub

The menu item allows multiple forms at the same time. If you want to allow only single form to load then hide other forms. See an example below.

Private Sub members_Click(Index As Integer)
frmMember.Show
frmBorrow.hide
frmBook.hide
End Sub

Note: You cannot add the code for Module 1 because first you have to add the components for Microsoft ADO Data Control 6.0 (OLEDB) and Microsoft Data grid Control 6.0 (OLEDB).

# Bank Management System in VB 6.0 using MS Access Database

In this article, you will create an application in visual basic 6.0 with Microsoft access database as a back-end to manage customer transactions and bank employee details. This project is called bank management system in vb 6.0 using ms access.

The bank management system in vb 6.0 using ms access. must do the following tasks.

• INSERT, DELETE, UPDATE records of Employees and Customers.
• Keep track of customer transactions. A customer is allowed to withdraw and deposit money, not exceeding the balance in the account.
• Bank employees only work for the branch with unique code.

The system will be developed in following ways.

• Develop System Design
• Data Model [ E-R Diagram]
• User Interface in VB 6.0
• Code the different Modules
• Verify the Output

## DATA MODEL

### Tables

Using the E-R Diagram we can create a relational model of the database. It means we can create tables with the database. To create a new database go to the Microsoft Access Database and create a new blank database. If you have MS Access 2007 or Higher save the database with (.mdb) extension. Otherwise, Visual Basic 6 may not recognize the database file.

Create three four tables within the database with appropriate data-types given in the following figures.

### MODULES AND FORMS

The modules used in this application are given below

• frmBankManagement
• frmBranch
• frmCustomer
• frmEmployee
• frmTransaction
• Module1.bas

## Components of frmBankManagement

Labels:

Name : lblBankManagement
Caption : Bank Management System

Buttons:

Name : cmdLogin
Caption : LOGIN

## Code for frmBankMangement

Private Sub cmdLogin_Click()

End Sub

Labels

Name : lblUserID
Caption : UserID
Caption : Password

Text-boxes

Name : txtUserID
Text : 'leave blank'
Text : Password

Buttons

Name : cmdLogin
Name : cmdCancel
Caption : Cancel

Option Explicit

Private Sub cmdCancel_Click()

'set the global var to false 'to denote a failed login

Me.Hide
End Sub

= adUseClient rec.Open "SELECT * FROM Employee", con,

While Not rs.EOF
con.Close MsgBox ("Connection Closed")
Exit Sub
ElseIf

con.Close
MsgBox ("Connection Closed")
Exit Sub
ElseIf
Val(txtUserID) <> rs!AccountNo Then

rs.MoveNext ElseIf Val(txtUserID) <>
rec!EID Then rec.MoveNext

Else
MsgBox "Invalid Password, try again!", ,
SendKeys "{Home}+{End}"

Exit Sub
End If
Wend
rs.Close
rec.Close
End Sub

MsgBox ("Connected")
End Sub

## Components for frmBranch

Labels

Name : lblBID
Caption : BRANCH ID
Name : lblBcode
Caption :BRANCH CODE
Name : lblLocation
Caption : LOCATION
Name : lblTotEmp
Caption : TOTAL EMPLOYEE

Text-boxes

Name: txtBID
Text : 'leave blank'
Name : txtBcode
Text : 'leave blank'
Name : txtLocation
Text : 'leave blank'
Name : txtTotEmp
Text : 'leave blank'

Buttons

Name : cmdAdd
Caption : &ADD
Name : cmdDel
Caption : &DELETE
Name : cmdUpdate
Caption: &UPDATE
Name : cmdDisplay
Caption : &DISPLAY
Name : cmdClr
Caption : &CLEAR
Name : cmdExit
Caption : &EXIT

DataGrids

Name : DataGrid1

## Code for frmBranch

Private Sub cmdAdd_Click()

Dim total As Double

rs.Open "SELECT Count(EID) FROM Employee",

total = rs.Fields(0).Value

con.Execute "INSERT INTO
Branch(BID, Bcode, Location , TotalEmployees )
VALUES (" & txtBID & ",'" & txtBcode &
"','" & txtLocation & "', " & total & ")"

End Sub

Private Sub cmdClr_Click()

txtBID = " "
txtBcode = " "
txtLocation = " "
txtTotEmp = " "

End Sub

Private Sub cmdDel_Click()
con.Execute "DELETE * FROM Branch
WHERE BID = " & txtBID & ""
MsgBox ("Record Deleted")

End Sub

Private Sub cmdDisplay_Click()

rs.Open "SELECT * FROM Branch",

Set DataGrid1.DataSource = rs

End Sub

Private Sub cmdExit_Click()

con.Close

End Sub

Private Sub cmdUpdate_Click()
Dim total As Double

rs.Open "SELECT Count(EID) FROM
Employee WHERE BID = " &
txtBID & " ", con,

total = rs.Fields(0).Value

con.Execute "UPDATE Branch SET Bcode
= '" & txtBcode & "',Location
= '" & txtLocation & " ',
TotalEmployees = " & total & "
WHERE BID = " & txtBID & " "

MsgBox ("Record Updated ")

End Sub

End Sub

## Components of frmCustomer

Labels

Name :lblAno
Caption : ACCOUNT NO
Name :lblAname
Caption : ACCOUNT NAME
Name : lblAddress
Caption : BALANCE
Name : lblBrankID
Caption : BRANCH ID

Text-Boxes

Name : txtAno
Text : 'leave blank'
Name : txtAname
Text ; 'leave blank'
Name : txtAddress
Text : 'leave blank'
Name : txtBalance
Text : 'leave blank'
Name : txtBID
Text : 'leave blank'
Name : cmdAdd
Caption : &ADD
Name : cmdDel
Caption : &DELETE
Name : cmdClr
Caption : &CLEAR
Name : cmdExit
Caption : &EXIT
Name : cmdUPDATE
Caption : &UPDATE
Name : cmdDisplay
Caption : &DISPLAY

## Code for frmCustomer

Private Sub cmdAdd_Click()

con.Execute "INSERT INTO Customer (
AccountNo, AccountName, Address, Balance, BID )
VALUES(" & txtAno & ",' " & txtAname &
" ',' " & txtAddress & " '," &
txtBalance & ", " & txtBID & ")"
txtAno = "" txtAname = "" txtAddress = ""
txtBalance = "" txtBID = ""

End Sub

Private Sub cmdClr_Click()

txtAno = ""
txtAname = ""
txtBalance = ""
txtBID = ""

End Sub

Private Sub cmdDel_Click()

con.Execute "DELETE * FROM Customer
WHERE AccountNo = " & txtAno & ""

MsgBox ("Record Deleted")

End Sub

Private Sub cmdDisplay_Click()

rs.Open "SELECT * FROM Customer",
Set DataGrid1.DataSource = rs

End Sub

Private Sub cmdExit_Click()

con.Close

End Sub

Private Sub cmdUpdate_Click()

con.Execute "UPDATE Customer
SET AccountName = '" & txtAname & " ',
Balance = " & txtBalance & "
WHERE AccountNo = " & txtAno & "" txtAno = ""

MsgBox ("Record Updated!")

End Sub

MsgBox ("connected")
End Sub

## Components of frmEmployees

Labels

Name : lblEID
Caption : EID
Name : lblEname
Caption : EMPLOYEE NAME
Name : lblDesig
Caption : DESIGNATION
Name : lblSalary
Caption : SALARY
Name : lblWeekoff
Caption :WEEKOFF
Name : lblBranchID
Caption : BRANCH ID

Text-boxes

Name : txtEID
Text : 'leave blank'
Name : txtEname
Text : 'leave blank'
Name : txtDesig
Text ; 'leave blank'
Name ; txtSalary
Text : 'leave blank'
Name : txtWeekoff
Text : 'leave blank'
Name : txtBID
Text : 'leave blank'

Buttons

Name : cmdAdd
Caption : &ADD
Name : cmdDel
Caption : &DELETE
Name : cmdUpdate
Caption : &UPDATE
Name : cmdDisplay
Caption : &DISPLAY
Name : cmdClr
Caption : &CLEAR
Name : cmdExit
Caption : &EXIT

DataGrid

Name : DataGrid 1

## Code for frmEmployee

Private Sub cmdAdd_Click()
con.Execute "INSERT INTO
Employee (EID, Ename, Designation, Salary, Weekoff, BID)
VALUES (" & txtEID & ",'" &
txtEname & "','" &
txtDesig & "', " &
txtSalary & ", '" &
txtWeekoff & "', " &
txtBID & ")"
txtEID = "" txtEname = "" txtDesig
= "" txtSalary = "" txtWeekoff = "" txtBID = ""

End Sub

Private Sub cmdClr_Click()

txtEID = ""
txtEname = ""
txtDesig = ""
txtSalary = ""
txtWeekoff = ""
txtBID = ""

End Sub

Private Sub cmdDel_Click()

con.Execute "DELETE * FROM
Employee WHERE EID = " &
txtEID & ""

MsgBox ("Record Deleted ")

txtEID = ""

End Sub

Private Sub cmdDisplay_Click()

rs.Open "SELECT * FROM Employee",
Set DataGrid1.DataSource = rs

End Sub

Private Sub cmdExit_Click()

con.Close

End Sub

Private Sub cmdUpdate_Click()

con.Execute "UPDATE Employee
SET Ename = '" &
txtEname & "',Designation = '" &
txtDesig & "',Salary = " &
txtSalary & ",Weekoff = ' " &
txtWeekoff & " ', BID = " &
txtBID & " WHERE EID = " &
txtBID & " "

MsgBox ("Record Updated")

End Sub

End Sub

## Components of frmTransaction

Labels

Name : lblTID
Caption : TRANSACTION ID
Name : lblBID
Caption : BRANCH ID
Name :lblAno
Caption : ACCOUNT NO
Name : lblWDraw
Caption : WITHDRAW
Name : lblDEPOSIT
Caption : lblDeposit
Name : lblDate
Caption : DATE

Text-boxes

Name : txtTID
Text : 'leave blank'
Name : txtBID
Text : 'leave blank'
Name : txtAno
Text : 'leave blank'
Name : txtWDraw
Text : 'leave blank'
Name : txtDeposit
Text : 'leave blank'
Name : txtDate
Text : 'leave blank'

Buttons

Name : cmdAdd
Caption : &ADD
Name : cmdDel
Caption : &DELETE
Name : cmdUpdate
Caption : &UPDATE
Name : cmdDisplay
Caption : &DISPLAY
Name : cmdClr
Caption : &CLEAR
Name : cmdExit
Caption : &EXIT

Data Grids

Name : Data Grid 1

## Code for frmTransaction

Private Sub cmdAdd_Click()

con.Execute "INSERT INTO
Trans(TID, BID, AccountNo, Withdraw, Deposit, TransactionDate )
VALUES (" & txtTID & "," &
txtBID & ", " &
txtAno & "," &
txtWDraw & ", " &
txtDeposit & ", ' " &
txtDate & "' )"
txtTID = "" txtBID = "" txtAno
= "" txtWDraw = "" txtDeposit
= "" txtDate = ""

End Sub

Private Sub cmdClr_Click()

txtTID = ""
txtBID = ""
txtAno = ""
txtWDraw = ""
txtDeposit = ""
txtDate = ""

End Sub

Private Sub cmdDel_Click()

con.Execute "DELETE * FROM
Trans WHERE TID = " &
txtTID & ""

MsgBox ("Record Deleted")

End Sub

Private Sub cmdDisplay_Click()

rs.Open "SELECT * FROM Trans ", con,
Set DataGrid1.DataSource = rs

End Sub

Private Sub cmdExit_Click()
con.Close
End Sub

Private Sub cmdUpdate_Click()

con.Execute "UPDATE Trans SET
BID = " & txtBID & " ,
AccountNo = " & txtAno & " ,
Withdraw = " & txtWDraw & ",
Deposit = " & txtDeposit & "
WHERE TID = " & txtTID & ""
txtTID = "" txtBID = "" txtAno
= "" txtWDraw = "" txtDeposit = "" txtDate = ""

MsgBox ("Record Updated")

End Sub

MsgBox ("connected")

End Sub

## frmMENU [ MDI Form ]

The MDI form is that form which has MENU and when you click on any one of the menu item, it opens up another MDI Child form.

We will set MDI Child property for following forms

• frmBranch
• frmCustomer
• frmEmployee
• frmTransaction

Go to the property of each form and set the MDI Child to ‘True’.

Under Project 1 , [Right Click] the Forms > [Click] Add > [Click] MDI Form.

Configure Menu using the Menu Editor in MDI Form and write appropriate code for the form.

Private Sub branch_Click(Index As Integer)
frmBranch.Show
End Sub

Private Sub customer_Click(Index As Integer)
frmCustomer.Show
End Sub

Private Sub employee_Click(Index As Integer)
frmEmployee.Show
End Sub

Private Sub exit_Click(Index As Integer)
End Sub

Private Sub transaction_Click(Index As Integer)
frmTransaction.Show
End Sub

### Code for Module1.Bas

Most of the forms in the application need to connect to the database, so you may have to write code for connectivity with Microsoft Access Database repeatedly in each form.

Instead of writing the code for connectivity in each form, we write it once in a Module and call the function from the form. In this way, we save time to write the same code many times.

Here is the code for Module1.bas

Note; To know more about creating a new Module go to one of the previous Visual Basic 6 posts.

Public con As ADODB.Connection
Public constr As String
Public Sub loadcon() constr = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:UsersGirishDocumentsStudentDB.mdb;Persist Security Info=False"
con.Open constr
End Sub
ï»¿

# 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.

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.

## 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

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 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 constr As String

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

End Sub

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

Option Explicit

Private Sub cmdCancel_Click()
'set the global var to false
Me.Hide
End Sub

Private Sub cmdOK_Click()
With

.MoveFirst While Not .EOF

con.Close
MsgBox ("Connection Closed")
Exit Sub
ElseIf
txtUserID <> !UserID Then
.MoveNext
Else
Exit Sub
End If
Wend
.Close
End With

End Sub

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

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()
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 = " &
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 & "",

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

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

Private Sub cmdUpdate_Click()

con.Execute "UPDATE Dependent
SET  DependentName = ' " &
txtDName & " ' , Relation = ' " &
txtRel & " ' WHERE DependentID = " &
txtDID & " "
MsgBox ("Record Updated")

End Sub

End Sub

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

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)
End Sub

Private Sub salary_Click(Index As Integer)
frmSalary.Show
End Sub

# Payroll Processing System in VB 6 With Ms Access

In this example, we are going to create a payroll processing system in VB 6 with Ms Access as the database. To complete this project, you need a computer with windows operating system XP or higher installed, Visual Basic 6.0 software, and Microsoft Access program anything higher than Ms Access 2003 or higher.

For the sake of this example, we are using VB 6.0 software and windows 7 64-bit system with Ms Access 2013 installed.

## Payroll Processing System

The payroll processing system allow use to do following things with the employee information.

• Modify Employee Record
• Delete the Employee Record

The main purpose of the payroll processing system is to calculate the salary of each employee.

Steps To Design And Implement This Project

To design and implement this project, we will follow a simple process listed below.

• Create a Database with tables for Employee and Salary
• Design the form for Employee records
• Write codes for the Employee form
• Design the form for Salary records
• Write codes for Salary form.
• Connect the database with the form
• Test each functionality and correct any errors.

## Create The Database

The very first step is to create a database in Microsoft Access and add two tables. Open Ms Access 2013 and click ‘ Create a blank database’ and type the name of the database as ‘ EmpDB.mdb’. Save it to you drive in a folder called ‘AccessDB’.

At this moment the database is empty and it has nothing. Our task is to create two tables in the database. When you switch to Design view in Ms Access, you will asked to name your table, you can call it – Employee and click OK.

You table must have fields and corresponding data types. See the image below.

Once you have created tables, then you must create a relationship between these two tables. The employee number is the unique in both table. Each employee can have one salary account. Therefore, we must have a one-to-one relationship between employee table and the salary table.

Before you create the relationship close the tables, then go to database tools in Ms Access and click Relationships. The next step is to add both employee table and salary table in the relationship window.

You must click on the relationship link and edit the relationship. Select ‘Enforce Referential Integrity’ and also select ‘Cascade Update Related Fields’ and ‘Cascade Delete Related Record’. These two properties will make sure that if you delete any record from one table, that will also remove it from the other.

Now you can add some records to both the table. It is good to add at least 5 records in both table. Leave the Net field empty in Salary table because we need to calculate it and update it from our VB 6 program.

Note that the net salary is not calculated yet and salary is linked to a single employee.

## Form Design For Employee Relation

In this section we will design a form in VB 6 for employee relation so that we could add, update, and delete records from the database. There will also be link to main program or to exit from the employee form.

Form

Name: frmEmployee
Caption: Employee Details
BackColor: &H00404000&
ForeColor: Highlight Text
Height: 5775
Width: 10515

Labels

Name: lblEmpNo
Caption: Employee No:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Width: 2295
Height: 495
Name: lblEmpname
Caption: Employee Name:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Width: 2295
Height: 495
Name: lblAge
Caption: Age
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Width: 2295
Height: 495
Name: lblAddress
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Width: 2295
Height: 495
Name: lblDob
Caption: D.O.B
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Width: 2295
Height: 495
Name: lblPhone
Caption: Phone No:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Width: 2295
Height: 495

Textboxes

Name: txtEno
Height: 695
Width: 2895
Text: 'leave blank'
Name: txtEname
Height: 695
Width: 2895
Text: 'leave blank'
Name: txtAge
Height: 695
Width: 2895
Text: 'leave blank'
Name: txtAddress
Height: 695
Width: 2895
Text: 'leave blank'
Name: txtDob
Height: 695
Width: 2895
Text: 'leave blank'
Name: txtPhone
Height: 695
Width: 2895
Text: 'leave blank'

Buttons

Name: cmdAdd
Font: Ms Sans Serif, 12, Regular
Name: cmdModify
Caption: &MODIFY
Font: Ms Sans Serif, 12, Regular
Name: cmdDelete
Caption: &DELETE
Font: Ms Sans Serif, 12, Regular
Name: cmdClear
Caption: &CLEAR
Font: Ms Sans Serif, 12, Regular
Name: cmdMain
Caption: &MAIN
Font: Ms Sans Serif, 12, Regular
Name: cmdExit
Caption: &EXIT
Font: Ms Sans Serif, 12, Regular

## Code For Employee Form Of Payroll Processing System

Option Explicit

con.Execute "INSERT INTO Employee (Eno, Ename,Age,Address, Dob, Phone) VALUES ('" & txtEno & "','" & txtEname & "','" & txtAge & "','" & txtAddress & "','" & txtDob & "', '" & txtPhone & "')"

End Sub

Private Sub cmdClear_Click()
txtEno.Text = " "
txtEname.Text = " "
txtAge.Text = " "
txtDob.Text = " "
txtPhone.Text = " "
End Sub

Private Sub cmdDelete_Click()
con.Execute "DELETE * FROM Employee WHERE Eno = " & txtEno & ""

MsgBox ("Record Deleted")
txtEno = ""
End Sub

Private Sub cmdExit_Click()
con.Close
End Sub

Private Sub cmdMain_Click()
frmPayrollProcessing.Show
con.Close
End Sub

Private Sub cmdModify_Click()
con.Execute "UPDATE Employee SET Eno = '" & txtEno & "', Ename = ' " & txtEname & " ', Age = '" & txtAge & " ', Address = '" & txtAddress & "', Dob ='" & txtDob & "', Phone = '" & txtPhone & "' WHERE Eno = ' " & txtEno & " '"
MsgBox "Record Updated"

End Sub

MsgBox ("Connected")
End Sub

## Form Design For Salary Relation

In this section, we will create a form to add salary account for existing employees, calculate their net salaries. The form will also print the net salary of an employee in a text file.

Form

Name: frmSalary
Caption: Salary
BackColor: &H00404000&
ForeColor: Highlight Text
Height: 5775
Width: 10515

Labels

Name: lblSalEno
Caption: Employee No:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175
Name: lblSalDesig
Caption: Designation:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175
Name: lblSalBasic
Caption: Basic:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175
Name: lblDa
Caption: DA:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175
Name: lblSalHra
Caption: HRA:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175
Name: lblSalPf
Caption: Pf:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175
Name: lblSalNet
Caption: NET SALARY:
BackColor: &H00404000&
ForeColor: Highlight Text
Font: Ms Sans Serif, 12, Bold
Height: 615
Width: 2175

Textboxes

Name: txtSalEno
Height: 615
Width: 1935
Text: 'leave blank'
Name: txtSalDesig
Height: 615
Width: 1935
Text: 'leave blank'
Name: txtSalBasic
Height: 615
Width: 1935
Text: 'leave blank'
Name: txtSalDa
Height: 615
Width: 1935
Text: 'leave blank'
Name: txtSalHra
Height: 615
Width: 1935
Text: 'leave blank'
Name: txtSalPf
Height: 615
Width: 1935
Text: 'leave blank'
Name: txtSalNet
Height: 615
Width: 1935
Text: 'leave blank'

Buttons

Name: cmdAdd
Font: MS Sans Serif, 12, Regular
Height: 735
Width: 1935
Name: cmdUpdate
Caption: &UPDATE
Font: MS Sans Serif, 12, Regular
Height: 735
Width: 1935
Name: cmdCalculate
Caption: &CALCULATE
Font: MS Sans Serif, 12, Regular
Height: 735
Width: 1935
Name: cmdPrint
Caption: &PRINT
Font: MS Sans Serif, 12, Regular
Height: 735
Width: 1935
Name: cmdExit
Caption: &EXIT
Font: MS Sans Serif, 12, Regular
Height: 735
Width: 1935

## Code For Salary Form Of Payroll Processing System

Private Sub cmdAdd_Click()
con.Execute "INSERT INTO Salary (Eno, Designation, Basic, Da, Hra, Pf,Net) VALUES ('" & txtSalEno & "','" & txtSalDesig & "','" & txtSalBasic & "','" & txtSalDa & "','" & txtSalHra & "', '" & txtSalPf & "','" & txtSalNet & "')"
End Sub

Private Sub cmdCalculate_Click()
Dim Basic As Double
Dim Da As Double
Dim Hra As Double
Dim Pf As Double
Dim Net As Double

Basic = Val(txtSalBasic.Text)

Hra = Basic * 0.2
Da = Basic * 0.1
Pf = Basic * 0.1
Net = Basic + Da + Hra + Pf
txtSalHra.Text = Str(Hra)
txtSalDa.Text = Str(Da)
txtSalPf.Text = Str(Pf)
txtSalNet.Text = Str(Net)
End Sub

Private Sub cmdExit_Click()
con.Close
End Sub

Private Sub cmdPrint_Click()
Dim sFileText As String
Dim iFileNo As Integer
iFileNo = FreeFile
'open the file for writing
Open "C:\AccessDB\Test.txt" For Output As #iFileNo

'write some example text to the file
Print #iFileNo, "Employee No:"; txtSalEno.Text
Print #iFileNo, "Employee Designation: "; txtSalDesig.Text
Print #iFileNo, "Basic:"; txtSalBasic.Text; ""
Print #iFileNo, "Net Salary:"; txtSalNet.Text
MsgBox " Printing Done!!"

'close the file (if you dont do this, you wont be able to open it again!)
Close #iFileNo

End Sub

Private Sub cmdUpdate_Click()
con.Execute "UPDATE Salary SET Basic = '" & txtSalBasic & "' , Da = '" & txtSalDa & "', Hra = '" & txtSalHra & "', Pf = '" & txtSalPf & "', Net = '" & txtSalNet & " ' WHERE Eno = '" & txtSalEno & " ' "

MsgBox ("Updated")
End Sub

MsgBox "Connected!"
End Sub



## Create A Main Form For The Payroll Processing Project

Since we have two separate forms and we want to be able to go to any form. In this section, we will create a new main form. Here is the design of the main form in VB 6.

Form

Name: frmPayrollProcessing
Caption: Payroll Processing System
BackColor: &H00404000&
ForeColor: Highlight Text
Height: 5475
Width: 8940

Labels

Name: lblMainBanner
Caption: Payroll Processing System
BackColor: &H00404000&
ForeColor: Highlight Text

Buttons

Name: cmdMainEmp
Caption: &EMPLOYEE
Font: Ms Sans Serif, 12, Regular
Height: 1182
Width: 2205
Name: cmdMainSalary
Caption: &SALARY
Font: Ms Sans Serif, 12, Regular
Height: 1182
Width: 2205
Name: cmdMainExit
Caption: &EXIT
Font: Ms Sans Serif, 12, Regular
Height: 1182
Width: 2205

Make sure that you start your project with the main form. Therefore, go to Project tab on the top and click on Project1 Properties. Select ‘frmPayrollProcessing‘ under Startup Object.

## Code For Main Form

Private Sub cmdMainEmp_Click()
frmEmp.Show
End Sub
Private Sub cmdMainExit_Click()
End Sub
Private Sub cmdMainSalary_Click()
frmSalary.Show
End Sub

## Create A Module To Establish Connection With Database

The final step in this project is to create a module that help you establish a connection with the Ms Access database. This step is very important for your project success.

To create a new module, go to Project Explorer and right-click on Forms > select Add > click Module.

The module code window will open up immediately. Insert the following code and recheck.

Public con As New ADODB.Connection
Public constr As String

constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AccessDB\EmpDB.mdb;Persist Security Info=False"
con.Open constr
End Sub

## Testing The Project

There are two phase to testing the project – payroll processing.

1. You always starts with Employee table and create employee records
2. If you want to compute the salary of an employee , Enter the employee no, designation, and basic and click on the Calculate button. The program will automatically compute the net salary.
3. The third step is to print the output to a text file. Each time you click on the print button it will over write the file located in C:/Test.txt. If there is not file with that name , the program will create it when you click on the print button.

The net salary and other details are automatically processed.

This record is new for Salary table; therefore, it is not updated but added to the salary table. You can click the Add button to add the record in Salary table.

There is a print button which will print the information in a text file. Press that PRINT button and check the output text file located in AccessDB folder where you keep your database files.

Open the text file – Test.txt and you should see an entry for your latest database entry.

There are other functionalities like Update, Modify, etc which you can test on your own.

# Student Attendance Management System Using MS Access and VB 6 Part – 4

Student Attendance Management System Using MS Access and VB 6 Part 4 – In this article, we will continue to build the interface for attendance management system that will meet the user requirements.

You will build the following interfaces.

• View Attendance (frmViewAttendance)
• Today’s Attendance (frmTodayAttendance)
• Student Record Management (frmStudent)
• Teacher Record Management (frmTeacherRecord)

Let us get started.

## View Attendance

The view attendance interface allows students, teachers, and admin to view attendance between specific dates, count the hours present, and display if they have the eligibility to attend the exams.

The total number of hours of class for a student is 300 hours. If the student is able to get at least 150 hour present then he or she is eligible to attend the exam. The program display how many hour to complete to qualify and number of hours present in the class.

### System Diagram For View Attendance

The view attendance interface requires three input values – name, start date, and end date of attendance taken. It display current total when a student was present and deficit- remaining hours to be eligible for exam. Finally, the eligibility of a student to appear in the exam as mentioned earlier.

### Form Design(frmViewAttendance)

The next step is to create a form called frmViewAttendance that matches with the above system diagram. You can create a form design given below. It is not necessary that your form should exactly like the following figure. You may try a different design as well.

### Components for View Attendance

The components for view attendance interface are listed below.

Form Details

Name : frmViewAttendance
Caption : View Attendance
BackColor: Windows Text

Labels

Name: lblHead
Alignment : 2-Center
Caption: View Attendance
BackColor: Windows Text
ForeColor: Highlight Text
Font : Arimo 18px Bold
Name: lblName
Alignment : 0-Left Justify
Caption: Name
BackColor: Windows Text
ForeColor: Highlight Text
Name: lblStartDate
Alignment : 0-Left Justify
Caption: Start Date
BackColor: Windows Text
ForeColor: Highlight Text
Name: lblEndDate
Alignment : 0-Left Justify
Caption: End Date
BackColor: Windows Text
ForeColor: Highlight Text
Name: lblDeficit
Alignment : 0-Left Justify
Caption: Deficit
BackColor: Windows Text
ForeColor: Highlight Text
Name: lblCurrentTotal
Alignment : 0-Left Justify
Caption: Current Total
BackColor: Windows Text
ForeColor: Highlight Text
Name: lblEligible
Alignment : 0-Left Justify
Caption: Is the student eligible for exam ?
BackColor: Windows Text
ForeColor: Highlight Text

Textboxes

Name : txtSname
Text : 'none'
Name : txtSdate
Text : 'none'
Name : txtSdate
Text : 'none'
Name : txtEdate
Text: 'none'
Name : txtDeficit
Text : 'none'
Name : txtCurrent
Text : 'none'
Name : txtEligible
Text : 'none'

Buttons

Name : cmdSearch
Caption : &Search
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdClose
Caption : &Close
BackColor : Button Highlight
Style : 1-Graphical

Data-grid Control

Name : DGViewAttendance

### Code for View Attendance

Dim rs1 As New ADODB.Recordset
Dim total As Integer
Dim deficit As Integer
Public Sub openrecords()

rs.Open "SELECT Student.[SID],Student.[Sname],Attendance.[Adate],Attendance.[APresent] FROM Attendance,Student WHERE Student.[Sid]= Attendance.[SID] And Student.[Sname] = '" & txtSname & "' And ( Attendance.[Adate] Between  # " & Format(txtSdate.Text, "mm/dd/yyyy") & " # And  # " & Format(txtEdate.Text, "mm/dd/yyyy") & " # )", con, adOpenDynamic, adLockPessimistic
rs1.Open "SELECT Count(APresent)FROM Attendance,Student WHERE Student.[Sid]= Attendance.[SID] And Student.[Sname] = '" & txtSname & "' And ( Attendance.[Adate] Between  # " & Format(txtSdate.Text, "mm/dd/yyyy") & " # And  # " & Format(txtEdate.Text, "mm/dd/yyyy") & " # )", con, adOpenDynamic, adLockPessimistic

End Sub

Private Sub cmdCLOSE_Click()

rs.Close
con.Close
'closing connection

End Sub

Private Sub cmdSearch_Click()

'making connection
Call openrecords
Dim Present As String

Present = "YES"

total = 300
deficit = total - rs1.Fields(0).Value

txtCurrent.Text = rs1.Fields(0).Value

txtDeficit.Text = Str(deficit)
If deficit <= 150 Then
txtEligible.Text = "ELIGIBLE FOR EXAM"
txtEligible.ForeColor = vbGreen
Else
txtEligible.Text = "NOT ELIGIBLE"
txtEligible.ForeColor = vbRed

Set DGViewAttendance.DataSource = rs

End If
End Sub

## Today’s Attendance

The today’s attendance is only visible to teachers. When the teacher log into the system, his or her students are listed automatically. The teacher can browse through the student data and mark attendance.

### System Diagram for Today’s Attendance

There is not input required for today’s attendance other than the teacher must log into the system with their own id. Only those who log into the system will get their student list. One teacher cannot see students of other teachers.

### Form Design (frmTodayattendance)

The next step is to design a form based on the system diagram. You may come up with your own design.

### Components for Today Attendance

Here is the list of components used to build the today’s attendance interface.

Form Details

Name : frmTodayAttendance
Caption: Today's Attendance
BackColor: Windows Text

Labels

Name : lblTodayMain
Caption: Today's Attendance
BackColor: Windows Text
ForeColor : Highlight Text
Font : Arimo 18px Bold
Name : lblTSid
Caption: Student ID
BackColor: Windows Text
ForeColor : Highlight Text
Name : lblTSname
Caption: Student Name
BackColor: Windows Text
ForeColor : Highlight Text
Name : lblTodaySection
Caption: Section
BackColor: Windows Text
ForeColor : Highlight Text
Name : lblTodayClass
Caption: Class
BackColor: Windows Text
ForeColor : Highlight Text

Textboxes

Name : txtTSid
Text : 'none'
Name : txtTodaySname
Text : 'none'
Name : txtTodaySection
Text : 'none'
Name : txtTodayClass
Text : 'none'

Data-Grid Control

Name : DGToday

Frame Control

The frame control is for marking attendance and it has two option buttons. Once we create frame, right click on the frame and click-“send to back“. After which you can create option button on top of it.

Name : frameMarkAttendance
Caption: Mark Attendance
BackColor :Window Text
ForeColor : Highlight Text

Option Buttons

Name : OptionYes
Caption : YES
BackColor :Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold
Name : OptionNo
Caption : NO
BackColor :Window Text
ForeColor :Highlight Text
Font : MS Sans-serif 8px Bold

Buttons

Name : cmdFirst
Caption : &First
BackColor: Button Highlight
Style : 1-Graphical
Name : cmdLast
Caption : &Last
BackColor: Button Highlight
Style : 1-Graphical
Name : cmdNext
Caption : &Next
BackColor: Button Highlight
Style : 1-Graphical
Name : cmdPrevious
Caption : &Previous
BackColor: Button Highlight
Style : 1-Graphical
Name : cmdClose
Caption : &Close
BackColor: Button Highlight
Style : 1-Graphical

### Code for Today Attendance

Option Explicit
Public sqltodaystr As String
Public Sub CreateConnection()
sqltodaystr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBProjects\Attendance\attendance.mdb;Persist Security Info=False"
contoday.Open sqltodaystr
End Sub
Public Sub OpenRecordset()
Dim tid As Integer

rstoday.Open "SELECT Teaches.[SID],Teaches.[TID],Student.[Sname],Student.[Section],Student.[Class] FROM Student,Teaches WHERE Teaches.[TID] = " & tid & " And Student.[Sid] = Teaches.[SID]", contoday, adOpenDynamic, adLockPessimistic
Set DGToday.DataSource = rstoday
rstoday.ActiveConnection = Nothing
End Sub

Private Sub cmdCLOSE_Click()
rstoday.Close
contoday.Close
End Sub

Private Sub cmdFirst_Click()

rstoday.MoveFirst
txtTSid.Text = rstoday.Fields("Sid").Value
txtTodaySname.Text = rstoday.Fields("Sname").Value
txtTodaySection.Text = rstoday.Fields("Section").Value
txtTodayClass.Text = rstoday.Fields("Class").Value

End Sub

Private Sub cmdLast_Click()

rstoday.MoveLast
txtTSid.Text = rstoday.Fields("Sid").Value
txtTodaySname.Text = rstoday.Fields("Sname").Value
txtTodaySection.Text = rstoday.Fields("Section").Value
txtTodayClass.Text = rstoday.Fields("Class").Value

End Sub

Private Sub cmdNext_Click()

rstoday.MoveNext
If rstoday.EOF = True Then
rstoday.MoveFirst
End If
txtTSid.Text = rstoday.Fields("Sid").Value
txtTodaySname.Text = rstoday.Fields("Sname").Value
txtTodaySection.Text = rstoday.Fields("Section").Value
txtTodayClass.Text = rstoday.Fields("Class").Value

End Sub

Private Sub cmdPrevious_Click()

rstoday.MovePrevious
If rstoday.BOF = True Then
rstoday.MoveLast
End If
txtTSid.Text = rstoday.Fields("Sid").Value
txtTodaySname.Text = rstoday.Fields("Sname").Value
txtTodaySection.Text = rstoday.Fields("Section").Value
txtTodayClass.Text = rstoday.Fields("Class").Value

End Sub

CreateConnection
OpenRecordset
End Sub

Private Sub OptionNo_Click()
Dim Curr_date As Date
Dim Absent As String
Dim todaytid As Integer
Absent = "NO"
Curr_date = Format(Now, "mm/dd/yyyy")

con.Execute ("INSERT INTO Attendance VALUES(" & _
"#" & Curr_date & "#," & _
"" & txtTSid & "," & _
"" & todaytid & "," & _
"'" & Absent & "')")

OptionYes.Value = False
OptionNo.Value = False
con.Close
End Sub

Private Sub OptionYes_Click()
Dim Curr_date As Date
Dim Present As String
Dim todaytid As Integer
Present = "YES"
Curr_date = Format(Now, "mm/dd/yyyy")

con.Execute ("INSERT INTO Attendance VALUES(" & _
"#" & Curr_date & "#," & _
"" & txtTSid & "," & _
"" & todaytid & "," & _
"'" & Present & "')")

OptionYes.Value = False
OptionNo.Value = False
con.Close

End Sub
ï»¿

## Student Record Management

The student record management is the job of admin staff. The admin can add new students, update student records, and delete the student information from the database.

### System Diagram for Student Record Management

The system diagram for student record management has two parts – inputs and tasks. The inputs for the system is student details, and once the input is received, the admin can perform one of the following tasks.

• UPDATE
• DELETE
• CLEAR
• CLOSE

Here is the system diagram.

### Form Design (frmStudent)

The next step is to use the system diagram as a guide and design the following form.

In the next section, we will discuss each component of the form in detail.

### Component for Student Record Management

Here are the components for student management form.

Form Details

Name : frmStudent
Caption : Student Record Management
BackColor : Window Text

Now we need a frame control to organize our other controls. First create a frame and then right click and select “Send to Back”. Start creating other controls such as labels, textboxes and button on top of it. If you do not want a frame skip this step and continue with rest of the controls.

Frame Control

Name : frameStudentRecord
Caption : Student Record Management
BackColor : Window Text
ForeColor : Highlight Text

Labels

Name : lblStudentMain
Caption : Student Record Management
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 18px Bold
Name : lblSSID
Caption : Student ID
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold
Name : lblSTID
Caption : Teacher ID
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold
Name : lblSSname
Caption : Student Name
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold
Name : lblSection
Caption : Section
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold
Name : lblClass
Caption : Class
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold
Name : lblPassword
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold
Name : lblAID
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Bold

Textboxes

Name :txtSID
Text :'none'
Name :txtSTID
Text :'none'
Name :txtSname
Text :'none'
Name :txtSection
Text :'none'
Name :txtClass
Text :'none'
Name :txtSpass
Text :'none'
Name :txtAID
Text :'none'

Buttons

Name : cmdADD
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdUpdate
Caption : &UPDATE
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdDelete
Caption : &DELETE
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdClear
Caption : &CLEAR
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdClose
Caption : &CLOSE
BackColor : Button Highlight
Style : 1-Graphical

### Code for Student Management System

Option Explicit

con.Execute ("INSERT INTO Student VALUES(" & txtSID & ",'" & txtSname & " ','" & txtSection & " ' ,'" & txtClass & " ' ,' " & txtSpass & " ', " & txtAID & " );")
con.Execute ("INSERT INTO Teaches VALUES (" & txtSID & "," & txtSTid & ");")

con.Close

End Sub

Private Sub cmdCLEAR_Click()
txtSname = " "
txtSID = " "
txtSTid = " "
txtSection = " "
txtClass = " "
txtSpass = ""
txtAID = " "
End Sub

Private Sub cmdCLOSE_Click()
End Sub

Private Sub cmdDelete_Click()
con.Execute "DELETE * FROM Attendance WHERE SID = " & txtSID & ""
con.Execute "DELETE * FROM Teaches WHERE SID = " & txtSID & ""
con.Execute "DELETE * FROM Student WHERE Sid = " & txtSID & ""
MsgBox ("Record Deleted!")
con.Close
End Sub

Private Sub cmdUPDATE_Click()
con.Execute "UPDATE Student SET Student.[Sname] = '" & txtSname & "', Student.[Section] = '" & txtSection & "',Student.[Class], Student.[Password] = '" & txtSpass & "' WHERE SID = " & txtSID & "; "
MsgBox ("Record Updated ")

End Sub

Private Sub txtSID_GotFocus()
txtSTid.Enabled = False
txtAID.Enabled = False
End Sub

Private Sub txtSname_GotFocus()
txtSTid.Enabled = False
txtAID.Enabled = False
End Sub
ï»¿

## Teacher Record Management

The teacher record management is similar to student management. To maintain the records of old and new teaching staff is the job of administrators.

### Form Design (frmTeacherRecord)

The next step is to design a form similar to given below.

In the section below, we will discuss the components used in the interface – Teacher Record Management.

### Components for Teacher Record Management

Here is the list of components for above interface.

Form Details

Name : frmTeacherRecord
Caption : Teacher Record Management
BackColor : Window Text

Frame Control

You can organize the form using the frame control. First create the control and then right click and ‘Send-to-back’. After that you can simply add other controls on top of the frame.

Name : frameTeacher
Caption : Teacher Record Management
BackColor : Window Text
ForeColor : Highlight Text

Labels

Name : lblTeacherMain
Alignment : 2-Center
Caption : Teacher Record Management
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 18px Bold
Name : lblTID
Alignment : 0-Left Justify
Caption : Teacher ID
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 10px Bold
Name : lblTNAME
Alignment : 0-Left Justify
Caption : Teacher Name
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 10px Bold
Name : lblCOURSE
Alignment : 0-Left Justify
Caption : Course
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 10px Bold
Name : lblPASS
Alignment : 0-Left Justify
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 10px Bold
Name : lblAID
Alignment : 0-Left Justify
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 10px Bold

Textboxes

Name :txtTID
Text : 'none'
Name :txtTNAME
Text : 'none'
Name :txtCOURSE
Text : 'none'
Name :txtTpass
Text : 'none'
Name :txtAID
Text : 'none'

Buttons

Name : cmdADD
BackColor : Window Text
Style : 1-Graphical
Name : cmdUpdae
Caption :&UPDATE
BackColor : Window Text
Style : 1-Graphical
Name : cmdDELETE
Caption :&DELETE
BackColor : Window Text
Style : 1-Graphical
Name : cmdCLR
Caption :&CLEAR
BackColor : Window Text
Style : 1-Graphical
Name : cmdCLOSE
Caption :&CLOSE
BackColor : Window Text
Style : 1-Graphical

### Code for Teacher Record Management

Private Sub cmdADD_Click()

con.Execute ("INSERT INTO Teacher VALUES(" & txtTID & ",'" & txtTNAME & " ','" & txtCOURSE & " ' ,' " & txtTpass & " ', " & txtAID & " );")
End Sub

Private Sub cmdCLOSE_Click()

End Sub

Private Sub cmdCLR_Click()

txtTID.Text = ""
txtTNAME.Text = ""
txtCOURSE.Text = ""
txtTpass.Text = ""
txtAID.Text = ""

End Sub

Private Sub cmdDelete_Click()

con.Execute ("DELETE * FROM Attendance WHERE TID = " & txtTID & " ")
con.Execute ("DELETE * FROM Teaches WHERE TID = " & txtTID & " ")
con.Execute ("DELETE * FROM Teacher WHERE Tid = " & txtTID & " ")
MsgBox ("Record Deleted!")
con.Close
End Sub

Private Sub cmdUPDATE_Click()
con.Execute "UPDATE Teacher SET Teacher.Tname = '" & txtTNAME & "', Teacher.Course = '" & txtCOURSE & "', Teacher.Password = '" & txtTpass & "' WHERE Tid = " & txtTID & "; "

MsgBox ("Record Updated ")
con.Close
End Sub

In the next part, you will learn to put all the above forms in one place called MDI form. The MDI form integrates your VB project forms together.

# Student Attendance Management System Using VB 6 Part – 3

In Student Attendance Management System Using VB 6 Part 3, we will learn to create an interface for the attendance system that will meet the user requirements.

Here is the list of interfaces that we need to create.

• Main Screen (frmSplash)
• View Attendance (frmViewAttendance)
• Today’s Attendance (frmTodayAttendance)
• Student Record Management (frmStudent)
• Teacher Record Management (frmTeacherRecord)

For each of the above interfaces which also the VB form we will list out the following things.

• System Diagram
• Form Design
• Components and its properties
• Visual basic code

The system design is a high-level overview of individual components which show how each component of interface is related.

## Connect To MS Access Database

Before we do anything we must first establish connection with MS Access database that we created earlier. If you did not create a database go to attendance management part-2 and create a database first.

To connect to database we will create a VB 6 module. Modules in VB are files that contain source codes. You can use them throughout your project without repeating them on each form. We must create a single module to connect with ms access database automatically.

To create a module, go to project explorer, right-click Form, select Add, and click Module.

## Code For Module.bas

Now we must add code so that our VB project can programmatically connect to the MS Access database.

Public con As New ADODB.Connection
Public constr As String

constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBProjects\Attendance\attendance.mdb;Persist Security Info=False"

con.Open constr
MsgBox ("connected")
End Sub

The function loadcon() is called whenever database connectivity is required. All connections must be closed as soon as form unloads

## Main Screen

The main screen is the first screen that a user is going to see. This section discusses the design of the Student Attendance Management System Using VB 6 Part 3 main splash screen.

### Form Design (frmSpalsh)

The splash screen is a special screen. You can add a splash screen from Standard Toolbar > Form > under Add Form, select Add a Splash and modify the form according to your project.

### Components for Main Screen

The splash screen comes with pre-configured labels. You need to modify them and add some new controls in this project. Here are the list of components.

Form Details

Name : frmSplash
Caption : Attendance Management System
BackColor : Window Text
ForeColor : Highlight Text

Frame Control

Now you need to add a frame control and right click and click “send to back”. Existing controls are not visible;therefore, delete them and create identical labels on top of frame control. If you wish, skip frame control and continue creating rest of the controls.

Name : frameMain
Caption : 'leave empty'
BackColor : Window Text

Labels

Name : lblCompanyProduct
Caption : NotesforMSc
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 14px Bold
Name : lblProductName
Caption : Attendance Management System
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 20px Bold
Name : lblPlatform
Caption : Windows 7
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 16px Regular
Name : lblPlatform
Caption : 64-Bit
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 16px Regular
Name : lblCopyright
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Regular
Name : lblCompany
Caption : Company-www.Notesformsc.org
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Regular
Name : lblWarning
Caption : Warning: Do not copy or reproduce without permission except for the educational purpose
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 8px Regular

Buttons

Name : cmdStu
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdTeach
BackColor : Button Highlight
Style : 1-Graphical
Name : cmdAdmin
BackColor : Button Highlight
Style : 1-Graphical

### Code For Main Screen

Option Explicit

End Sub

Private Sub cmdStu_Click()
End Sub

Private Sub cmdTeach_Click()

End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)

End Sub

lblVersion.Caption = "Version " & App.Major & "." & App.Minor & "." & App.Revision
lblProductName.Caption = App.Title

End Sub

Private Sub Frame1_Click()
End Sub

Note that above code may give error because login forms are not created yet. Continue to create login forms.

### System Diagram For Student Login

The easiest way to create a login form is to click Standard toolbar on VB project editor and click Form > select Log in Dialog from Add Form Window. The login in dialog comes with a preexisting code which you can modify later. In this example project, we have used the login dialog.

The second option is to create a new form and design the login screen yourself. Later , you can modify the appearance for the form.

The components for student login is listed below.

Form Details

Name : frmLogin
BackColor : Window Text
ForeColor : Highlight Text

Labels

Name : lblUserID
Caption : SID
BackColor : Window Text
ForeColor : Highlight Text
Name : lblPassword
BackColor : Window Text
ForeColor : Highlight Text

Textboxes

Name : txtSID
Text : 'leave blank'
Name : txtPassword
Text : 'leave blank'

All other properties remain same.

Option Explicit
Private Sub cmdCancel_Click()
'set the global var to false ' to denote a failed login

Me.Hide
End Sub
Private Sub cmdOK_Click()

'making connection

While Not rs.EOF
If rs!SID = Val(txtSID) Then
'place code to here to pass the 'success to the calling sub 'setting a global var is the easiest LoginSucceeded = True
'closing connection and recordset
rs.Close
con.Close

Exit Sub
Else
SendKeys "{Home}+{End}"
End If
End If
rs.MoveNext
Wend
End Sub

frmSplash.Hide
End Sub
ï»¿

### System Diagram for Teacher Login

You can create a form from the Standard Toolbar > Form > Log in Dialog and modify the form and its code. The second option is to create a new form and write login code which is difficult/

The components for teacher login is listed below. These are controls used in the form design according to the system diagram.

Form Details

Name : frmTULogin
BackColor : Window Text

Labels

Name : lblTeacherLogin
Alignment : Center
BackColor : Window Text
ForeColor : Hightlight Text
Font : Arimo 12px Bold
Name : lblTID
Caption : TID
BackColor : Window Text
ForeColor : Hightlight Text
Name : lblTPassword
BackColor : Window Text
ForeColor : Hightlight Text

Textboxes

Name : txtTID
Text : 'leave blank'
Name : txtPassword
Text : 'leave blank'

All other properties and controls stay the same. Note that the ‘leave blank‘ means you must remove any text already there.

The code for teacher login is created by modifying the preexisting code of “login dialog” form.

Option Explicit
Private Sub cmdCancel_Click()
'set the global var to false ' to denote a failed login

Me.Hide
End Sub
Private Sub cmdOK_Click()

'making connection
While Not rs.EOF
If rs!tid = Val(txtTID) Then
'place code to here to pass the 'success to the calling sub 'setting a global var is the easiest LoginSucceeded = True
'closing connection and recordset
rs.Close
con.Close

Exit Sub
Else
SendKeys "{Home}+{End}"
End If
End If
rs.MoveNext
Wend
End Sub

frmSplash.Hide
End Sub

The admin is the user with highest privileges, but the login behavior of administrator staff is the same as everyone.

Create the login form in the same way that you did for student and teacher. See section above.

Form Details

Name : frmALogin
BackColor : Window Text

Labels

Name : lblAdminLogin
BackColor : Window Text
ForeColor : Highlight Text
Font : Arimo 12px Bold
Name : lblAID
Caption : AID
BackColor : Window Text
ForeColor : Highlight Text
Name : lblAPassword
BackColor : Window Text
ForeColor : Highlight Text

TextBoxes

Name :txtAID
Text : 'leave blank'
Name :txtPassword
Text : 'leave blank'

Option Explicit
Private Sub cmdCancel_Click()
'set the global var to false ' to denote a failed login

Me.Hide
End Sub
Private Sub cmdOK_Click()

'making connection
While Not rs.EOF
If rs!AID = Val(txtAID) Then
'place code to here to pass the 'success to the calling sub 'setting a global var is the easiest LoginSucceeded = True
'closing connection and recordset
rs.Close
con.Close

Exit Sub
Else
SendKeys "{Home}+{End}"
End If
End If
rs.MoveNext
Wend
End Sub

frmSplash.Hide
End Sub

In the next part we will continue building the interface for the attendance management system.

# Student Attendance Management System Using VB 6 Part – 1

This is a 5 part series where we will learn to build Student Attendance Management System Using VB 6  using ms access as the back-end database and visual basic 6.0 as a front end.

This series goes through following stages and each of the stages is explained in a separate article in details.

1. Requirement Analysis
2. Database design, creating relations and relationships
3. Interface design and Coding
4. MDI form and Modules
5. Testing and Project completion

Before you start the project make sure that you have visual basic 6 installed and working. You also require MS Access any version above 2003-2007.

If you have a higher version of MS Access, then save your files in MS Access 2003-2007 format (.mdb).

Let us start building the application.

## Requirement Analysis

The first thing before making any application is to understand the customer requirement and its called a requirement analysis. The end product is a document called SRS, but for this document, we will not create any such document.

This part will only analyze the customer requirement in details and project constraints that will affect the application to built.

In this case,

” The client is a college that wants an attendance management system. The customer wants the separate login for students, teachers, and admin staff. The student must be able to view only their own and other student attendance. The must be able to check attendance between certain date ranges (say semester) and find out how much is the deficit, current total and eligibility to sit in the exam.

The teachers can mark daily attendance for themselves and for other teachers if they are replacing them. They should be able to view attendance for their students as well.

The admin staff has full privilege they can view attendance, manage student records and teacher records but cannot mark attendance.”

You read the verbatim and make a note of all the important points that may be necessary for the project.

There are three kinds of users in this system – student, teacher and admin staff. So we now list out all the requirements of each user.

Student

2. View attendance
3. View friend’s attendance

Teacher

2. View attendance for a student
3. Mark attendance only for teacher that has logged in.

2. View attendance
3. Manage student database
4. Manage the teacher database

Based on the requirement analysis, we can create a high-level diagram for the attendance management system and identify how the user will use the system. This is called a  use-case diagram.

## Use-Case Diagram

The use-case diagram identifies the goal of the system in meeting user requirements. In this section, we will create a use-case diagram for the attendance management system.

The first step is to identify the actors. An actor is a user with a specific role. Earlier we identified 3 actors.

1. Student
2. Teacher

Based on the requirement analysis we could identify primary tasks of the system.

• View Attendance
• Mark Today Attendance
• Manage Student Records
• Manage Teacher Records

Let’s make the use-case diagram for the attendance management system.

Now that we have a clear picture of what the system must do to meet the user requirements. We have to look at the attendance management system in more detail.

## System Diagram for Attendance Management

One way to show the details of the system is through a system diagram that provides a high-level view of the system. The system diagram shows the parts of the system that interact with each other.

We wish to create a system diagram that shows the hierarchical relationship between the components of the system.

In future articles, we will discuss the system diagram for each of the components and see how they help in building interfaces for our project.