'sqlod 為用于排序顯示時的sql語句
Dim sqlod As String
'sqltg為用于統(tǒng)計顯示時的sql語句
Dim sqltg As String

Private Sub cmdOrderAll_Click()
   '排序只是對DataGrid1排序，對于用于統(tǒng)計的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
   '設(shè)置DataGrid1的數(shù)據(jù)源
   Set DataGrid1(0).DataSource = rs_order
   DataGrid1(0).Refresh
   sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 數(shù)量統(tǒng)計" & _
         " 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
   '設(shè)置DataGrid2的數(shù)據(jù)源
   Set DataGrid2(0).DataSource = rs_tg
   DataGrid2(0).Refresh
   
End Sub

Private Sub cmdOrderYiZu_Click()
   '排序只是對DataGrid1排序，對于用于統(tǒng)計的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from House where 目前狀態(tài)= '已租' order by " & Combo1(1).Text
   rs_order.CursorLocation = adUseClient
   rs_order.Open sqlod, conn, adOpenStatic, adLockOptimistic
   '設(shè)置DataGrid1的數(shù)據(jù)源
   Set DataGrid1(1).DataSource = rs_order
   DataGrid1(1).Refresh
   sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 數(shù)量統(tǒng)計 " & _
         " from House where 目前狀態(tài) = '已租' 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
   '設(shè)置DataGrid2的數(shù)據(jù)源
   Set DataGrid2(1).DataSource = rs_tg
   DataGrid2(1).Refresh
End Sub
Private Sub cmdOrderWeiZu_Click()
   '排序只是對DataGrid1排序，對于用于統(tǒng)計的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from House where 目前狀態(tài)= '未租'or 目前狀態(tài)= '預(yù)定' order by " & Combo1(2).Text
   rs_order.CursorLocation = adUseClient
   rs_order.Open sqlod, conn, adOpenStatic, adLockOptimistic
   '設(shè)置DataGrid1的數(shù)據(jù)源
   Set DataGrid1(2).DataSource = rs_order
   DataGrid1(2).Refresh
   sqltg = "select " & Combo1(2).Text & ", count(" & Combo1(2).Text & ") as 數(shù)量統(tǒng)計 " & _
         " from House where 目前狀態(tài) = '已租' or 目前狀態(tài) = '預(yù)定' 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
   '設(shè)置DataGrid2的數(shù)據(jù)源
   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循環(huán)設(shè)置屬性，以減少代碼
   For i = 0 To 2
      '設(shè)置網(wǎng)格不可寫
      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
         '先設(shè)置DataGrid1的數(shù)據(jù)
         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
         '設(shè)置DataGrid1的數(shù)據(jù)源
         Set DataGrid1(0).DataSource = rs_house
         DataGrid1(0).Refresh
         '設(shè)置DataGrid2的統(tǒng)計數(shù)據(jù)
         '下面的sql語句已用戶選擇的Combo1(0).text分組，并且統(tǒng)計同一個Combo1(0).text的數(shù)量
         sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 數(shù)量統(tǒng)計" & _
         " 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
         '設(shè)置DataGrid2的數(shù)據(jù)源
         Set DataGrid2(0).DataSource = rs_tg
         DataGrid2(0).Refresh
      Case 1
         '先設(shè)置DataGrid1的數(shù)據(jù)
         sql = "select * from House where 目前狀態(tài) = '已租' 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
         '設(shè)置DataGrid1的數(shù)據(jù)源
         Set DataGrid1(1).DataSource = rs_house
         DataGrid1(1).Refresh
         '設(shè)置DataGrid2的統(tǒng)計數(shù)據(jù)
         '下面的sql語句已用戶選擇的Combo1(1).text分組，并且統(tǒng)計同一個Combo1(1).text的數(shù)量
         sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 數(shù)量統(tǒng)計 " & _
         " from House where 目前狀態(tài) = '已租' 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
         '設(shè)置DataGrid2的數(shù)據(jù)源
         Set DataGrid2(1).DataSource = rs_tg
         DataGrid2(1).Refresh
      Case 2
         '先設(shè)置DataGrid1的數(shù)據(jù)
         sql = "select * from House where 目前狀態(tài) = '未租' or 目前狀態(tài) = '預(yù)定' 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
         '設(shè)置DataGrid1的數(shù)據(jù)源
         Set DataGrid1(2).DataSource = rs_house
         DataGrid1(2).Refresh
         '設(shè)置DataGrid2的統(tǒng)計數(shù)據(jù)
         '下面的sql語句已用戶選擇的Combo1(2).text分組，并且統(tǒng)計同一個Combo1(2).text的數(shù)量
         sqltg = "select " & Combo1(2).Text & ", count(" & Combo1(2).Text & ") as 數(shù)量統(tǒng)計 " & _
         " from House where 目前狀態(tài) = '已租' or 目前狀態(tài) = '預(yù)定' 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
         '設(shè)置DataGrid2的數(shù)據(jù)源
         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
      '物業(yè)收費(fèi)統(tǒng)計選項卡
      Case 0
         '先設(shè)置DataGrid1的數(shù)據(jù)
         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
         '設(shè)置DataGrid1的數(shù)據(jù)源
         Set DataGrid1(0).DataSource = rs_house
         DataGrid1(0).Refresh
         '設(shè)置DataGrid2的統(tǒng)計數(shù)據(jù)
         '下面的sql語句已用戶選擇的Combo1(0).text分組，并且統(tǒng)計同一個Combo1(0).text的數(shù)量
         sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 數(shù)量統(tǒng)計 " & _
         " 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
         '設(shè)置DataGrid2的數(shù)據(jù)源
         Set DataGrid2(0).DataSource = rs_tg
         DataGrid2(0).Refresh
      Case 1
         '先設(shè)置DataGrid1的數(shù)據(jù)
         sql = "select * from House where 目前狀態(tài) = '已租' 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
         '設(shè)置DataGrid1的數(shù)據(jù)源
         Set DataGrid1(1).DataSource = rs_house
         DataGrid1(1).Refresh
         '設(shè)置DataGrid2的統(tǒng)計數(shù)據(jù)
         '下面的sql語句已用戶選擇的Combo1(1).text分組，并且統(tǒng)計同一個Combo1(1).text的數(shù)量
         sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 數(shù)量統(tǒng)計 " & _
         " from House where 目前狀態(tài) = '已租' 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
         '設(shè)置DataGrid2的數(shù)據(jù)源
         Set DataGrid2(1).DataSource = rs_tg
         DataGrid2(1).Refresh
      Case 2
         '先設(shè)置DataGrid1的數(shù)據(jù)
         sql = "select * from House where 目前狀態(tài) = '未租' or 目前狀態(tài) = '預(yù)定' 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
         '設(shè)置DataGrid1的數(shù)據(jù)源
         Set DataGrid1(2).DataSource = rs_house
         DataGrid1(2).Refresh
         '設(shè)置DataGrid2的統(tǒng)計數(shù)據(jù)
         '下面的sql語句已用戶選擇的Combo1(2).text分組，并且統(tǒng)計同一個Combo1(2).text的數(shù)量
         sqltg = "select " & Combo1(2).Text & ", count(" & Combo1(2).Text & ") as 數(shù)量統(tǒng)計 " & _
         " from House where 目前狀態(tài) = '已租' or 目前狀態(tài) = '預(yù)定' 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
         '設(shè)置DataGrid2的數(shù)據(jù)源
         Set DataGrid2(2).DataSource = rs_tg
         DataGrid2(2).Refresh
   End Select
End Sub