form17.frm

小区物业管理系统完整文档及代码。帮助毕业同学做好毕业设计。

   
   '设置一个变量用于存储combo1中的排序方式
   Dim orderby As String
   Select Case Combo1(3).ListIndex
       Case 0
           orderby = " order by zh.业主姓名"
       Case 1
           orderby = " order by zh.物业地址"
       Case 2
           orderby = " order by sbzl.年份"
       Case 3
           orderby = " order by sbzl.月份"
       Case 4
           orderby = " order by sbzl.本月用量"
       Case 5
           orderby = " order by sbzl.本月费用"
       Case 6
           orderby = " order by dbzl.年份"
       Case 7
           orderby = " order by dbzl.月份"
       Case 8
           orderby = " order by dbzl.本月用量"
       Case 9
           orderby = " order by dbzl.本月费用"
       Case 10
           orderby = " order by qbzl.年份"
       Case 11
           orderby = " order by qbzl.月份"
       Case 12
           orderby = " order by qbzl.本月用量"
       Case 13
           orderby = " order by qbzl.本月费用"
   End Select
   sqlod = sql & orderby
   If rs_yb.State = adStateOpen Then
      rs_yb.Close
   End If
   rs_yb.CursorLocation = adUseClient
   Set rs_yb = ExecuteSQL(sqlod, MsgText)
   '设置DataGrid1的数据源
   Set DataGrid1(3).DataSource = rs_yb
   DataGrid1(3).Refresh
End Sub

Private Sub cmdOrderg_Click()
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from qbzl order by " & Combo1(2).Text
   rs_order.CursorLocation = adUseClient
   Set rs_order = ExecuteSQL(sqlod, MsgText)
   '设置DataGrid1的数据源
   Set DataGrid1(2).DataSource = rs_order
   DataGrid1(2).Refresh
   '使用分组统计，并显示在DataGrid2中
   '下面的sql语句已用户选择的Combo1(2).text分组、排序，并且统计同一个Combo1(2).text的数量
   sqltg = "select " & Combo1(2).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
   " from qbzl group by " & Combo1(2).Text & " order by " & Combo1(2).Text
   If rs_tg.State = adStateOpen Then
      rs_tg.Close
   End If
   rs_tg.CursorLocation = adUseClient
   Set rs_tg = ExecuteSQL(sqltg, MsgText)
   '设置DataGrid2的数据源
   Set DataGrid2(2).DataSource = rs_tg
   DataGrid2(2).Refresh
End Sub

Private Sub cmdOrderp_Click()
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from dbzl order by " & Combo1(1).Text
   rs_order.CursorLocation = adUseClient
   Set rs_order = ExecuteSQL(sqlod, MsgText)
   '设置DataGrid1的数据源
   Set DataGrid1(1).DataSource = rs_order
   DataGrid1(1).Refresh
   '使用分组统计，并显示在DataGrid2中
   '下面的sql语句已用户选择的Combo1(1).text分组、排序，并且统计同一个Combo1(1).text的数量
   sqltg = "select " & Combo1(1).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
   " from dbzl group by " & Combo1(1).Text & " order by " & Combo1(1).Text
   If rs_tg.State = adStateOpen Then
      rs_tg.Close
   End If
   rs_tg.CursorLocation = adUseClient
   Set rs_tg = ExecuteSQL(sqltg, MsgText)
   '设置DataGrid2的数据源
   Set DataGrid2(1).DataSource = rs_tg
   DataGrid2(1).Refresh
End Sub

Private Sub cmdOrderw_Click()
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from sbzl order by " & Combo1(0).Text
   rs_order.CursorLocation = adUseClient
   Set rs_order = ExecuteSQL(sqlod, MsgText)
   '设置DataGrid1的数据源
   Set DataGrid1(0).DataSource = rs_order
   DataGrid1(0).Refresh
   '使用分组统计，并显示在DataGrid2中
   '下面的sql语句已用户选择的Combo1(0).text分组、排序，并且统计同一个Combo1(0).text的数量
   sqltg = "select " & Combo1(0).Text & ",sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
   "from sbzl group by " & Combo1(0).Text & " order by " & Combo1(0).Text
   If rs_tg.State = adStateOpen Then
      rs_tg.Close
   End If
   rs_tg.CursorLocation = adUseClient
   Set rs_tg = ExecuteSQL(sqltg, MsgText)
   '设置DataGrid2的数据源
   Set DataGrid2(0).DataSource = rs_tg
   DataGrid2(0).Refresh
   
End Sub

Private Sub Form_activate()
   frmD2.Width = 9390
   frmD2.Height = 7170
   Dim x0 As Long
   Dim y0 As Long
   '让窗体居中
   x0 = Screen.Width
   y0 = Screen.Height
   x0 = (x0 - Me.Width) / 2
   y0 = (y0 - Me.Height) / 2
   Me.Move x0, y0
   '使用for循环设置属性，以减少代码
   For i = 0 To 3
      '设置网格不可写
      DataGrid1(i).AllowAddNew = False
      DataGrid1(i).AllowDelete = False
      DataGrid1(i).AllowUpdate = False
      If Not i = 3 Then
         DataGrid2(i).AllowAddNew = False
         DataGrid2(i).AllowDelete = False
         DataGrid2(i).AllowUpdate = False
      End If
      Combo1(i).ListIndex = 0
   Next i
   '各个选项卡分别显示数据
   Select Case SSTab1.Tab
      '水表选项卡
      Case 0
         '先设置DataGrid1的数据
         sql = "select * from sbzl order by " & Combo1(0).Text
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_yb
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(0).text分组、排序，并且统计同一个Combo1(0).text的数量
         sqltg = "select " & Combo1(0).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
         " from sbzl group by " & Combo1(0).Text & " order by " & Combo1(0).Text
         If rs_tg.State = adStateOpen Then
            rs_tg.Close
         End If
         rs_tg.CursorLocation = adUseClient
         Set rs_tg = ExecuteSQL(sqltg, MsgText)
         '设置DataGrid2的数据源
         Set DataGrid2(0).DataSource = rs_tg
         DataGrid2(0).Refresh
      '电表选项卡
      Case 1
         sql = "select * from dbzl order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_yb
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(1).text分组、排序，并且统计同一个Combo1(1).text的数量
         sqltg = "select " & Combo1(1).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
         " from dbzl group by " & Combo1(1).Text & " order by " & Combo1(1).Text
         If rs_tg.State = adStateOpen Then
            rs_tg.Close
         End If
         rs_tg.CursorLocation = adUseClient
         Set rs_tg = ExecuteSQL(sqltg, MsgText)
         '设置DataGrid2的数据源
         Set DataGrid2(1).DataSource = rs_tg
         DataGrid2(1).Refresh
      '气表选项卡
      Case 2
         sql = "select * from qbzl order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(2).DataSource = rs_yb
         DataGrid1(2).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(2).text分组、排序，并且统计同一个Combo1(2).text的数量
         sqltg = "select " & Combo1(2).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
         " from qbzl group by " & Combo1(2).Text & " order by " & Combo1(2).Text
         If rs_tg.State = adStateOpen Then
            rs_tg.Close
         End If
         rs_tg.CursorLocation = adUseClient
         Set rs_tg = ExecuteSQL(sqltg, MsgText)
         '设置DataGrid2的数据源
         Set DataGrid2(2).DataSource = rs_tg
         DataGrid2(2).Refresh
     '住户3表数据选项卡
     Case 3
         '下面的sql语句表示从水、电、气3表中取出住户姓名等于Client表中
         '住户姓名的年份、月份、本月用量、本月费用
         '并且按照住户姓名、年份、月份排序
         sql = "select zh.业主姓名, zh.物业地址, sbzl.年份 as 水表年份, " & _
         " sbzl.月份 as 水表月份,sbzl.本月用量 as 水本月用量,sbzl.本月费用 as 水本月费用," & _
         " dbzl.年份 as 电表年份,dbzl.月份 as 电表月份, dbzl.本月用量 as 电本月用量," & _
         " dbzl.本月费用 as 电本月费用,qbzl.年份 as 气表年份,qbzl.月份 as 气表月份, " & _
         " qbzl.本月用量 as 气本月用量,qbzl.本月费用 as 气本月费用 " & _
         " from sbzl,dbzl,qbzl,zh where zh.业主姓名 = sbzl.住户姓名 and " & _
         " zh.业主姓名 = dbzl.住户姓名 and zh.业主姓名 = qbzl.住户姓名 order by Client.业主姓名, sbzl.年份,sbzl.月份"
                                  
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(3).DataSource = rs_yb
         DataGrid1(3).Refresh
   End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
   If rs_yb.State = adStateOpen Then
   rs_yb.Close
   End If
   
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   If rs_tg.State = adStateOpen Then
      rs_tg.Close
   End If
End Sub


Private Sub SSTab1_Click(PreviousTab As Integer)
   Select Case SSTab1.Tab
      '水表选项卡
      Case 0
         '先设置DataGrid1的数据
         sql = "select * from sbzl order by " & Combo1(0).Text
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_yb
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(0).text分组、排序，并且统计同一个Combo1(0).text的数量
         sqltg = "select " & Combo1(0).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
         " from sbzl group by " & Combo1(0).Text & " order by " & Combo1(0).Text
         If rs_tg.State = adStateOpen Then
            rs_tg.Close
         End If
         rs_tg.CursorLocation = adUseClient
         Set rs_tg = ExecuteSQL(sqltg, MsgText)
         '设置DataGrid2的数据源
         Set DataGrid2(0).DataSource = rs_tg
         DataGrid2(0).Refresh
      '电表选项卡
      Case 1
         sql = "select * from dbzl order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_yb
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(1).text分组、排序，并且统计同一个Combo1(1).text的数量
         sqltg = "select " & Combo1(1).Text & ",sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
         " from dbzl group by " & Combo1(1).Text & " order by " & Combo1(1).Text
         If rs_tg.State = adStateOpen Then
            rs_tg.Close
         End If
         rs_tg.CursorLocation = adUseClient
         Set rs_tg = ExecuteSQL(sqltg, MsgText)
         '设置DataGrid2的数据源
         Set DataGrid2(1).DataSource = rs_tg
         DataGrid2(1).Refresh
      '气表选项卡
      Case 2
         sql = "select * from qbzl order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(2).DataSource = rs_yb
         DataGrid1(2).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(2).text分组、排序，并且统计同一个Combo1(2).text的数量
         sqltg = "select " & Combo1(2).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
         " from qbzl group by " & Combo1(2).Text & " order by " & Combo1(2).Text
         If rs_tg.State = adStateOpen Then
            rs_tg.Close
         End If
         rs_tg.CursorLocation = adUseClient
        Set rs_tg = ExecuteSQL(sqltg, MsgText)
         '设置DataGrid2的数据源
         Set DataGrid2(2).DataSource = rs_tg
         DataGrid2(2).Refresh
     '住户3表数据选项卡
     Case 3
         '下面的sql语句表示从水、电、气3表中取出住户姓名等于Client表中住户姓名的年份、月份、本月用量、本月费用
         '并且按照住户姓名、年份、月份排序
         sql = "select zh.业主姓名, zh.物业地址, sbzl.年份 as 水表年份, " & _
         " sbzl.月份 as 水表月份,sbzl.本月用量 as 水本月用量,sbzl.本月费用 as 水本月费用," & _
         " dbzl.年份 as 电表年份,dbzl.月份 as 电表月份, dbzl.本月用量 as 电本月用量," & _
         " dbzl.本月费用 as 电本月费用,qbzl.年份 as 气表年份,qbzl.月份 as 气表月份, " & _
         " qbzl.本月用量 as 气本月用量,qbzl.本月费用 as 气本月费用 " & _
         " from sbzl,dbzl,qbzl,zh where zh.业主姓名 = sbzl.住户姓名 and " & _
         " zh.业主姓名 = dbzl.住户姓名 and zh.业主姓名 = qbzl.住户姓名 order by zh.业主姓名, sbzl.年份,sbzl.月份"
         
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         Set rs_yb = ExecuteSQL(sql, MsgText)
         '设置DataGrid1的数据源
         Set DataGrid1(3).DataSource = rs_yb
         DataGrid1(3).Refresh
   End Select
End Sub