frmtgclient.frm

本课题为实践课题

         EndProperty
         SplitCount      =   1
         BeginProperty Split0 
            BeginProperty Column00 
            EndProperty
            BeginProperty Column01 
            EndProperty
         EndProperty
      End
      Begin MSDataGridLib.DataGrid DataGrid2 
         Height          =   2415
         Index           =   1
         Left            =   -74760
         TabIndex        =   10
         Top             =   3720
         Width           =   3975
         _ExtentX        =   7011
         _ExtentY        =   4260
         _Version        =   393216
         HeadLines       =   1
         RowHeight       =   15
         BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ColumnCount     =   2
         BeginProperty Column00 
            DataField       =   ""
            Caption         =   ""
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column01 
            DataField       =   ""
            Caption         =   ""
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         SplitCount      =   1
         BeginProperty Split0 
            BeginProperty Column00 
            EndProperty
            BeginProperty Column01 
            EndProperty
         EndProperty
      End
   End
End
Attribute VB_Name = "frmTgClient"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'rs_client用于最初显示时的记录集
Dim rs_client As New ADODB.Recordset
'rs_order 为用于排序时的记录集
Dim rs_order As New ADODB.Recordset
'rs_tg 为用于统计时的记录集
Dim rs_tg As New ADODB.Recordset
'sql 为用于最初显示时的sql语句
Dim sql As String
'sqlod 为用于排序显示时的sql语句
Dim sqlod As String
'sqltg为用于统计显示时的sql语句
Dim sqltg As String

Private Sub cmdOrderC_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from Client 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 Client 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 cmdOrderGS_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from GongSi 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 GongSi 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 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 1
      '设置网格不可写
      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 Client order by " & Combo1(0).Text
         If rs_client.State = adStateOpen Then
            rs_client.Close
         End If
         rs_client.CursorLocation = adUseClient
         rs_client.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_client
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(0).text分组，并且统计同一个Combo1(0).text的数量
         sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 数量统计" & _
         " from Client 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 GongSi order by " & Combo1(1).Text
         If rs_client.State = adStateOpen Then
            rs_client.Close
         End If
         rs_client.CursorLocation = adUseClient
         rs_client.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_client
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(1).text分组，并且统计同一个Combo1(1).text的数量
         sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 数量统计 " & _
         " from GongSi 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 Select
End Sub

Private Sub SSTab1_Click(PreviousTab As Integer)
   Select Case SSTab1.Tab
      '家庭租户统计选项卡
      Case 0
         '先设置DataGrid1的数据
         sql = "select * from Client order by " & Combo1(0).Text
         If rs_client.State = adStateOpen Then
            rs_client.Close
         End If
         rs_client.CursorLocation = adUseClient
         rs_client.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_client
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(0).text分组，并且统计同一个Combo1(0).text的数量
         sqltg = "select " & Combo1(0).Text & ", count(" & Combo1(0).Text & ") as 数量统计" & _
         " from Client 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 GongSi order by " & Combo1(1).Text
         If rs_client.State = adStateOpen Then
            rs_client.Close
         End If
         rs_client.CursorLocation = adUseClient
         rs_client.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_client
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据
         '下面的sql语句已用户选择的Combo1(1).text分组，并且统计同一个Combo1(1).text的数量
         sqltg = "select " & Combo1(1).Text & ", count(" & Combo1(1).Text & ") as 数量统计 " & _
         " from GongSi 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 Select
End Sub


Private Sub Form_Unload(Cancel As Integer)
   If rs_client.State = adStateOpen Then
      rs_client.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