frmtgyading.frm

本章示例使用的是Windows2000 Professional版的操作系统

               SubFormatType   =   0
            EndProperty
         EndProperty
         SplitCount      =   1
         BeginProperty Split0 
            BeginProperty Column00 
            EndProperty
            BeginProperty Column01 
            EndProperty
         EndProperty
      End
      Begin MSDataGridLib.DataGrid DataGrid2 
         Height          =   2415
         Index           =   1
         Left            =   240
         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 = "frmTgYaDing"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'rs_money用于最初显示时的记录集
Dim rs_money 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 cmdOrderY_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from YaJin 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 sum( 押金金额 ) as 总押金金额统计 from YaJin"
   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 cmdOrderD_Click()
   '排序只是对DataGrid1排序，对于用于统计的DataGrid2不排序
   If rs_order.State = adStateOpen Then
      rs_order.Close
   End If
   sqlod = "select * from DingJin 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 sum (定金金额) as 总定金金额统计 from DingJin"
   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 YaJin order by " & Combo1(0).Text
         If rs_money.State = adStateOpen Then
            rs_money.Close
         End If
         rs_money.CursorLocation = adUseClient
         rs_money.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_money
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据，只进行金额统计即可
         '下面的sql语句统计总押金数
         sqltg = "select sum( 押金金额 ) as 总押金金额统计 from YaJin"
         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 DingJin order by " & Combo1(1).Text
         If rs_money.State = adStateOpen Then
            rs_money.Close
         End If
         rs_money.CursorLocation = adUseClient
         rs_money.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_money
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据,只统计定金金额
         '下面的sql语句用于统计定金金额
         sqltg = "select sum (定金金额) as 总定金金额统计 from DingJin"
         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 YaJin order by " & Combo1(0).Text
         If rs_money.State = adStateOpen Then
            rs_money.Close
         End If
         rs_money.CursorLocation = adUseClient
         rs_money.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(0).DataSource = rs_money
         DataGrid1(0).Refresh
         '设置DataGrid2的统计数据，只进行金额统计即可
         '下面的sql语句统计总押金数
         sqltg = "select sum( 押金金额 ) as 总押金金额统计 from YaJin"
         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 DingJin order by " & Combo1(1).Text
         If rs_money.State = adStateOpen Then
            rs_money.Close
         End If
         rs_money.CursorLocation = adUseClient
         rs_money.Open sql, conn, adOpenStatic, adLockOptimistic
         '设置DataGrid1的数据源
         Set DataGrid1(1).DataSource = rs_money
         DataGrid1(1).Refresh
         '设置DataGrid2的统计数据,只统计定金金额
         '下面的sql语句用于统计定金金额
         sqltg = "select sum (定金金额) as 总定金金额统计 from DingJin"
         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_money.State = adStateOpen Then
      rs_money.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