Code for Generate Bill Form

Private Sub cmdadd_Click()

Dim rs As New ADODB.Recordset

rs.Open "bill", cn, adOpenKeyset, adLockOptimistic rs.AddNew

rs!bill_connection = bill_connection.Text rs!bill_month = bill_month.Text

rs!bill_total_units = bill_total_units.Text rs!bill_fix_charge = bill_fix_charge.Text

rs!bill_energy_charge = bill_energy_charge.Text rs!bill_electricity_duty = bill_electricity_duty.Text rs!bill_sur_charges = bill_sur_charges.Text

rs!bill_total = bill_total.Text

rs.Update

MsgBox "successfully Added", vbInformation Call frmclear

Dim rs As New ADODB.Recordset

rs.Open "select * from bill where bill_id =" & bill_id.Text & " ", cn, adOpenKeyset, adLockOptimistic

ans = MsgBox("Are u sure to delete the record", vbYesNo + vbInformation) If ans = 6 Then

Dim rs As New ADODB.Recordset Dim ID As String

ID = InputBox("Enter the ID", "")

rs.Open "select * from bill where bill_id =" & ID & " ", cn, adOpenKeyset, adLockOptimistic

If rs.EOF = True Then

MsgBox "Record Not Found", vbInformation, "Not Exit"

Exit Sub Else

bill_id.Text = rs!bill_id

bill_connection.Text = rs!bill_connection bill_month.Text = rs!bill_month

bill_total_units.Text = rs!bill_total_units bill_fix_charge.Text = rs!bill_fix_charge

bill_energy_charge.Text = rs!bill_energy_charge bill_electricity_duty.Text = rs!bill_electricity_duty bill_sur_charges.Text = rs!bill_sur_charges

bill_total.Text = rs!bill_total

rs.Open "select * from connection where bill_id =" & bill_id.Text & " ", cn, adOpenKeyset, adLockOptimistic

rs!bill_id = bill_id.Text

rs!bill_connection = bill_connection.Text rs!bill_month = bill_month.Text

rs!bill_total_units = bill_total_units.Text rs!bill_fix_charge = bill_fix_charge.Text

rs!bill_energy_charge = bill_energy_charge.Text rs!bill_electricity_duty = bill_electricity_duty.Text rs!bill_sur_charges = bill_sur_charges.Text

rs!bill_total = bill_total.Text

rs.Update

MsgBox "successfully Update", vbInformation If bill_id.Text = "" Then

MsgBox "Select the bill to pay."

Else

Set rs = New ADODB.Recordset

rs.Open "select * from bill_payment WHERE billpay_bill_id = " & billID & " ", cn, adOpenKeyset, adLockOptimistic

Call fillConnection Call fillMonth

If userLevel = "Customer" Then

billHeaderTitle.Caption = "Bill Details"

Frame1.Visible = False Else

Frame3.Visible = False End If

End Sub

Public Sub List_Fill()

itemListing.ListItems.Clear

Set rs = New ADODB.Recordset Dim o_ListItem As ListItem If userLevel = "Customer" Then

rs.Open "select * from bill, conection, customer WHERE bill_connection = connection_id AND connection_customer = customer_id and customer_id = " &

userLoginID & " ", cn, adOpenKeyset, adLockOptimistic Else

rs.Open "select * from bill", cn, adOpenKeyset, adLockOptimistic End If

Do While Not rs.EOF = True

Set o_ListItem = itemListing.ListItems.Add(, , rs!bill_id) o_ListItem.SubItems(1) = rs!bill_connection

o_ListItem.SubItems(2) = rs!bill_month o_ListItem.SubItems(3) = rs!bill_total_units o_ListItem.SubItems(4) = rs!bill_fix_charge o_ListItem.SubItems(5) = rs!bill_energy_charge o_ListItem.SubItems(6) = rs!bill_electricity_duty o_ListItem.SubItems(7) = rs!bill_sur_charges o_ListItem.SubItems(8) = rs!bill_total

rs.MoveNext Loop

rs.Close End Sub

Private Sub itemListing_ItemClick(ByVal Item As MSComctlLib.ListItem) bill_id.Text = itemListing.SelectedItem.Text

bill_connection.Text = itemListing.SelectedItem.SubItems(1) bill_month.Text = itemListing.SelectedItem.SubItems(2) bill_total_units.Text = itemListing.SelectedItem.SubItems(3) bill_fix_charge.Text = itemListing.SelectedItem.SubItems(4) bill_energy_charge.Text = itemListing.SelectedItem.SubItems(5) bill_electricity_duty.Text = itemListing.SelectedItem.SubItems(6) bill_sur_charges.Text = itemListing.SelectedItem.SubItems(7) bill_total.Text = itemListing.SelectedItem.SubItems(8)

billID = bill_id.Text End Sub

Public Function fillConnection()

Set rs = New ADODB.Recordset

rs.Open "select * from conection", cn, adOpenKeyset, adLockOptimistic Do While Not rs.EOF = True

bill_connection.AddItem rs!connection_id rs.MoveNext

Loop rs.Close End Function

Public Function fillMonth() bill_month.AddItem "January"

bill_month.AddItem "February"

bill_month.AddItem "March"

bill_month.AddItem "April"

bill_month.AddItem "May"

bill_month.AddItem "June"

bill_month.AddItem "July"

bill_month.AddItem "August"

bill_month.AddItem "September"

bill_month.AddItem "October"

bill_month.AddItem "November"

bill_month.AddItem "December"

End Function

Code for Generate Bill Form

Private Sub cmdadd_Click()

Dim rs As New ADODB.Recordset

rs.Open "bill", cn, adOpenKeyset, adLockOptimistic rs.AddNew

rs!bill_connection = bill_connection.Text rs!bill_month = bill_month.Text

rs!bill_total_units = bill_total_units.Text rs!bill_fix_charge = bill_fix_charge.Text

rs!bill_energy_charge = bill_energy_charge.Text rs!bill_electricity_duty = bill_electricity_duty.Text rs!bill_sur_charges = bill_sur_charges.Text

rs!bill_total = bill_total.Text

rs.Update

MsgBox "successfully Added", vbInformation Call frmclear

Dim rs As New ADODB.Recordset

rs.Open "select * from bill where bill_id =" & bill_id.Text & " ", cn, adOpenKeyset, adLockOptimistic

ans = MsgBox("Are u sure to delete the record", vbYesNo + vbInformation) If ans = 6 Then

Dim rs As New ADODB.Recordset Dim ID As String

ID = InputBox("Enter the ID", "")

rs.Open "select * from bill where bill_id =" & ID & " ", cn, adOpenKeyset, adLockOptimistic

If rs.EOF = True Then

MsgBox "Record Not Found", vbInformation, "Not Exit"

Exit Sub Else

bill_id.Text = rs!bill_id

bill_connection.Text = rs!bill_connection bill_month.Text = rs!bill_month

bill_total_units.Text = rs!bill_total_units bill_fix_charge.Text = rs!bill_fix_charge

bill_energy_charge.Text = rs!bill_energy_charge bill_electricity_duty.Text = rs!bill_electricity_duty bill_sur_charges.Text = rs!bill_sur_charges

bill_total.Text = rs!bill_total

rs.Open "select * from connection where bill_id =" & bill_id.Text & " ", cn, adOpenKeyset, adLockOptimistic

rs!bill_id = bill_id.Text

rs!bill_connection = bill_connection.Text rs!bill_month = bill_month.Text

rs!bill_total_units = bill_total_units.Text rs!bill_fix_charge = bill_fix_charge.Text

rs!bill_energy_charge = bill_energy_charge.Text rs!bill_electricity_duty = bill_electricity_duty.Text rs!bill_sur_charges = bill_sur_charges.Text

rs!bill_total = bill_total.Text

rs.Update

MsgBox "successfully Update", vbInformation If bill_id.Text = "" Then

MsgBox "Select the bill to pay."

Else

Set rs = New ADODB.Recordset

rs.Open "select * from bill_payment WHERE billpay_bill_id = " & billID & " ", cn, adOpenKeyset, adLockOptimistic

Call fillConnection Call fillMonth

If userLevel = "Customer" Then

billHeaderTitle.Caption = "Bill Details"

Frame1.Visible = False Else

Frame3.Visible = False End If

End Sub

Public Sub List_Fill()

itemListing.ListItems.Clear

Set rs = New ADODB.Recordset Dim o_ListItem As ListItem If userLevel = "Customer" Then

rs.Open "select * from bill, conection, customer WHERE bill_connection = connection_id AND connection_customer = customer_id and customer_id = " &

userLoginID & " ", cn, adOpenKeyset, adLockOptimistic Else

rs.Open "select * from bill", cn, adOpenKeyset, adLockOptimistic End If

Do While Not rs.EOF = True

Set o_ListItem = itemListing.ListItems.Add(, , rs!bill_id) o_ListItem.SubItems(1) = rs!bill_connection

o_ListItem.SubItems(2) = rs!bill_month o_ListItem.SubItems(3) = rs!bill_total_units o_ListItem.SubItems(4) = rs!bill_fix_charge o_ListItem.SubItems(5) = rs!bill_energy_charge o_ListItem.SubItems(6) = rs!bill_electricity_duty o_ListItem.SubItems(7) = rs!bill_sur_charges o_ListItem.SubItems(8) = rs!bill_total

rs.MoveNext Loop

rs.Close End Sub

Private Sub itemListing_ItemClick(ByVal Item As MSComctlLib.ListItem) bill_id.Text = itemListing.SelectedItem.Text

bill_connection.Text = itemListing.SelectedItem.SubItems(1) bill_month.Text = itemListing.SelectedItem.SubItems(2) bill_total_units.Text = itemListing.SelectedItem.SubItems(3) bill_fix_charge.Text = itemListing.SelectedItem.SubItems(4) bill_energy_charge.Text = itemListing.SelectedItem.SubItems(5) bill_electricity_duty.Text = itemListing.SelectedItem.SubItems(6) bill_sur_charges.Text = itemListing.SelectedItem.SubItems(7) bill_total.Text = itemListing.SelectedItem.SubItems(8)

billID = bill_id.Text End Sub

Public Function fillConnection()

Set rs = New ADODB.Recordset

rs.Open "select * from conection", cn, adOpenKeyset, adLockOptimistic Do While Not rs.EOF = True

bill_connection.AddItem rs!connection_id rs.MoveNext

Loop rs.Close End Function

Public Function fillMonth() bill_month.AddItem "January"

bill_month.AddItem "February"

bill_month.AddItem "March"

bill_month.AddItem "April"

bill_month.AddItem "May"

bill_month.AddItem "June"

bill_month.AddItem "July"

bill_month.AddItem "August"

bill_month.AddItem "September"

bill_month.AddItem "October"

bill_month.AddItem "November"

bill_month.AddItem "December"

End Function

In document Visual Basic and MS Access Project Report in Electricity Billing System (Page 78-88)