Barangay Management System Development Part 7 – Sidebar and Statistics
Good day everyone, we are half way on our journey to design and develop an information system for barangay. This part of the tutorial will focus on the sidebar feature of the system where we will put the basic statistics of the system such as the population report per purok and the population grouped according to their gender.
You need to download first the resources from the previous tutorial which is the creation of main form and menu structure of our system.
Let us now proceed to the steps on how to establish our statistics feature of the project.
Step 1 – Add a picture box component in our main form.
Step 2 – Set the alignment into Align Left and appearance into Flat.
Step 3 – You are free to give colors and design to your sidebar, in our case we will keep it as simple as possible. The image below is the final design of our sidebar and statistics section.
Based on the image above, our sidebar will include the basic statistics such as the population of the barangay grouped by gender and the population of the barangay grouped by purok.
Step 4 – Next is to add a class module that will handle and store the codes on our statistics. Name and save our class module as Cls_Statistics.
Step 5 – We will create a function that will count the number of male residents.
Function CountTotalMale() As Integer If rs.State = adStateOpen Then rs.Close msql = " SELECT Count(tbl_residentinfo.gender) AS countMale" & _ " From tbl_residentinfo" & _ " Where tbl_residentinfo.gender=0" rs.Open msql, conn If IsNull(rs(0).Value) Then CountTotalMale = 0 Else CountTotalMale = rs(0).Value End If End Function
Step 6 – Go back to the main form and we will create an object of the class statistics, after that we will use the object to call the function that we have created.
Dim clsData As New Cls_Statistics Private Sub MDIForm_Load() lblmale.Caption = clsData.CountTotalMale End Sub
Step 7 – we will now create the function that will count the number of female. Go back to the Cls_Statistics and paste the code.
Function CountTotalFemale() As Integer If rs.State = adStateOpen Then rs.Close msql = " SELECT Count(tbl_residentinfo.gender) AS countFemale" & _ " From tbl_residentinfo" & _ " Where tbl_residentinfo.gender=1" rs.Open msql, conn If IsNull(rs(0).Value) Then CountTotalFemale = 0 Else CountTotalFemale = rs(0).Value End If End Function
Step 8 – call the function
Private Sub MDIForm_Load() lblmale.Caption = clsData.CountTotalMale lblfemale.Caption = clsData.CountTotalFemale End Sub
Step 10 – We will add the value of male and female to get the total population. Paste the code in the load event of the form
lbltotal.Caption = CInt(lblmale.Caption) + CInt(lblfemale.Caption)
Step 10 – We will now create a function that will count the population by purok.
Public Sub DisplayPopulationByPurok(lstDay As ListView) Set mrs = New ADODB.Recordset mrs.CursorLocation = adUseServer Dim lstItem As ListItem, a As Integer If mrs.State = adStateOpen Then mrs.Close msql = "SELECT tbl_purok.purok_name, Count(tbl_residentinfo.resident_id) AS CountOfID" & _ " FROM tbl_purok INNER JOIN tbl_residentinfo ON tbl_purok.purok_id = tbl_residentinfo.purok_id" & _ " GROUP BY tbl_purok.purok_name;" 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) mrs.MoveNext Loop End Sub
Step 11 – we will create a code that will sum up the columns of population in the list view. Copy the code below in the main form
Public Sub ComputeTotal() Dim a As Integer, b As Integer, stotal As Currency a = lstcount.ListItems.Count stotal = 0 For b = 1 To a stotal = Val(stotal) + CCur(lstcount.ListItems(b).SubItems(2)) Next lblcount.Caption = Format(stotal, "#") End Sub
Call the function in the load event of the form.
Private Sub MDIForm_Load() lblmale.Caption = clsData.CountTotalMale lblfemale.Caption = clsData.CountTotalFemale lbltotal.Caption = CInt(lblmale.Caption) + CInt(lblfemale.Caption) clsData.DisplayPopulationByPurok lstcount ComputeTotal End Sub Step 12 – the code below is the code for our refresh button. This will call all the functions that were executed in the load event of the form.
Private Sub cmdOk_Click() MDIForm_Load 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.
see you on the part 7 of this free tutorial.