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