frmtgyibiao.frm

本程序用VB+SQL 2000实现,可以实现小区的住户资料,仪表资料,住户投宿,住户报修等数据的查询,修改,删除,统计和以报表形式打印输出.

   '使用分组统计，并显示在DataGrid2中
   '下面的sql语句已用户选择的Combo1(2).text分组、排序，并且统计同一个Combo1(2).text的数量
   sqltg = "select " & Combo1(2).Text & ", sum(本月用量) as 总用量, sum(本月费用) as 总费用 " & _
   " from Gas 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
   rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
   '设置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 Power order by " & Combo1(1).Text
   rs_order.CursorLocation = adUseClient
   rs_order.Open sqlod, conn, adOpenStatic, adLockOptimistic
   '设置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 Power 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
   rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
   '设置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 Water order by " & Combo1(0).Text
   rs_order.CursorLocation = adUseClient
   rs_order.Open sqlod, conn, adOpenStatic, adLockOptimistic
   '设置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 Water 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
   rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
   '设置DataGrid2的数据源
   Set DataGrid2(0).DataSource = rs_tg
   DataGrid2(0).Refresh
   
End Sub

Private Sub Form_activate()
   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 Water order by " & Combo1(0).Text
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置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 Water 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
         rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid2的数据源
         Set DataGrid2(0).DataSource = rs_tg
         DataGrid2(0).Refresh
      '电表选项卡
      Case 1
         sql = "select * from Power order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置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 Power 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
         rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid2的数据源
         Set DataGrid2(1).DataSource = rs_tg
         DataGrid2(1).Refresh
      '气表选项卡
      Case 2
         sql = "select * from Gas order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置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 Gas 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
         rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid2的数据源
         Set DataGrid2(2).DataSource = rs_tg
         DataGrid2(2).Refresh
     '住户3表数据选项卡
     Case 3
         '下面的sql语句表示从水、电、气3表中取出住户姓名等于Client表中
         '住户姓名的年份、月份、本月用量、本月费用
         '并且按照住户姓名、年份、月份排序
         sql = "select Client.业主姓名, Client.物业地址, Water.年份 as 水表年份, " & _
         " Water.月份 as 水表月份,Water.本月用量 as 水本月用量,Water.本月费用 as 水本月费用," & _
         " Power.年份 as 电表年份,Power.月份 as 电表月份, Power.本月用量 as 电本月用量," & _
         " Power.本月费用 as 电本月费用,Gas.年份 as 气表年份,Gas.月份 as 气表月份, " & _
         " Gas.本月用量 as 气本月用量,Gas.本月费用 as 气本月费用 " & _
         " from Water,Power,Gas,Client where Client.业主姓名 = Water.住户姓名 and " & _
         " Client.业主姓名 = Power.住户姓名 and Client.业主姓名 = Gas.住户姓名 order by Client.业主姓名, Water.年份,Water.月份"
                                  
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(3).DataSource = rs_yb
         DataGrid1(3).Refresh
   End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
   
   rs_yb.Close
   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 Water order by " & Combo1(0).Text
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置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 Water 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
         rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid2的数据源
         Set DataGrid2(0).DataSource = rs_tg
         DataGrid2(0).Refresh
      '电表选项卡
      Case 1
         sql = "select * from Power order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置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 Power 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
         rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid2的数据源
         Set DataGrid2(1).DataSource = rs_tg
         DataGrid2(1).Refresh
      '气表选项卡
      Case 2
         sql = "select * from Gas order by 仪表编号"
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置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 Gas 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
         rs_tg.Open sqltg, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid2的数据源
         Set DataGrid2(2).DataSource = rs_tg
         DataGrid2(2).Refresh
     '住户3表数据选项卡
     Case 3
         '下面的sql语句表示从水、电、气3表中取出住户姓名等于Client表中住户姓名的年份、月份、本月用量、本月费用
         '并且按照住户姓名、年份、月份排序
         sql = "select Client.业主姓名, Client.物业地址, Water.年份 as 水表年份, " & _
         " Water.月份 as 水表月份,Water.本月用量 as 水本月用量,Water.本月费用 as 水本月费用," & _
         " Power.年份 as 电表年份,Power.月份 as 电表月份, Power.本月用量 as 电本月用量," & _
         " Power.本月费用 as 电本月费用,Gas.年份 as 气表年份,Gas.月份 as 气表月份, " & _
         " Gas.本月用量 as 气本月用量,Gas.本月费用 as 气本月费用 " & _
         " from Water,Power,Gas,Client where Client.业主姓名 = Water.住户姓名 and " & _
         " Client.业主姓名 = Power.住户姓名 and Client.业主姓名 = Gas.住户姓名 order by Client.业主姓名, Water.年份,Water.月份"
         
         If rs_yb.State = adStateOpen Then
            rs_yb.Close
         End If
         rs_yb.CursorLocation = adUseClient
         rs_yb.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(3).DataSource = rs_yb
         DataGrid1(3).Refresh
   End Select
End Sub


Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)

End Sub