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.
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.
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.
In this post, we will create an application for Student Record Management System in Visual Basic 6 with Microsoft Access Database.
The list of Modules and Forms used in the program as follows.
(A) frmStudentManagement System
Caption: STUDENT RECORD MANAGEMENT SYSTEM
(B) frmLogin
Caption: LOGIN
(C) frmCourse
Caption: COURSE DETAILS
(D) frmStudentDetails
Caption: STUDENT DETAILS
(E) Module1.Bas
System Design
First, we will design the System Model and simple high-level diagram of each module will be enough. There are many 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.
A student must Log In to access the System
The Administrator has to log in to the system with username and password provided during registration process. No one is allowed to log in without a proper User ID and Password.
Administrators must provide valid Username and Password
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.
Maintain Student Record using Student Details Form
Next, there is an option to maintain the Course Details, when you click a Specific button. We will discuss that later.
Course Details
Data Model
ER Model
ER Diagram for Student Management
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.
Course Table in Design View
Login Table in Design View
Components of frmStudentManagent System
Form Student Management System
Labels
Name :lblStudentManagement
Caption : Student Management System
Font: MS Sans Serif
Font-Size: 24 PX
Font-Weight : Bold
Buttons
Name : btnLogin
Caption: Log in
Font: MS-Sans-Serif
Font-weight:Bold
Font-Size: 10px
Code for frmStudentManagement System
Private Sub Login_Click()
frmLogin.Show
End Sub
Components of frmLogin
Login Form – Student Management
Labels
Name: lblUserID
Caption : User Name:
Name : lblPassword
Caption: Password
Text-Boxes
Name : txtUserID
Text : 'leave blank'
Name : txtPassword
Text : 'leave blank'
Buttons
Name : cmdOK
Caption : Log in
Name : cmdCancel
Caption : Cancel
Code for frmLogin
Option Explicit
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim constr As String
Public LoginSucceeded As Boolean
Private Sub cmdCancel_Click()
'set the global var to false '
'to denote a failed login '
LoginSucceeded = False
Me.Hide
End Sub
Private Sub cmdOK_Click()
Dim userfound As Boolean
With rs .Open "SELECT * FROM LOGIN",
con, adOpenDynamic, adLockOptimistic
'check the username '
.MoveFirst While Not .EOF
If txtUserName = !RollNo And
txtPassword = !Password Then
frmSTUDREC.Show
LoginSucceeded = True
Exit Sub
Me.Hide
ElseIf
txtUserName <> !RollNo Then
.MoveNext
Else
MsgBox "Invalid Password, try again!", ,
"Login" txtPassword.SetFocus
SendKeys "{Home}+{End}"
Exit Sub
End If
Wend
.Close
End With
End Sub
Private Sub Form_Load()
Call loadcon
MsgBox ("connected")
End Sub
Components of frmCourse
Form Course Details – Student Management
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 rs As New ADODB.Recordset
Dim constr As String
Private Sub cmdAddCourse_Click()
con.Execute "INSERT INTO
Course(CourseID, CourseName, Duration)
VALUES ('" + txtCID.Text + "','" +
txtCNAME.Text + "','" + txtDURATION.Text + "')"
txtCID.Text = "" txtCNAME.Text = "" txtDURATION.Text = ""
con.Close
End Sub
Private Sub cmdDelCourse_Click()
con.Execute "DELETE * FROM COURSE
WHERE CourseID = " &
txtCID & ""
End Sub
Private Sub cmdDisplay_Click()
Dim rsGrid As New ADODB.Recordset
con.CursorLocation = adUseClient rsGrid.CursorLocation
= adUseClient rsGrid.CursorType = adOpenStatic
rsGrid.Open "SELECT * FROM Student_Records
WHERE CourseID = " &
txtCID & "",
con, adOpenForwardOnly, adLockPessimistic
Set DataGrid1.DataSource = rsGrid
End Sub
Private Sub cmdFirst_Click()
With rs .Open "SELECT * FROM Course",
con, adOpenDynamic, adLockOptimistic
While Not .EOF
txtCID.Text = rs!CourseID
txtCNAME.Text = rs!CourseName
txtDURATION.Text = rs!Duration
.MoveFirst
Wend
End With
End Sub
Private Sub cmdLast_Click()
With rs .Open "SELECT * FROM Course",
con, adOpenDynamic, adLockOptimistic
While Not .EOF
txtCID.Text = rs!CourseID
txtCNAME.Text = rs!CourseName
txtDURATION.Text = rs!Duration
.MoveLast
Wend
End With
End Sub
Private Sub cmdNext_Click()
With rs .Open "SELECT * FROM Course",
con, adOpenDynamic, adLockOptimistic
While Not .EOF
txtCID.Text = rs!CourseID
txtCNAME.Text = rs!CourseName
txtDURATION.Text = rs!Duration
.MoveNext
Wend
End With
End Sub
Private Sub cmdPrev_Click()
With rs .Open "SELECT * FROM Course",
con, adOpenDynamic, adLockOptimistic
While Not .EOF
txtCID.Text = rs!CourseID
txtCNAME.Text = rs!CourseName
txtDURATION.Text = rs!Duration
.MovePrevious
Wend
End With
End Sub
Private Sub Form_Load()
Call loadcon
MsgBox ("Connected")
End Sub
Components of frmStudentDetails
Form Student Record Details – Student Management
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 rs As New ADODB.Recordset
Dim constr As String
Private Sub btnCourse_Click()
Unload Me
frmCourse.Show
End Sub
Private Sub cmdData_Click()
Dim rsGrid As New ADODB.Recordset
con.CursorLocation = adUseClient
rsGrid.CursorLocation = adUseClient
rsGrid.CursorType = adOpenStatic
rsGrid.Open "SELECT * FROM Student_Records",
con, adOpenForwardOnly, adLockPessimistic
Set DataGrid1.DataSource = rsGrid
End Sub
Private Sub cmdDel_Click()
con.Execute "DELETE * FROM
STUDENT_RECORDS WHERE RollNo = " &
(Text1.Text) & ""
MsgBox ("Record Deleted Successfully")
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdFirst_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
con, adOpenDynamic, adLockOptimistic
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
Text5.Text = rs!Grade
.MoveFirst
.Close
End With
End Sub
Private Sub cmdLast_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
con, adOpenDynamic, adLockOptimistic
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
Text5.Text = rs!Grade
.MoveLast
End With
End Sub
Private Sub cmdNext_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
con, adOpenDynamic, adLockOptimistic
While Not .EOF
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
Text5.Text = rs!Grade
.MoveNext
Wend
.Close
End With
End Sub
Private Sub cmdPrev_Click()
With rs
.Open "SELECT * FROM STUDENT_RECORDS",
con, adOpenDynamic, adLockOptimistic
While Not .EOF
Text1.Text = rs!RollNo
Text2.Text = rs!FirstName
Text3.Text = rs!LastName
Text4.Text = rs!CourseID
Text5.Text = rs!Grade
.MovePrevious
Wend
End With
End Sub
Private Sub cmdSave_Click()
con.Execute "UPDATE STUDENT_RECORDS
SET FirstName = '" & Text2 & "',
LastName = '" & Text3 & "',
CourseID =" & Text4 & ",
Grade = '" & Text5 & "'
WHERE RollNo = " & Text1 & ""
MsgBox ("Record Updated")
End Sub
Private Sub Form_Load()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:UsersGirishDocumentsStudentDB.mdb;
Persist Security Info=False"
con.Open constr
End Sub
Private Sub cmdAdd_Click()
con.Execute "INSERT INTO
STUDENT_RECORDS(RollNo,FirstName,LastName,CourseID,Grade)
VALUES ('" + Text1.Text + "','" +
Text2.Text + "','" +
Text3.Text + "','" +
Text4.Text + "','" +
Text5.Text + "')"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
con.Close
End Sub
Code for Module1.Bas
The Module1.Bas is the code for connection to Microsoft Access Database. Since you may not want to define this connection on each form that is used in the application.
Public Sub loadcon()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:UsersGirishDocumentsStudentDB.mdb;
Persist Security Info=False"
con.Open constr
End Sub
Note :The C:\Users\Peter\Documents\StudentDB.mdb; is path to the Access database , you need to change is according to path of your Student Database.
When you establish connection with a database such as Microsoft Access or SQL through Visual basic 6 form. You must extract a set of records from one or more tables. These records are stored in a VB 6 object called a recordset. There are two ways to get recordset.
ADODC ( ActiveX Object Data Control )
ADODB ( ActiveX Object Data Base)
The next section I will discuss more about it.
ADODC vs ADODB
The ADODC provides a control that that creates a “connection string” and interface with the database. It skips the programming and helps to connect controls like textbox to connect to database directly.
Whereas ADODB is a class library with lot of methods and objects to connect to external databases. In other words, using methods and objects you can connect to any database and retrieve recordset.
To use the ADODC and ADODB, you must install following components under Project > Components > Controls Tab.
Add ADODC Control and Data Grid Control
Select two items – Microsoft ADO Data Control 6.0 and Microsoft Data Grid Control 6.0.
Click Apply and then click OK.
You will find that the VB Tool box has new sets of controls in the left hand side of VB 6.0 IDE. See image below.
ADODC and DataGrid Control Option in Toolbox
Recordset Connection Problem
Once you establish connection with the database and retrieve recordset , you can insert, delete, update or display data anyway you like. However, this will not work if your connection is broken or you want to work offline. As long as the database connection state is open, you can continue without problem.
Suppose you established connection with MS Access database and display your data in a data-grid control as shown in the following example.
Form Design
This form has two buttons and a data-grid control to display data. The show data button will retrieve data from the database table ‘student‘ and display in the data-grid control.The second button, disconnect will close the recordset and the connection to the database.
Form design to display data from Student table
Create MS Access 2003 Database with table ‘student’
The first step is to create a MS access database and create a simple table – students. Fill some random data for testing.
Student Table in MS Access
Code for Controls
Now you need to write appropriate codes for both the controls.
Show Data
Private Sub cmdShowData_Click()
Call myconnect
rs.CursorLocation = adUseClient
'open student table
rs.Open "student", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable
Set DataGrid1.DataSource = rs
End Sub
Disconnected
Private Sub cmdDisconnect_Click()
rs.Close
con.Close
End Sub
Code under ‘General’ to establish database connection
Option Explicit
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public constr As String
Public Sub myconnect()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBProjects\Temp\temp.mdb;Persist Security Info=False"
con.Open constr
MsgBox "Connected"
End Sub
Testing Our Program
When you run the program, you will find that clicking show data will populate the data-grid control successfully.
Student data is populated when you click Show Data button
However, as discussed earlier the recordset is available as long as the connection to the database is open. If you click the Disconnect button the data will disappear.
Clicking the Disconnect button will remove the data from grid
Therefore, the access to data set depends on the connection. We need data available even after the connection is closed or recordset is closed.
Persistent Recordset
The persistent recordset follow a very simple principle of saving your recordset data in a file, so that even if you break the connection with database it does not matter.
Now we rewrite the same program with persistent recordset.
Form Design
This form has two buttons and a data-grid control. The first button is Show Data and the second button is Save Data.
Form to show persistent student record
The save data button will run a query to the database and get the recordset. It will open a file and save the data immediately. The connection is closed.
The show data will not query the data but open the file and get the recordset. In this way, it does not matter whether the connection to the database is available or not, we can continue to work offline.
Database
There is not change in the database. You may use the same database and table which was used for the program earlier.
Code for controls
Save Data
Private Sub cmdSave_Click()
Call loadcon
rs.CursorLocation = adUseClient
rs.Open "student", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable
If Dir$("C:/VBProjects/Temp/mydata.xml") <> "" Then
Kill "C:/VBProjects/Temp/mydata.xml"
End If
rs.Save "C:/VBProjects/Temp/mydata.xml", adPersistXML
rs.Close
con.Close
End Sub
The above code will establish the connection to database which is MS Access 2003. It will store the entire table ‘student’ from the database to a recordset called rs.
adUseClient– it is the cursor location that change, navigate or update data. This cursor works on the client side.
con – name of the connection object.
adOpenKeyset -This is the cursor type when you access the recordset. This specify which records are visible or accessible, how efficient it is.
adLockBatchOptimistic– It is lock type required to edit the record. Optimistic batch update lock is not supported when cursor is adUseClient.
adCmdTable – It is a commandType that will internally generate a query that will fetch a table with all columns.
If Dir$("C:/VBProjects/Temp/mydata.xml") <> "" Then
Kill "C:/VBProjects/Temp/mydata.xml"
End If
The persistent recordset uses rs.save command to create a file and save the results. Each time it attempts to create a new file. It there is an exiting file, you get an error. The above command will check for existing file and delete it.
The above code will create a new file and save the recordset data. Now, the file is an XML file because we specified adPersistXML parameter which save the query into an XML file.
Recordset is stored in XML file
There is a default method to save the recordset in a DAT file using parameter – adPersistADTG
This file can be retrieved in the same manner as an XML file.
rs.close
con.close
The last part of code is very important as it closes all the connection. However, it does not concern us because the recordset is stored in the file.
Show Data
The show data button will open the recordset from previously saved file and display it to the data-grid.
Private Sub cmdShow_Click()
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "C:/VBProjects/Temp/mydata.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile
Set DataGrid1.DataSource = rs
End Sub
The recordset is opened from a file and the command type specified is adCmdFile.
Persistent data shown without a database connection
The above result is persistent because the database connection is already closed. Note that the code to establish a database connection will remain same as the previous program.
This article will help you to develop a library management system in Visual basic 6.0 with MS Access as database. To help you understand the project, we have included system diagram , er diagram, table design in MS Access and VB 6 form designs.
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.
frmLibraryManagement (Main Form)
frmLogin (Login Form)
frmBook (Book Form)
frmMember (Member Form)
frmBorrow (Book Borrow Form)
frmMenu (MDI Form)
Module1.bas (Module)
Project explorer showing all forms
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.
System Diagram -Library Management System using VB 6 with MS Access
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.
ER Diagram For Library Management Project
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.
MS Access Database Location – C:/VBProjects/Library/library.mdb
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.
Book Table – Library Management System
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”.
Member Table – Library Management System
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.
Borrow Table – Library Management System
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.
Relationships Between Tables – Library Management System
The relationship between Member and Borrow is called One-to-Many and the relationship between Book and Borrow is One-to-Many.
Relationship Types
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 rs As New ADODB.Recordset
Public constr As String
Public Sub loadcon()
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 Library Management System
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
Caption : Copyright : https://notesformsc.org
BackColor :Window Text
ForeColor : Highlight Text
Font : MS Sans-serif px Bold
Buttons
Name : cmdLogin
Caption : Login
BackColor : Button Highlight
Font : MS Sans-serif 12px Bold
Style :1-Graphical
Code for frmLibraryManagement
Private Sub cmdLogin_Click()
frmLogin.Show
Unload Me
End Sub
Components for frmLogin
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.
Login Dialog – Library Management System
Now we must rename and change the code for frmLogin.
Form Login – Library Management System
Form Details
Name : frmLogin
Caption :Login
BackColor: WindowText
ForeColor : Highlight Text
Labels
Name : lblUserID
Caption : UserID
BackColor : Window Text
ForeColor : Highlight Text
Name : lblPassword
Caption : Password
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
Code for frmLogin
Option Explicit
Public LoginSucceeded As Boolean
Private Sub cmdCancel_Click()
'set the global var to false
'to denote a failed login
LoginSucceeded = False
Me.Hide
End Sub
Private Sub cmdOK_Click()
rs.Open "SELECT * FROM Member ", con, adOpenDynamic, adLockReadOnly
While Not rs.EOF
If rs!MemberID = Val(txtUserID) Then
'check for correct password'
If rs!Pass = txtPassword Then
'place code to here to pass the '
'success to the calling sub'
'setting a global var is the easiest'
LoginSucceeded = True
frmMENU.Show
Me.Hide
con.Close
Exit Sub
Else
MsgBox "Invalid Password, try again!", , "Login"
txtPassword.SetFocus
SendKeys "{Home}+{End}"
End If
End If
rs.MoveNext
Wend
End Sub
Private Sub Form_Load()
Call loadcon
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.
Library Management – frmBook
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
Option Explicit
Private Sub cmdBAdd_Click()
Call loadcon
con.Execute "INSERT INTO Book (BookID, BookTitle, Author) VALUES (" & txtBookID & ",'" & txtBookTitle & "','" & txtAuthor & "')"
MsgBox ("Record Inserted Successfully")
con.Close
End Sub
Private Sub cmdBCatalog_Click()
Call loadcon
rs.CursorLocation = adUseClient
rs.Open "Book", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable
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.Save "C:/VBProjects/Library/savebookdisplay.xml", adPersistXML
rs.Close
con.Close
Set rs = Nothing
rs.Open "C:/VBProjects/Library/savebookdisplay.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile
Set BookFinder.DataSource = rs
Set rs = Nothing
End Sub
Private Sub cmdBExit_Click()
Unload Me
End Sub
Private Sub cmdBUpdate_Click()
Call loadcon
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.
Library Management – frmMember
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
Caption : &ADD
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()
Call loadcon
rs.CursorLocation = adUseClient
rs.Open "Member", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable
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.Save "C:/VBProjects/Library/savememberdisplay.xml", adPersistXML
rs.Close
con.Close
Set rs = Nothing
rs.Open "C:/VBProjects/Library/savememberdisplay.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile
Set MemberFinder.DataSource = rs
Set rs = Nothing
End Sub
Private Sub cmdMExit_Click()
Unload Me
End Sub
Private Sub cmdMUpdate_Click()
Call loadcon
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.
Library Management – frmBorrow
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()
Call loadcon
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
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()
Call loadcon
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()
Unload Me
If con.State = adStateOpen Then
con.Close
End If
End Sub
Private Sub cmdReturn_Click()
Call loadcon
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
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
The frmMENU is a form with menu items and we can create a menu using the Menu Editor. To create a MDI form, [Right-Click] Project1.vbp > Select Add > [Click] MDI Form. Create following menu items using Menu Editor.
MDI Form with Menu – Form Book Details – Library Management System using VB 6 with MS Access
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
Code for frmMenu
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.
Set MDI Child Property to True
Add following code to frmMENU in from the code editor.
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)
Unload Me
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).
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.
Customer and Bank employee can log into there account with user id and password.
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
SYSTEM MODEL
System Diagram -bank management system in vb 6.0 using ms access.
DATA MODEL
E-R Diagram
ER Diagram – System Diagram -bank management system in vb 6.0 using ms access.
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.
Branch Table – Bank Management SystemCustomer Table – Bank Management SystemTransaction Table – Bank Management SystemEmployee Table – Bank Management System
MODULES AND FORMS
The modules used in this application are given below
frmBankManagement
frmLogin
frmMENU(MDI Form)
frmBranch
frmCustomer
frmEmployee
frmTransaction
Module1.bas
Components of frmBankManagement
FORM -bank management system in vb 6.0 using ms access.
Labels:
Name : lblBankManagement
Caption : Bank Management System
Buttons:
Name : cmdLogin
Caption : LOGIN
Code for frmBankMangement
Private Sub cmdLogin_Click()
Unload Me
frmLogin.Show
End Sub
Components of frmLogin
Form Login – Bank Management System
Labels
Name : lblUserID
Caption : UserID
Name : lblPassword
Caption : Password
Text-boxes
Name : txtUserID
Text : 'leave blank'
Name : txtPassword
Text : Password
Buttons
Name : cmdLogin
Caption : Login
Name : cmdCancel
Caption : Cancel
Code for frmLogin
Option Explicit
Public LoginSucceeded As Boolean
Private Sub cmdCancel_Click()
'set the global var to false 'to denote a failed login
LoginSucceeded = False
Me.Hide
End Sub
Private Sub cmdLogin_Click()
rs.CursorLocation = adUseClient rec.CursorLocation
= adUseClient rec.Open "SELECT * FROM Employee", con,
adOpenDynamic, adLockPessimistic rs.Open
"SELECT * FROM Customer", con, adOpenDynamic, adLockOptimistic
'check the username
While Not rs.EOF
If txtUserID = rs!AccountNo And txtPassword = rs!Password Then
frmMENU.Show
LoginSucceeded = True
con.Close MsgBox ("Connection Closed")
Exit Sub
Unload Me
ElseIf
Val(txtUserID) = rec!EID And txtPassword
= rec!Password Then
frmMENU.Show LoginSucceeded = True
con.Close
MsgBox ("Connection Closed")
Exit Sub
Unload Me
ElseIf
Val(txtUserID) <> rs!AccountNo Then
rs.MoveNext ElseIf Val(txtUserID) <>
rec!EID Then rec.MoveNext
Else
MsgBox "Invalid Password, try again!", ,
"Login" txtPassword.SetFocus
SendKeys "{Home}+{End}"
Exit Sub
End If
Wend
rs.Close
rec.Close
End Sub
Private Sub Form_Load()
Call loadcon
MsgBox ("Connected")
End Sub
Components for frmBranch
Form Bank Branch – Bank Management Sys
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",
con, adOpenDynamic, adLockPessimistic
total = rs.Fields(0).Value
con.Execute "INSERT INTO
Branch(BID, Bcode, Location , TotalEmployees )
VALUES (" & txtBID & ",'" & txtBcode &
"','" & txtLocation & "', " & total & ")"
MsgBox ("Record Added")
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.CursorLocation = adUseClient
rs.Open "SELECT * FROM Branch",
con, adOpenDynamic, adLockPessimistic
Set DataGrid1.DataSource = rs
End Sub
Private Sub cmdExit_Click()
Unload Me
con.Close
End Sub
Private Sub cmdUpdate_Click()
Dim total As Double
rs.Open "SELECT Count(EID) FROM
Employee WHERE BID = " &
txtBID & " ", con,
adOpenDynamic, adLockPessimistic
total = rs.Fields(0).Value
con.Execute "UPDATE Branch SET Bcode
= '" & txtBcode & "',Location
= '" & txtLocation & " ',
TotalEmployees = " & total & "
WHERE BID = " & txtBID & " "
MsgBox ("Record Updated ")
End Sub
Private Sub Form_Load()
Call loadcon
End Sub
Components of frmCustomer
Form Customer Details – Bank Management System
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 = ""
MsgBox ("Record Added")
End Sub
Private Sub cmdClr_Click()
txtAno = ""
txtAname = ""
txtAddress = ""
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",
con, adOpenDynamic, adLockPessimistic
Set DataGrid1.DataSource = rs
End Sub
Private Sub cmdExit_Click()
Unload Me
con.Close
End Sub
Private Sub cmdUpdate_Click()
con.Execute "UPDATE Customer
SET AccountName = '" & txtAname & " ',
Address = ' " & txtAddress & " ',
Balance = " & txtBalance & "
WHERE AccountNo = " & txtAno & "" txtAno = ""
MsgBox ("Record Updated!")
End Sub
Private Sub Form_Load()
Call loadcon
MsgBox ("connected")
End Sub
Components of frmEmployees
Form Employee Details – Bank Management System
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 & ")"
MsgBox ("Record Added")
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",
con, adOpenDynamic, adLockPessimistic
Set DataGrid1.DataSource = rs
End Sub
Private Sub cmdExit_Click()
Unload Me
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
Private Sub Form_Load()
loadcon
End Sub
Components of frmTransaction
Form Transaction – Bank Management System
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 = ""
MsgBox ("Record Added")
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,
adOpenDynamic, adLockPessimistic
Set DataGrid1.DataSource = rs
End Sub
Private Sub cmdExit_Click()
Unload Me
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
Private Sub Form_Load()
Call loadcon
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’.
Set MDI Child to True
Under Project 1 , [Right Click] the Forms > [Click] Add > [Click] MDI Form.
Create MDI Form
Configure Menu using the Menu Editor in MDI Form and write appropriate code for the form.
Form Menu – Bank Management System
Code for frmMENU
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)
Unload Me
Unload frmBankManagement
Unload frmLogin
Unload frmCustomer
Unload frmEmployee
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 rs As ADODB.Recordset
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

In this article, we will learn to create an application in Visual Basic 6.0 for Employee Record Management in an organization.
The application does following tasks
Insert, Delete, Update employee records.
Compute the Gross Pay and Net Pay of each employee.
Maintain records of departments for each employee.
Each employee should be able to login to the system with own password.
The development of the application – Employees Record Management is done in following ways.
Develop System Model.
Define the Data Model (E-R Diagram)
Design the User Interface in VB 6.
Rename the components.
Write codes for components.
Test and verify Output.
System Model
System Model – Employee Record Management
E-R Diagram for Employee Record Management
E-R Diagram – Employee Record Management
Working with MS Access Database
Based on the E-R Model you can create a New Access Database and save it as the emp.mdb file. To create a blank database
Open Microsoft Office Access 2007 ( Check MS office documentation for other versions) > Click “Blank Database”.
Click Blank Database
Create three table for the emp.mdb database.
Dependent Table Schema
Dependent Table in Design View
Employee Table Schema
Employee Table in Design View
Login Table in Design View
Login Table in Design View
Note: Each Table should have at least one field as Primary Key.
Relationship between Tables
Relationship Between Tables
Accessing Access Database from Visual Basic 6
You can use a common module from visual basic to connect to the access database. Functions from this module will be called from all Forms in the visual basic project, so that we do not need to rewrite the same code for database connectivity.
Add the ADODB components in the VB Toolbox.
Add ADOBD component
Add an Adodb component, [Right Click] on the Control Object > Click Properties.
ADODB Component
Under the Adodc properties windows > Click Build.
ADODC Properties
Select the Provider
Under the Connection tab > Select the Database you created previously.
Select the Database
Now Click “Test Connection” to check if the database connection is successful. Click on the “Provider” tab again and we now have our connection string.
Connection String
This new Connection String will be used in Module.Bas file for accessing the database at run-time. The existing ADODC control is no longer required and you can delete it.
Creating Module1 for Database Connectivity
To create a new database connection at run-time, do the following. Under Project.vbp windows > Project1 [Right-Click] > Add [Select] > Module [Click].
A new module will open as shown in the following figure.
Create a New Module
Type the following code in the Module Code Editor.
Public con As New adodb.Connection
Public rs As New adodb.Recordset
Public constr As String
Public Sub loadcon()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Peter\Documents\emp.mdb;Persist Security Info=False"
con.Open constr
MsgBox ("connected")
End Sub
FORM EMPLOYEE DETAILS MANAGEMENT
Labels
Name: lblEMPMANAGE
Caption: EMPLOYEE RECORD MANAGEMENT
Buttons
Name: cmdLogin
Caption: Login
Code for frmEMPMANAGE
Private Sub cmdLogin_Click()
Unload Me
frmLogin.Show
End Sub
Components of frmLogin
Form Login
Labels
Name: lblUserID
Caption:UserID
Name: lblPassword
Caption: Password
Textboxes
Name: txtUserID
Text: 'leave blank'
Name: txtPassword
Text; 'leave blank'
Buttons
Name; vbOK
Caption: Login
Name: vbCancel
Caption: Cancel
Code for frmLogin
Option Explicit
Public LoginSucceeded As Boolean
Private Sub cmdCancel_Click()
'set the global var to false
'to denote a failed login
LoginSucceeded = False
Me.Hide
End Sub
Private Sub cmdOK_Click()
With
rs .Open "SELECT * FROM LOGIN", con, adOpenDynamic, adLockOptimistic
'check the username
.MoveFirst While Not .EOF
If txtUserID = !UserID And txtPassword = !Password Then
frmMenu.Show
LoginSucceeded = True
con.Close
MsgBox ("Connection Closed")
Exit Sub
Unload Me
ElseIf
txtUserID <> !UserID Then
.MoveNext
Else
MsgBox "Invalid Password, try again!", , "Login"
txtPassword.SetFocus SendKeys "{Home}+{End}"
Exit Sub
End If
Wend
.Close
End With
End Sub
Private Sub Form_Load()
Call loadcon
Unload frmEMPMANAGE
End Sub
Components for frmEmpDetails
Form Employee Details
Labels
Name: lblEmpID
Caption: EMPID
Name: lblFirstname
Caption: FirstName
Name: lblLastname
Caption: LastName
Name: lblHRA
Caption: HRA
TextBoxes
Name: txtEmpID
Text: 'leave blank'
Name: txtFirstname
Text: 'leave blank'
Name: txtLastname
Text: 'leave blank'
Name: txtHRA
Text: 'leave blank'
Name: txtDA
Text: 'leave blank'
Name: txtBasic
Text: 'leave blank'
Buttons
Name: cmdAdd
Caption: &ADD
Name: cmdDel
Caption: &DELETE
Name: cmdUpdate
Caption: &Update
Name: cmdClr
Caption: &Clear
Name: cmdExit
Caption: &Exit
Name: cmdFirstname
Caption: &FirstName
Name: cmdLastname
Capttion: &LastName
Name: cmdNext
Caption: Next Record
Name: cmdPrev
Caption: Previous Record
Code for frmEmpDetails
Private Sub cmdAdd_Click()
con.Execute "INSERT INTO emp(EmpID,FirstName,
LastName,Basic, HRA, DA)
VALUES (" & txtEmpID & ",'" &
txtFirstname & "','" &
txtLastname & "'," &
txtBasic & "," &
txtHRA & "," &
txtDA & " );"
MsgBox ("Record Inserted")
End Sub
Private Sub cmdCLR_Click()
txtEmpID = ""
txtFirstname = ""
txtLastname = ""
txtBasic = ""
txtHRA = ""
txtDA = ""
End Sub
Private Sub cmdDel_Click()
con.Execute "DELETE * FROM emp
WHERE EmpID = " &
txtEmpID & ""
MsgBox ("Record Deleted")
txtEmpID = ""
End Sub
Private Sub cmdExit_Click()
Unload Me
con.Close
End Sub
Private Sub cmdUpdate_Click()
con.Execute "UPDATE emp SET FirstName = '" &
txtFirstname & "', LastName = ' " &
txtLastname & " ' , Basic = " &
txtBasic & " , HRA = " &
txtHRA & " , DA = " &
txtDA & " WHERE EmpID = " &
txtEmpID.Text & ""
End Sub
Private Sub Form_Load()
Call loadcon
End Sub
Component for frmSalary
Form Salary – Employee Record Management
Labels
Name: lblCalculateS
Option: CALCULATE SALARY
Name: lblEmpID
Caption: ENTER EMPLOYEE ID
Name: lblGrossP
Caption: GROSS PAY
Name: lblNetP
Caption: NET PAY
Textboxes
Name: txtEmpID
Text: 'leave blank'
Name: txtGross
Text: 'leave blank'
Name: txtNet
Text: 'leave blank'
Buttons
Name : cmdGross
Caption: GROSS PAY
Name: cmdNet
Caption: NET PAY
Name: cmdExit
Caption: EXIT
Code for frmSalary
Private Sub cmdExit_Click()
Unload Me
con.Close
End Sub
Private Sub cmdGross_Click()
Dim gross As Double
Dim net As Double
Dim tax As Double
With
rs rs.Open "SELECT * FROM emp WHERE EmpID = " &
txtEmpID & "", con, adOpenDynamic, adLockPessimistic
gross = !Basic + !HRA + !DA
End With
txtGross.Text = gross
con.Execute "UPDATE emp SET GrossPay = ' " &
gross & " ' WHERE EmpID = " &
txtEmpID & ""
rs.Close
End Sub
Private Sub cmdNet_Click()
Dim gross As Double
Dim net As Double
Dim tax As Double
With rs
rs.Open "SELECT * FROM emp
WHERE EmpID = " &
txtEmpID & "",
con, adOpenDynamic, adLockPessimistic
gross = !Basic + !HRA + !DA
tax = gross * 10 / 100
net = gross - tax
End With
rs.Close
txtNet.Text = net
con.Execute "UPDATE emp
SET NetPay = ' " & net & " '
WHERE EmpID = " & txtEmpID & ""
End Sub
Private Sub Form_Load()
Call loadcon
End Sub
Components for frmDependent
Form Dependent – Employee Record Management System
Labels
Name: lblDID
Caption: Department ID
Name: lblDname
Caption: Department Name
Name: lblRelation
Caption: Relation
Name: lblDependent
Caption: Employee Dependent
Textboxes
Name: txtDID
Text: 'leave blank'
Name: txtDname
Text: 'leave blank'
Name: txtRel
Text: 'leave blank'
Name: txtEmpID
Text: 'leave blank'
Buttons
Name: cmdAdd
Caption: &ADD
Name: cmdDel
Caption: &DELETE
Name: cmdUpdate
Caption: &UPDATE
Name: cmdClr
Caption: &CLEAR
Caption: &EXIT
Name: cmdExit
Code for frmDependent
Private Sub cmdAdd_Click()
con.Execute "INSERT INTO
Dependent(DependentID, DependentName,Relation,EmpID )
VALUES (" & txtDID & ", '" &
txtDName & "','" &
txtRel & "'," &
txtEmpID & ");"
txtDID.Text = ""
txtDName.Text = ""
txtRel.Text = ""
txtEmpID = ""
con.Execute "DELETE * FROM Dependent
WHERE DependentID = " & txtDID & ""
End Sub
Private Sub cmdClr_Click()
txtDID.Text = ""
txtDName.Text = ""
txtRel.Text = ""
txtEmpID = ""
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdUpdate_Click()
con.Execute "UPDATE Dependent
SET DependentName = ' " &
txtDName & " ' , Relation = ' " &
txtRel & " ' WHERE DependentID = " &
txtDID & " "
MsgBox ("Record Updated")
End Sub
Private Sub Form_Load()
Call loadcon
End Sub
frmMenu [MDI Form]
MDI Form are Multiple-Document Interface in which you can open multiple forms. All our previously created will be child form of MDI.
This form is called frmMENU.
This form has menu editor to add different menu items and each menu item is linked to one of the forms created earlier. Before we get started with MDI form, you must set the MDI Child property of following Forms to ‘True’.
frmEmpDetails
frmSalary
frmDependent
Create a New MDI Form
To create a new MDI From, go to Project1.vbp > [Right Click] and then [Select ] Add > [Click] MDI Form.
Right Click the form and you will see the Menu Editor, using the editor create following menu items.
Caption : Employee Details
Name : employee
Index : 0
Caption : Salary Calculation
Name : salary
Index : 1
Caption : Dependents
Name : dependents
Index : 2
Caption : Exit
Name : exit
Index : 3
Code for frmMENU
The code for frmMENU is simple, you click on one of the menu item and a new form opens up, however the new form does not open in a separate windows, but it will open within frmMENU as MDI Child form.
Private Sub depend_Click(Index As Integer)
frmDependent.Show
End Sub
Private Sub empdetails_Click(Index As Integer)
frmEmpDetails.Show
End Sub
Private Sub exit_Click(Index As Integer)
Unload Me
Unload frmLogin
End Sub
Private Sub salary_Click(Index As Integer)
frmSalary.Show
End Sub
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.
Add Employee Record
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.
Figure 1 – Employee table in Design view
You table must have fields and corresponding data types. See the image below.
Figure 2 – Salary table in Design view
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.
Figure 3 -Create a One-To-One Relationship between Employee and Salary Table
Figure 4 – Enforce Referential Integrity
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.
Figure 5 – An Instance of Employee Relation
Figure 6 – Instance Of Salary Relation
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.
Figure 7 – Employee Details Form of Payroll Processing System
Name: cmdAdd
Caption: &ADD
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
Private Sub cmdAdd_Click()
con.Execute "INSERT INTO Employee (Eno, Ename,Age,Address, Dob, Phone) VALUES ('" & txtEno & "','" & txtEname & "','" & txtAge & "','" & txtAddress & "','" & txtDob & "', '" & txtPhone & "')"
MsgBox "Record Added"
End Sub
Private Sub cmdClear_Click()
txtEno.Text = " "
txtEname.Text = " "
txtAge.Text = " "
txtAddress.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()
Unload Me
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
Private Sub Form_Load()
Call loadcon
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.
Figure 8 – Salary Form Of Payroll Processing System
Name: cmdAdd
Caption: &ADD
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 & "')"
MsgBox "Record Added"
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()
Unload Me
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
'please note, if this file already exists it will be overwritten!
'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
Private Sub Form_Load()
Call loadcon
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.
Figure 9 – Main Form of Payroll Processing System
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()
Unload Me
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.
Figure 10 – Create A New Module from Project Explorer
The module code window will open up immediately. Insert the following code and recheck.
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public constr As String
Public Sub loadcon()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\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.
You always starts with Employee table and create employee records
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.
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.
Figure 11 – Test 1 – Database is Connected
Figure 12 – Test 2 – Employee Record Added
Figure 13 – Test 3 – Only enter Employee no, Designation, and Basic salary and click Calculate
The net salary and other details are automatically processed.
Figure 14 – Test 4 -Net Salary and Other Details 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.
Figure 15 – Test 5 – Record Added to 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.
Figure 16 – Test 6 – Print Output to Text File
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.
In this article, we will design a menu in VB 6. To create this program, you may require a windows computer with Windows XP or higher operating system and Visual Basic 6.0 installed.
Step 1: Create a New VB 6 form
Open visual basic 6 and choose “Standard EXE” and your default VB form will show up. The next step is to create a rectangle shape from the toolbar on the left hand side of form. Here are the properties of the form.
Form Name: Menu_Form
Caption: Menu Design
BackColor : Highlight Text
Width: 10515
Height: 6660
Create a rectangle shape with following properties. Do not change other properties of shape.
Name : Shape1
Caption: Shape1
Border Width: 5
Figure 1 – Form Menu with rectangular shape
Step 2: Create Menu For Color
In this section, we will create a new menu to select a color for the shape. Go to Tools > Menu Editor or right-click on the form and select Menu Editor.
Figure 2 – Color menu in editor
In the Menu Editor, you must enter the values for Caption, Name. You can add Shortcut if necessary. Select Next to add a new menu item and Delete to remove an item from the menu.
To add a sub menu, select the right arrow ( ->) or to move the sub menu, up and down arrow. Then you must add Caption, Name, and Shortcut for sub menu items.
The final menu look like the following.
Figure 3- Color menu with submenu items
Click each of the sub menu item and add following codes.
Private Sub blue_Click()
Shape1.FillColor = RGB(0, 0, 255)
End Sub
Private Sub green_Click()
Shape1.FillColor = RGB(0, 255, 0)
End Sub
Private Sub red_Click()
Shape1.FillColor = RGB(255, 0, 0)
End Sub
Step 3: Create Menu For Shapes
Now that you have created color menu, we must create Shape menu with three sub menu for Oval, Rectangle, and Square shape.
To create this menu, follow all steps mentioned in step 2. Your Shape menu should look like the following.
Figure 4-Shape menu in editor
Now the shape menu with three sub menu look like the following.
Figure 5 – Shape menu with sub items
Add following codes by clicking each of the sub menu items of shape menu.
Private Sub oval_Click()
Shape1.shape = 2
End Sub
Private Sub rectangle_Click()
Shape1.shape = 0
End Sub
Private Sub square_Click()
Shape1.shape = 1
End Sub
Step 4: Create Menu to Apply Colors
In this section, we create our last menu item that apply the color selected from the color menu.
Suppose you choose ‘Green‘ from color menu. It will not change the background color of the shape unless you choose the fill style from apply color menu.
Follow step 2 to create a new menu called Apply Color. The menu should have the following sub menu items.
Figure 6 – Apply colors menu in editor
The final menu for the form will look like the following. Look at the sub menu for Apply Color.
Figure 7 – Subitems for Apply Colors
Add the following codes for each sub menu by clicking on them.
Private Sub solid_Click()
Shape1.FillStyle = 1
End Sub
Private Sub transparent_Click()
Shape1.FillStyle = 0
End Sub
Testing the Project
Once you have saved your project. It is time to test it.
Color Menu = Choose the color for the shape. e.g Red, Blue, Green
Figure 8 – Select a color
Shape Menu = Choose the shape e.g Oval, Rectangle, or Square
Figure 9 – Select a shape
Apply Color = Apply the chosen color style. e.g Transparent, or Solid Fill.
Figure 10 – Apply solid fill of color green
The final step will apply solid fill of selected color green to the shape. Note that you can change the color, shape, or fill at any point in the program.
Visual Basic 6 allows us to add menu bar to the form. You can add your own menu bar to the form and write code to make it work.
In this article, we will add a menu bar to a VB 6 form.
To add a form, go to Tools > select Menu Editor
Open Menu Editor under Tools
Once you have clicked the Menu Editor, you will see the following Menu Editor window.
Menu Editor
Write a caption for your menu and a name, you can also select an index value which determines the order of the menu. Therefore, least the number, higher the order.
You must click Insert to add your menu item or you can delete an item.
There are four arrows at the bottom left side which indicate the position of the current menu item. You can move an item – up, down, left, and right. Moving up or down will change the order of the menu item. If you move an item right, it will become a sub-menu item for an item(File) above it.
Order of Menu items and Sub Menu items
From the above figure, you can see that the File is a top level menu item and Open/Close are sub-menu items. The open and close are move to right for making them as sub-menu item.
Output : Menu Bar
Output – Final Menu With Two Sub-menu items
The File menu will appear with two sub-menu items open and close as shown in above figure.
A VB 6 form has a title bar and the title of the form can be changed. In this article, we will talk about two ways to change the title of a form object.
Change title from property window.
Change title during run-time from code editor.
Change title using property window
When you select a VB 6 from in the IDE. You will notice that on left hand corner window , there is a small section called Property Window, which shows all the properties of the form object.
We can change the title and some other property of a form easily. This is most preferred way to things in VB 6.
Use Property window to change the Caption
In the above figure, we have used caption attribute to set the title for the form object. In the same way you can change Height and Width of the form, set a background color for the form and so on.
Form title changed to Salary
Changing title during run-time
You saw how we can change the title and other properties using property window for the form. Now, we will see another method of changing the title for the form – code editor.
You can write some code that will change the title and other attributes of a form.For example, consider the following code to change the title to “Finance”.
Private Sub Form_Load()
Me.Caption = "Finance"
Me.BackColor = RGB(255, 120, 0)
End Sub