frmtghouse.frm

本课题为实践课题

'sqlod 为用于排序显示时的sql语句
Dim sqlod As String
'sqltg为用于统计显示时的sql语句
Dim sqltg As String

Private Sub cmdOrderAll_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from House 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
   sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 数量统计" & _
         " from House 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 cmdOrderYiZu_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from House where 目前状态= '已租' 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
   sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 数量统计 " & _
         " from House where 目前状态 = '已租' 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 cmdOrderWeiZu_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from House where 目前状态= '未租'or 目前状态= '预定' order by " & Combo1(2).Text
   rs_order.CursorLocation = adUseClient
   rs_order.Open sqlod, conn, adOpenStatic, adLockOptimistic
   '设置DataGrid1的数据源
   Set DataGrid1(2).DataSource = rs_order
   DataGrid1(2).Refresh
   sqltg = "select " & Combo1(2).Text & ", count(" & Combo1(2).Text & ") as 数量统计 " & _
         " from House where 目前状态 = '已租' or 目前状态 = '预定' 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 Form_Load()
   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 2
      '设置网格不可写
      DataGrid1(i).AllowAddNew = False
      DataGrid1(i).AllowDelete = False
      DataGrid1(i).AllowUpdate = False
      DataGrid2(i).AllowAddNew = False
      DataGrid2(i).AllowDelete = False
      DataGrid2(i).AllowUpdate = False
      Combo1(i).ListIndex = 0
      
   Next i
   Select Case SSTab1.Tab
      '所有房屋选项卡
      Case 0
         '先设置DataGrid1的数据
         sql = "select * from House order by " & Combo1(0).Text
         If rs_house.State = adStateOpen Then
            rs_house.Close
         End If
         rs_house.CursorLocation = adUseClient
         rs_house.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_house
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(0).text分组，并且统计同一个Combo1(0).text的数量
         sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 数量统计" & _
         " from House 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
         '先设置DataGrid1的数据
         sql = "select * from House where 目前状态 = '已租' order by " & Combo1(1).Text
         If rs_house.State = adStateOpen Then
            rs_house.Close
         End If
         rs_house.CursorLocation = adUseClient
         rs_house.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_house
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(1).text分组，并且统计同一个Combo1(1).text的数量
         sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 数量统计 " & _
         " from House where 目前状态 = '已租' 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
         '先设置DataGrid1的数据
         sql = "select * from House where 目前状态 = '未租' or 目前状态 = '预定' order by " & _
         Combo1(1).Text
         If rs_house.State = adStateOpen Then
            rs_house.Close
         End If
         rs_house.CursorLocation = adUseClient
         rs_house.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(2).DataSource = rs_house
         DataGrid1(2).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(2).text分组，并且统计同一个Combo1(2).text的数量
         sqltg = "select " & Combo1(2).Text & ", count(" & Combo1(2).Text & ") as 数量统计 " & _
         " from House where 目前状态 = '已租' or 目前状态 = '预定' 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 Select
End Sub

Private Sub Form_Unload(Cancel As Integer)

   If rs_house.State = adStateOpen Then
      rs_house.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 House order by " & Combo1(0).Text
         If rs_house.State = adStateOpen Then
            rs_house.Close
         End If
         rs_house.CursorLocation = adUseClient
         rs_house.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_house
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(0).text分组，并且统计同一个Combo1(0).text的数量
         sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 数量统计 " & _
         " from House 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
         '先设置DataGrid1的数据
         sql = "select * from House where 目前状态 = '已租' order by " & Combo1(1).Text
         If rs_house.State = adStateOpen Then
            rs_house.Close
         End If
         rs_house.CursorLocation = adUseClient
         rs_house.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_house
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(1).text分组，并且统计同一个Combo1(1).text的数量
         sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 数量统计 " & _
         " from House where 目前状态 = '已租' 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
         '先设置DataGrid1的数据
         sql = "select * from House where 目前状态 = '未租' or 目前状态 = '预定' order by " & _
         Combo1(1).Text
         If rs_house.State = adStateOpen Then
            rs_house.Close
         End If
         rs_house.CursorLocation = adUseClient
         rs_house.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(2).DataSource = rs_house
         DataGrid1(2).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(2).text分组，并且统计同一个Combo1(2).text的数量
         sqltg = "select " & Combo1(2).Text & ", count(" & Combo1(2).Text & ") as 数量统计 " & _
         " from House where 目前状态 = '已租' or 目前状态 = '预定' 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 Select
End Sub