static.frm

一个使用vb编写的人事管理系统

            TabIndex        =   8
            Top             =   720
            Width           =   930
         End
      End
      Begin VB.Frame Frame1 
         Caption         =   "按性别统计"
         Height          =   855
         Left            =   120
         TabIndex        =   2
         Top             =   600
         Width           =   5535
         Begin VB.TextBox Text2 
            Height          =   375
            Left            =   3960
            TabIndex        =   6
            Text            =   "Text2"
            Top             =   360
            Width           =   975
         End
         Begin VB.TextBox Text1 
            Height          =   375
            Left            =   1320
            TabIndex        =   4
            Text            =   "Text1"
            Top             =   360
            Width           =   975
         End
         Begin VB.Label Label2 
            AutoSize        =   -1  'True
            Caption         =   "女职工："
            Height          =   210
            Left            =   2880
            TabIndex        =   5
            Top             =   480
            Width           =   915
         End
         Begin VB.Label Label1 
            AutoSize        =   -1  'True
            Caption         =   "男职工："
            Height          =   210
            Left            =   360
            TabIndex        =   3
            Top             =   480
            Width           =   915
         End
      End
      Begin VB.Label Label11 
         AutoSize        =   -1  'True
         Caption         =   "统计结果："
         Height          =   210
         Left            =   -74760
         TabIndex        =   30
         Top             =   3720
         Width           =   1140
      End
   End
End
Attribute VB_Name = "Staticfrm"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub Command1_Click()
    '============================================
    '单击“添加”按钮，将设置的条件写入列表框中。
    '如果选取的查询字段为日期型，在数据两侧加“#”；
    '如果选择的查询字段为字符型，在数据两侧加引号；
    '如果选择的查询字段为数值型，不对数据特别处理。
    '============================================
    
    
    Dim SQLS As String
    Dim And_Or As String
    
    And_Or = "     "
    
    If Combo1.Text = "工作时间" Then          '日期型字段
        SQLS = Combo1.Text & " " & Combo2.Text & " #" & Trim(Text9.Text) & "# "
    ElseIf Combo1.Text = "职工年龄" Or Combo1.Text = "基本工资" Then  '数值型字段
        SQLS = Combo1.Text & " " & Combo2.Text & Trim(Text9.Text)
    Else     '字符型字段
       SQLS = Combo1.Text & " " & Combo2.Text & "'" & Trim(Text9.Text) & "'"
    End If
    
    '如果列表框中已有内容，添加的条件字符串前应有“and”或“or”
    If List1.ListCount > 0 Then
        If Option1.Value Then And_Or = " and "
        If Option2.Value Then And_Or = " OR  "
    End If
    
    List1.AddItem And_Or & SQLS
    
    Option1.Visible = True
    Option2.Visible = True
    Option1.Value = True

End Sub

Private Sub Command2_Click()
    '============================================
    '单击“删除”按钮，删除选定行。
    '如果列表列表框中还有内容，取消第一行的“and”或“or”字符；
    '============================================
    
    If List1.ListIndex < 0 Then
      '没有选定被删除项
      MsgBox "请先选择被删除项", , "删除查询条件"
      Exit Sub
    Else
      '删除选定项目
      List1.RemoveItem List1.ListIndex
      
      '如果列表框中还有项目，使第一项没有“and”/“or”
      If List1.ListCount > 0 Then
         List1.List(0) = Space(5) + Mid(List1.List(0), 6)
         List1.ListIndex = 0
      End If
      
    End If
End Sub

Private Sub Command3_Click()
    '单击“清除”按钮，清除列表框中所有内容
    
    List1.Clear
End Sub

Private Sub Command4_Click()
    '===============================================
    '单击“统计”按钮，获取查找条件字符串；
    '显示统计结果。
    '===============================================
    On Error Resume Next
    Dim FindStr As String         'SELECT子句
    Dim I As Integer
    
    FindStr = ""
    
    '获取列表框中所有条件
    For I = 0 To List1.ListCount - 1
      FindStr = FindStr & List1.List(I)
    Next I
      
    FindStr = LTrim(FindStr)
    
    '如果列表框中无内容，显示错误提示并退出
    If FindStr = "" Then
        MsgBox "请向列表框中添加查询条件！", , "查询条件设置错误"
        Exit Sub
    End If
    
    '设置查询条件，返回查询记录集，并将此记录集设置为网格控件的DataSource。
    FindStr = "select * from emp where " & FindStr
    Set ADORst = New Recordset
    ADORst.Open FindStr, DB, adOpenStatic, adLockOptimistic
    Text10.Text = ADORst.RecordCount
    Text10.Locked = True
End Sub

Private Sub Command5_Click()
    '单击“退出”按钮，断开与数据库的连接，退出程序
    Unload Me
End Sub

Private Sub Form_Load()
    '===============================================
    '设置与数据库的连接，返回包含表中所有记录的记录集；
    '设置网格控件的DataSource，使之显示记录集中的信息。
    '预置组合框中的内容，将表中的所有字段添加到Combo1中；
    '将比较运算符添加到组合框Combo2中，并进行其他初始化设置。
    '===============================================
    Dim I As Integer
    
    
    '向组合框Combl1中添加记录集的所有字段名称
    For I = 0 To ADORst.Fields.Count - 1
      Combo1.AddItem (ADORst.Fields(I).Name)
    Next I
    
    '向组合框Combl2中添加比较运算符
    Combo2.AddItem "="
    Combo2.AddItem ">"
    Combo2.AddItem ">="
    Combo2.AddItem "<"
    Combo2.AddItem "<="
    Combo2.AddItem "<>"
    Combo2.AddItem "Like"
    
    '控件外观初始化设置
    Combo2.Text = "="
    Combo1.Text = "职工编号"
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    Text5.Text = ""
    Text6.Text = ""
    Text7.Text = ""
    Text8.Text = ""
    Text9.Text = ""
    Text10.Text = ""
    
    '设置单选纽不可见
    Option1.Visible = False
    Option2.Visible = False
    
    Call SimpleTJ
End Sub

Sub SimpleTJ()
  Dim Sex_M As Integer, Sex_F As Integer
  Dim Age_35 As Integer, Age_45 As Integer
  Dim Age_55 As Integer, Age_25 As Integer
  Dim AGE_65 As Integer, Age_Ave As Single
  Dim Total_Num As Integer, Total_age As Long
  Dim Avg_Age As Single
    
    Set ADORst = New Recordset
    ADORst.Open "select * from emp", DB, adOpenStatic, adLockOptimistic

  ADORst.MoveFirst
  Do While Not ADORst.EOF
    
    '按性别统计人数
    If ADORst.Fields("职工性别") = "男" Then
       Sex_M = Sex_M + 1
    Else
      Sex_F = Sex_F + 1
    End If
    
    '按年龄分布统计人数
    If ADORst.Fields("职工年龄") <= 25 Then
       Age_25 = Age_25 + 1
    ElseIf ADORst.Fields("职工年龄") <= 35 Then
       Age_35 = Age_35 + 1
    ElseIf ADORst.Fields("职工年龄") <= 45 Then
       Age_45 = Age_45 + 1
    ElseIf ADORst.Fields("职工年龄") <= 55 Then
       Age_55 = Age_55 + 1
    Else
       AGE_65 = AGE_65 + 1
    End If
       
    Total_Num = Total_Num + 1
    Total_age = Total_age + ADORst.Fields("职工年龄")
    ADORst.MoveNext
 Loop
  
 '显示统计结果
  Text1.Text = Sex_M
  Text2.Text = Sex_F
  Text3.Text = Age_25
  Text4.Text = Age_35
  Text5.Text = Age_45
  Text6.Text = Age_55
  Text7.Text = AGE_65
  Text8.Text = Total_age / Total_Num
End Sub

Private Sub SSTab1_Click(PreviousTab As Integer)
   Call SimpleTJ
End Sub