Barangay Management System Development Part 8 – Purok Management Module

Barangay Management System Development Part 8 – Purok Management Module

Welcome to another tutorial on the development of barangay management system. We are now on the 8th part of our series but before we proceed, let us first have a recap on our accomplishments.

We have started the systems development of the barangay by identifying the requirements and then we have planned out the database structure of the project. The third part was the identification of project components that will be included, 4th part was the connectivity between our visual basic application and mysql database. After that we have designed the login form that connects to our database, menu structure was then created together with the main form and the previous tutorial was on the statistics of the project which was placed on the sidebar of the project.

Please download the resources from the previous topic. Let us now proceed with the tutorial proper. Open the project and follow the steps provided.

Step 1 – Create Cls_Purok

We will create a class module on purok, this class module contains the different functions of the purok management such as the add purok module, update purok module, etc.

Create the class module and save it as Cls_Purok.

Step 2 – Local variable

Add the following code on the very top of the class module

Dim mrs As New ADODB.Recordset
Dim msql As String

Step 3 – Code to add new purok record.

Public Sub AddPurok()
    Set mrs = New ADODB.Recordset
    mrs.CursorLocation = adUseClient
If mrs.State = adStateOpen Then mrs.Close
    msql = " Select * from tbl_purok Where purok_name ='" & EncodePurok.purokname.Text & "'"
    mrs.Open msql, conn, adOpenStatic, adLockOptimistic, adCmdText
If mrs.RecordCount >= 1 Then
    MsgBox "Duplicate Record.", vbCritical, ""
Exit Sub
End If
With mrs
    .AddNew
      !purokname = EncodePurok.purokname.Text
      !remarks = EncodePurok.remarks.Text
    .Update
End With
MsgBox "New entry successfully saved to the record.", vbInformation
End Sub

Step 4 – Code to delete purok record.

Sub DeletePurok(selected_id As Integer)
    Set mrs = New ADODB.Recordset
    mrs.CursorLocation = adUseServer
If mrs.State = adStateOpen Then mrs.Close
    msql = "Delete From tbl_purok Where purok_id =" & selected_id & ""
    mrs.Open msql, conn
    MsgBox "Record/s deleted."
End Sub

Step 5 – Code to update the purok record.

Public Sub UpdatePurok(selected_id As Integer, purok_name As String, remarks As String)
    Set mrs = New ADODB.Recordset
    mrs.CursorLocation = adUseServer
If mrs.State = adStateOpen Then mrs.Close
    msql = " Select * from tbl_purok Where purok_id=" & selected_id & ""
    mrs.Open msql, conn, adOpenStatic, adLockOptimistic
        mrs!purok_name = purok_name
        mrs!remarks = remarks
mrs.Update
    MsgBox "Record(s) Updated", vbInformation, ""
End Sub

Step 6 – Code to display the purok record.

Public Sub DisplayPurok(lstDay As ListView, SearchValue As String)
    Set mrs = New ADODB.Recordset
    mrs.CursorLocation = adUseServer
Dim lstItem As ListItem, a As Integer
If mrs.State = adStateOpen Then mrs.Close
    If SearchValue = "" Then
    msql = "SELECT * FROM tbl_purok order by purok_name"
Else
    msql = "SELECT * FROM tbl_purok WHERE tbl_purok.purok_name like '%" & SearchValue & "%' order by purok_name"
End If
    mrs.Open msql, conn, adOpenStatic, adLockOptimistic, adCmdText
    lstDay.ListItems.Clear
    Do While Not mrs.EOF
    a = a + 1
        Set lstItem = lstDay.ListItems.Add(, , a, 1, 1)
            lstItem.SubItems(1) = IIf(IsNull(mrs(0).Value), "", mrs(0).Value)
            lstItem.SubItems(2) = IIf(IsNull(mrs(1).Value), "", mrs(1).Value)
            lstItem.SubItems(3) = IIf(IsNull(mrs(2).Value), "", mrs(2).Value)
            mrs.MoveNext
    Loop
End Sub

Step 7 – Create ListPurok Form

Create a form, save and name it as List_Purok. The form contains the following controls:

  • Two imagelist
  • Two textbox
  • One listview
  • One toolbar menu

The purpose of this form is to show the list of purok that was saved and stored in the database. The toolbar menu serves as the control such as adding of new record, update and delete a record. The form has also a search feature that allows the user to search for the record. The form below is the output. You can also create and modify the form based on your preference.

Barangay Management System Development Part 8 Purok List
Barangay Management System Development Part 8 Purok Lis

Step 8 – Code for List_Purok form

The code below is the process of creating an object of the class.

Dim clsdata As New Cls_Purok

The code below will resize and maximize the width and height of listview based on the screen resolution.

Private Sub Form_Resize()
lstItem.Width = ScaleWidth - (lstItem.Left + 100)
lstItem.Height = ScaleHeight - (lstItem.Top + 100)
txtSearch.Width = ScaleWidth - (txtSearch.Left + 100)
End Sub

The code below will connect to our database, add icons to our menu and call the displaypurok method that will display the list of purok in the listview.

Private Sub Form_Load()
ConnServer
With tbrMenu
    .ImageList = lstMenu
    For i = 1 To lstMenu.ListImages.Count
        .Buttons(i).Image = i
        n = n + 2
    Next i
End With
clsdata.DisplayPurok lstItem, ""
End Sub

The code below is when the user double click the listview, the code will retrieve the information on the selected item on the listview and transfer the data into the Encode_Purok form.

Private Sub lstItem_DblClick()
    If lstItem.ListItems.Count > 0 Then
        Encode_Purok.id.Text = lstItem.SelectedItem.ListSubItems(1).Text
        Encode_Purok.purokname.Text = lstItem.SelectedItem.ListSubItems(2).Text
        Encode_Purok.remarks.Text = lstItem.SelectedItem.ListSubItems(3).Text
        Encode_Purok.cmdSave.Visible = False
        Encode_Purok.cmdUpdate.Visible = True
        Encode_Purok.Show 1
    Else
        MsgBox "No records found.", vbCritical, ""
    End If
End Sub

The code below is for the menu button of the form, the add button will open the form for encoding new items, the delete menu is to delete the record from the database based on the selected item in the listview, the refresh button is to display the list of purok, update menu is to call the code of lstItem_DblClick, and close menu is to close the form.

Private Sub tbrMenu_ButtonClick(ByVal Button As ComctlLib.Button)
Select Case Button.Key
Case "New":
    Encode_Purok.cmdUpdate.Visible = False
    Encode_Purok.cmdSave.Visible = True
    Encode_Purok.Show 1
Case "Delete":
    If txtSelected.Text = "" Then
        MsgBox "Please select item to delete.", vbCritical, ""
    Else
        If vbYes = MsgBox("Delete selected item?", vbQuestion + vbYesNo, "") Then
        clsdata.DeletePurok txtSelected
        clsdata.DisplayPurok lstItem, ""
        txtSelected.Text = ""
        End If
    End If
Case "Refresh":
    clsdata.DisplayPurok lstItem, ""
Case "Update":
    lstItem_DblClick
Case "Close":
    Unload Me
End Select
End Sub

The code below will sort the items based on the column click by the user.

Private Sub lstItem_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    If lstItem.Sorted And _
        ColumnHeader.Index - 1 = lstItem.SortKey Then
        lstItem.SortOrder = 1 - lstItem.SortOrder
    Else
        lstItem.SortOrder = lvwAscending
        lstItem.SortKey = ColumnHeader.Index - 1
    End If
    lstItem.Sorted = True
End Sub

The code below is to get the id of the selected item in the listview, the fetched id will serve as the basis for the delete and update function.

Private Sub lstItem_ItemClick(ByVal Item As MSComctlLib.ListItem)
txtSelected.Text = lstItem.SelectedItem.ListSubItems(1).Text
End Sub

The code below is for the search box of the form

Private Sub txtSearch_Change()
If Trim(txtSearch.Text) = "" Then
    clsdata.DisplayPurok lstItem, ""
    txtSelected.Text = ""
Else
    clsdata.DisplayPurok lstItem, txtSearch.Text
    txtSelected.Text = ""
End If
End Sub

Step 9 – Create form for encoding purok information

The form has the following controls:

  • Three textbox (1 hidden in the form)
  • Two labels
  • Two command buttons

The image below is the output of the form; again you are free to make changes to the form.

Barangay Management System Development Part 8 Purok Encoding
Barangay Management System Development Part 8 Purok Encoding

Step 10 – code for the encoding/updating form

The code below is what we call the process of creating an object of the class

Dim clsdata As New Cls_Purok

The code below will connect to our database

Private Sub Form_Load()
ConnServer
End Sub

The code below is the form validation function or method. The method will check if all of the required fields have been inputted by the user.

Function ValidateEntry() As Boolean
ValidateEntry = True
    If Trim(purokname.Text) = "" Then
        MsgBox "Fill-in Purok Name.", vbCritical, "Don't leave the field entry"
        purokname.SetFocus
        Exit Function
    End If
ValidateEntry = False
End Function

The code below will save the encoded data, close the form and show the list purok form.

Private Sub cmdSave_Click()
If ValidateEntry = False Then
    clsdata.AddPurok purokname, remarks
    clsdata.DisplayPurok List_Purok.lstItem, ""
    Unload Me
End If
End Sub

The code below is the update method of the purok.

Private Sub cmdUpdate_Click()
If ValidateEntry = False Then
    clsdata.UpdatePurok id.Text, purokname, remarks
    clsdata.DisplayPurok List_Purok.lstItem, ""
    Unload Me
End If
End Sub

Lastly, the code below is to close the form.

Private Sub cmdClose_Click()
Unload Me
End Sub

Congratulations you have successfully completed the part 7 of our tutorial. The full video tutorial will be uploaded in our youtube channel.

Download Source code

You may also visit the following articles related to the barangay information system.

Barangay Records Management Features and User Interface

City Wide Barangay Management System in PHP and MySQL

see you on the part 7 of this free tutorial.

iNetTutor.com

Comments

comments

, ,

Post navigation