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

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. It also shows the number of hours deficit that will make them eligible.

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, when a student was absent, finally, the eligibility of a student to appear in the exam as mentioned earlier.

System Diagram for View Attendance
System Diagram for View Attendance

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.

Form Design View Attendance
Form Design View Attendance

Components for View Attendance

The components for view attendance interface are listed below.

Form Name: frmViewAttendance

BackColor: Windows Text
ForeColor : Highlight Text

Labels

Name: lblHead
Caption: View Attendance
BackColor: Windows Text
ForeColor: Highlight Text

Name : lblName
Caption : Name
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblStartDate
Caption : Start Date
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblEndDate
Caption : End Date
BackColor : Windows Text
ForeColor : Highlight Text

Name: lblDeficit
Caption: Deficit
BackColor: Windows Text
ForeColor : Highlight Text

Name : lblCurrentTotal
Caption : Current Total
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblEligible
Caption: Is the student eligible for exam?
BackColor : Windows Text
ForeColor : Highlight Text

Textboxes

Name : txtSname
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

Name : cmdClose
Caption : &Close

Name : DGViewAttendance

Code for View Attendance

Dim rs1 As New ADODB.Recordset
Dim total As Integer
Dim deficit As Integer


Private Sub cmdCLOSE_Click()

Unload frmMenu
Unload frmViewAttendance
Unload frmTodayAttendance

'closing connection

rs.Close
con.Close


End Sub

Private Sub cmdSearch_Click()

'making connection

Call loadcon
MsgBox ("Search operation will begin now...")

Dim Present As String

Present = "YES"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic

rs.Open "SELECT Student.[SID],Student.[Sname],Attendance.[Adate],Attendance.[APresent] FROM Attendance,Student WHERE 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 WHERE APresent = '" & Present & "' And ( Attendance.[Adate] Between  # " & Format(txtSdate.Text, "mm/dd/yyyy") & " # And  # " & Format(txtEdate.Text, "mm/dd/yyyy") & " # ); "), con, adOpenDynamic, adLockPessimistic

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 for teachers once they log in to mark daily attendance of their students. A substitute teacher can mark attendance of another teacher’s student.

System Diagram for Today’s Attendance

System Diagram For Today's Attendance
System Diagram For Today’s Attendance

The system lets you search for an input – TID and student ID, Section, Class is displayed. The date will always be today’s date. Then the teacher can either mark student present or absent and move to the next student of the class.

Form Design (frmTodayattendance)

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

Form Design Today Attendance
Form Design Today Attendance

Components for Today Attendance

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

Form Name : frmTodayAttendance

Caption: Today's Attendance
BackColor: Windows Text

Labels

Name : lblTAttendance
Caption: Today's Attendance
BackColor: Windows Text
ForeColor : Highlight Text

Name : lblSearch
Caption: Input Teacher ID First
BackColor: Windows Text
ForeColor : Highlight Text

Name : lblTSID
Caption : Student ID:
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblTTid
Caption : Teacher ID:
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblSection
Caption : Section:
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblClass
Caption : Class:
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblDate
Caption : Date:
BackColor : Windows Text
ForeColor : Highlight Text

Name : lblPresent
Caption : Present:
BackColor : Windows Text
ForeColor : Highlight Text

Textboxes

Name : txtSTid
Text : 'none'

Name : txtTodaySID

Text : ‘none’

Name : txtTodayTid

Text : ‘none’

Name : txtTodaySection

Text : ‘none’

Name : txtTodayClass

Text : ‘none’

Name : txtDate

Text : ‘none’

OptionButtons

Name : OpYES

Caption : YES

BackColor : Windows Text

ForeColor : Highlight Text

Name: OpNO

Caption: NO

BackColor: Windows Text

ForeColor : Highlight Text

Buttons

Name : cmdFirst

Caption : &First

Name : cmdLast

Caption : &Last

Name : cmdNext

Caption : &Next

Name : cmdPrevious

Caption : &Previous

Name : Clear

Caption : &Clear

Name: Close

Caption : &Close

Code for Today Attendance


Option Explicit
Public rs1       As ADODB.Recordset
Public conn     As ADODB.Connection
Public strSQL   As String
Dim tid_value As Integer

 
Public Sub CreateConnection()
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\VBDB\AttendanceDB.mdb;Persist Security Info=False"
End Sub
 
Public Sub OpenRecordset()
    Set rs1 = New ADODB.Recordset
    rs1.Open strSQL, conn, adOpenKeyset, adLockPessimistic
End Sub



Private Sub cmdCLOSE_Click()
Unload Me
Unload frmViewAttendance
rs1.Close
conn.Close


End Sub

Private Sub cmdCLR_Click()
txtTodaySID.Text = ""
txtTodayTid.Text = ""
txtDate.Text = ""
txtTodaySection.Text = ""
txtTodayClass.Text = ""
End Sub



Private Sub cmdFirst_Click()

     
 
   rs1.MoveFirst
   
      
    txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")
  
   
    
End Sub

Private Sub cmdLast_Click()


 rs1.MoveLast
       
    txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")

    
    
End Sub

Private Sub cmdNext_Click()

        
  If Not rs1.BOF And Not rs1.EOF Then
        
        rs1.MoveNext
        'CODE TO LOAD NEXT RECORD TO FORM HERE
               
    txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")
    
    OpYES.Enabled = True
    OpNO.Enabled = True
    OpYES.Value = False
    OpNO.Value = False
    
        
    ElseIf rs1.EOF = True Or rs1.BOF = True Then
    
        
        rs1.MoveFirst
        
        
    txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")
        'CODE TO LOAD RECORD TO SCREEN HERE
    End If


    
End Sub

Private Sub cmdPrev_Click()


If Not rs1.EOF Then
    
        rs1.MovePrevious
        
        'CODE TO LOAD NEXT RECORD TO FORM HERE
    txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")
    
    OpYES.Enabled = False
    OpNO.Enabled = False
    
    

    ElseIf rs1.EOF Then
    
        rs1.MoveLast
        
        'CODE TO LOAD RECORD TO SCREEN HERE
    txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")
    End If

End Sub






Private Sub OpNO_Click()
loadcon
Dim Curr_date As Date
Dim Absent As String

Absent = "NO"
Curr_date = Format(Now, "mm/dd/yyyy")

con.Execute ("INSERT INTO Attendance VALUES(" & _
"#" & Curr_date & "#," & _
"" & txtTodaySID & "," & _
"" & txtTodayTid & "," & _
"'" & Absent & "')")
 
    MsgBox ("Record Added")
    
    OpYES.Enabled = False
    OpNO.Enabled = False
    con.Close
    
End Sub

Private Sub OpYES_Click()

loadcon
Dim Curr_date As Date
Dim Present As String

Present = "YES"
Curr_date = Format(Now, "mm/dd/yyyy")

con.Execute ("INSERT INTO Attendance VALUES(" & _
"#" & Curr_date & "#," & _
"" & txtTodaySID & "," & _
"" & txtTodayTid & "," & _
"'" & Present & "')")
 
    MsgBox ("Record Added")
    
    OpYES.Enabled = False
    OpNO.Enabled = False
    con.Close
    
    
End Sub

Private Sub txtSTID_LostFocus()
 Set conn = New ADODB.Connection
    Set rs1 = New ADODB.Recordset
        
    CreateConnection
 
  tid_value = Val(txtSTID)
  
    strSQL = "SELECT * FROM Teaches WHERE TID = " & tid_value & " "
    conn.Execute strSQL
 
    OpenRecordset
 MsgBox ("Recordset open..")
 
    If rs1.BOF Then
        rs1.MoveFirst
        'CODE TO LOAD THE RECORD TO THE SCREEN HERE
        txtTodaySID.Text = rs1.Fields("SID").Value
    txtTodayTid.Text = rs1.Fields("TID").Value
    txtTodaySection.Text = rs1.Fields("Section").Value
    txtTodayClass.Text = rs1.Fields("Class").Value
        
    txtDate.Text = Format(Now, "mm/dd/yyyy")
    End If
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.

  • ADD
  • UPDATE
  • DELETE
  • CLEAR
  • CLOSE

Here is the system diagram.

System Diagram for Student Record Management
System Diagram for Student Record Management

Form Design (frmStudent)

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

Form Design Student Management
Form Design Student Management

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 Name: frmStudent

Caption: Student Record Management

Labels

Name: lblMain

Caption: Student Record Management

BackColor: Windows Text

ForeColor : Highlight Text

Name : lblSSID

Caption : Student ID

BackColor : Windows Text

ForeColor : Highlight Text

Name : lblSSname

Caption : Student Name

BackColor : Windows Text

ForeColor : Highlight Text

Name : lblSSem

Caption : Semester

BackColor : Windows Text

ForeColor : Highlight Text

Name: lblPasword

Caption: Password

BackColor: Windows Text

ForeColor : Highlight Text

Name : lblAID

Caption : Admin ID

BackColor : Windows Text

ForeColor : Highlight Text

Textboxes

Name : txtSID

Text : ‘none’

Name : txtSname

Text : ‘none’

Name : txtSem

Text : ‘none’

Name : txtPassword

Text : ‘none’

Name : txtAID

Text : ‘none’

Buttons

Name : cmdADD

Caption : &ADD

Name : cmdUpdate

Caption : &UPDATE

Name : cmdDelete

Caption : &DELETE

Name : cmdClear

Caption : &CLEAR

Name : cmdClose

Caption : &CLOSE

Code for Student Management System

Option Explicit

Private Sub cmdADD_Click()


con.Execute ("INSERT INTO Student VALUES(" & txtSID & ",'" & txtSname & " ','" & txtSem & " ' ,' " & txtPASS & " ', " & txtAid & " );")
 
 
    MsgBox ("Record Added")
    
    
End Sub

Private Sub cmdCLEAR_Click()
txtSname = " "
txtSID = " "
txtSem = " "
txtSpass = ""
txtAid = " "
End Sub

Private Sub cmdCLOSE_Click()
Unload frmStudent
Unload frmTeacherRecord

End Sub

Private Sub cmdDEL_Click()
con.Execute ("DELETE * FROM Attendance WHERE SID = " & txtSID & " ")
con.Execute ("DELETE * FROM Student WHERE SID = " & txtSID & " ")

MsgBox ("Record Deleted!")

End Sub

Private Sub cmdUPDATE_Click()
 
con.Execute "UPDATE Student SET Student.[Sname] = '" & txtSname & "', Student.[Semester] = '" & txtSem & "', Student.[Password] = '" & txtSpass & "' WHERE SID = " & txtSID & "; "

MsgBox ("Record Updated ")

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.

System Diagram for Teacher Record Management

System Diagram for Teacher Record Management
System Diagram for Teacher Record Management

Form Design (frmTeacherRecord)

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

Form Design Teacher Record
Form Design Teacher Record

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 Name : frmTeacherRecord

Caption: Teacher Record Management

Labels

Name: lblMain

Caption: Teacher Record Management

BackColor: Windows Text

ForeColor : Highlight Text

Name : lblTID

Caption : Teacher ID

BackColor : Windows Text

ForeColor : Highlight Text

Name : lblTNAME

Caption : Teacher Name

BackColor : Windows Text

ForeColor : Highlight Text

Name : lblCOURSE

Caption : Course

BackColor : Windows Text

ForeColor : Highlight Text

Name : lblPASS

Caption : Password

BackColor : Windows Text

ForeColor : Highlight Text

Name : lblAID

Caption : Admin ID

BackColor : Windows Text

ForeColor : Highlight Text

Textboxes

Name : txtTID

Text : ‘none’

Name : txtTNAME

Text : ‘none’

Name : txtCOURSE

Text : ‘none’

Name : txtTpass

Text : ‘none’

Name : txtAID

Text : ‘none’

Buttons

Name : cmdADD

Caption : &ADD

Name : cmdUPDATE

Caption : &UPDATE

Name : cmdDELETE

Caption : &DELETE

Name : cmdCLEAR

Caption : &CLEAR

Name : cmdCLOSE

Caption : &CLOSE

Code for Teacher Record Management

Private Sub cmdADD_Click()
con.Execute ("INSERT INTO Teacher VALUES(" & txtTID & ",'" & txtTNAME & " ','" & txtCOURSE & " ' ,' " & txtPASS & " ', " & txtAID & " );")
 
 
    MsgBox ("Record Added")
End Sub

Private Sub cmdCLOSE_Click()
Unload frmTeacherRecord
Unload frmStudent
End Sub

Private Sub cmdCLR_Click()

txtTID = " "
txtTNAME = " "
txtCOURSE = " "
txtTpass = " "
txtAID = " "

End Sub

Private Sub cmdDELETE_Click()

con.Execute ("DELETE * FROM Attendance WHERE TID = " & txtTID & " ")
con.Execute ("DELETE * FROM Teacher WHERE TID = " & txtTID & " ")

MsgBox ("Record Deleted!")

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

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.

(adsbygoogle = window.adsbygoogle || []).push({}); </script

Please support us by disabling your adblocker or whitelist this site from your adblocker. Thanks!

turn of adblocker imag

(adsbygoogle = window.adsbygoogle || []).push({});