querypay.frm

感觉还可以了 大家看看 指点下 有什么不足的地方,大家多多指教啊

VERSION 5.00
Begin VB.Form QueryPay 
   Caption         =   "查询"
   ClientHeight    =   3480
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   3675
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   ScaleHeight     =   3480
   ScaleWidth      =   3675
   StartUpPosition =   3  '窗口缺省
   Begin VB.Frame Frame1 
      Caption         =   "查询工资记录"
      Height          =   3135
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   3375
      Begin VB.CheckBox Check1 
         Caption         =   "不限定年月"
         Height          =   255
         Left            =   1920
         TabIndex        =   10
         Top             =   1920
         Width           =   1215
      End
      Begin VB.CommandButton Command2 
         Caption         =   "取消"
         Height          =   495
         Left            =   1800
         TabIndex        =   9
         Top             =   2400
         Width           =   1215
      End
      Begin VB.CommandButton Command1 
         Caption         =   "查询"
         Height          =   495
         Left            =   360
         TabIndex        =   8
         Top             =   2400
         Width           =   1215
      End
      Begin VB.ComboBox Combo2 
         Height          =   300
         Left            =   1920
         TabIndex        =   5
         Text            =   "Combo2"
         Top             =   1440
         Width           =   855
      End
      Begin VB.ComboBox Combo1 
         Height          =   300
         ItemData        =   "QueryPay.frx":0000
         Left            =   360
         List            =   "QueryPay.frx":0002
         TabIndex        =   4
         Text            =   "Combo1"
         Top             =   1425
         Width           =   1215
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Left            =   360
         TabIndex        =   1
         Text            =   "Text1"
         Top             =   720
         Width           =   2655
      End
      Begin VB.Label Label5 
         Caption         =   "月"
         Height          =   255
         Left            =   2880
         TabIndex        =   7
         Top             =   1470
         Width           =   255
      End
      Begin VB.Label Label4 
         Caption         =   "年"
         Height          =   255
         Left            =   1680
         TabIndex        =   6
         Top             =   1470
         Width           =   255
      End
      Begin VB.Label Label3 
         Caption         =   "工资月份"
         Height          =   255
         Left            =   360
         TabIndex        =   3
         Top             =   1200
         Width           =   1215
      End
      Begin VB.Label Label1 
         Caption         =   "职工ID"
         Height          =   255
         Left            =   360
         TabIndex        =   2
         Top             =   480
         Width           =   2175
      End
   End
End
Attribute VB_Name = "QueryPay"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub Check1_Click()
    If Check1.Value Then        '设置是否把日期作为搜索条件
        Combo1.Enabled = False  '工资年月控件不可用
        Combo2.Enabled = False
    Else
        Combo1.Enabled = True   '工资年月控件可用
        Combo2.Enabled = True
    End If
End Sub

Private Sub Command1_Click()
    Dim sql As String
    Dim rs As New ADODB.Recordset
    Dim paydate As String
    Dim FilterEmpty As Boolean
    Dim i As Integer
    Dim resultcount As Long
    Dim rsFilter As String
    
    paydate = ""
    FilterEmpty = True     '设置查询条件为"空"
    rsFilter = ""
    If Combo1.Enabled Then      '判断用户输入数据完整性和有效性
        If Combo1.ListIndex = -1 Then
            MsgBox "年份必须选择！", vbCritical
            Combo1.SetFocus
            Exit Sub
        End If
        If Combo2.ListIndex = -1 Then
            MsgBox "月份必须选择！", vbCritical
            Combo2.SetFocus
            Exit Sub
        End If
        paydate = Combo1.List(Combo1.ListIndex) & "-" & Combo2.List(Combo2.ListIndex)       '构造年月字符串
    End If
    If DbHandle.DbConnection Then
        If Text1.Text <> "" Then        '如果要求职工ID作为搜索条件
            sql = "TBL_USER"        '查询职工表中是否存在输入的职工记录
            rs.CursorType = adOpenDynamic
            rs.LockType = adLockOptimistic
            rs.Filter = "USER_ID='" & Text1.Text & "'"
            rs.Open sql, DbFinance
            If DbHandle.resultcount(rs) <> 1 Then       '不存在无法查询,要求用户输入正确的职工ID
                MsgBox "错误，不存在的职工ID号！", vbExclamation
                Text1.SetFocus
                rs.Close
                Set rs = Nothing
                DbHandle.DbClose
                Exit Sub
            End If
            rs.Close
        End If
        If paydate <> "" Then        '判断每个输入控件,如果用户改动就构造搜索条件字符串
            If FilterEmpty Then
                rsFilter = "PAY_DATE='" & paydate & "'"
            Else
                rsFilter = rsFilter & " AND PAY_DATE='" & paydate & "'"
            End If
            FilterEmpty = False
        End If
        If Text1.Text <> "" Then
            If FilterEmpty Then
                rsFilter = "PAY_USER='" & Text1.Text & "'"
            Else
                rsFilter = rsFilter & " AND PAY_USER='" & Text1.Text & "'"
            End If
            FilterEmpty = False
        End If
        rs.CursorType = adOpenDynamic
        rs.LockType = adLockOptimistic
        rs.Filter = ""
        sql = "SELECT PAY_USER,PAY_DATE,PAY_MONEY FROM TBL_PAY"
        If Not FilterEmpty Then     '构造完整的SQL语句(静态语句+动态条件)
            sql = sql & " WHERE " & rsFilter
        End If
        rs.Open sql, DbFinance


        resultcount = DbHandle.resultcount(rs)
        QueryPay2.Cls      '设置电子表格的列头
        QueryPay2.MSFlexGrid1.Cols = 3
        QueryPay2.MSFlexGrid1.Rows = resultcount + 1
        QueryPay2.MSFlexGrid1.Row = 0
        QueryPay2.MSFlexGrid1.Col = 0
        QueryPay2.MSFlexGrid1.Text = "员工ID"
        QueryPay2.MSFlexGrid1.Col = 1
        QueryPay2.MSFlexGrid1.Text = "工资年月"
        QueryPay2.MSFlexGrid1.Col = 2
        QueryPay2.MSFlexGrid1.Text = "工资金额"
        For i = 0 To 2      '平均分配每个列的宽
            QueryPay2.MSFlexGrid1.ColWidth(i) = QueryPay2.MSFlexGrid1.Width / 3 - 50
        Next i
        For i = 1 To resultcount    '循环将报销记录信息放入电子表格
            QueryPay2.MSFlexGrid1.Row = i
            QueryPay2.MSFlexGrid1.Col = 0
            QueryPay2.MSFlexGrid1.Text = rs("PAY_USER")
            QueryPay2.MSFlexGrid1.Col = 1
            QueryPay2.MSFlexGrid1.Text = rs("PAY_DATE")
            QueryPay2.MSFlexGrid1.Col = 2
            QueryPay2.MSFlexGrid1.Text = Trim(Str(rs("PAY_MONEY")))
            rs.MoveNext
        Next i
        QueryPay2.Show 1        '显示查询结构窗体
        rs.Close        '查询结束,释放资源
        Set rs = Nothing
        DbHandle.DbClose
        Unload Me
    Else        '数据库连接失败,退出
        MsgBox "数据库错误！", vbExclamation
        DbHandle.DbClose
        End
    End If
End Sub

Private Sub Command2_Click()
    Unload Me       '返回主窗体
End Sub

Private Sub Form_Load()
    Dim i As Long
    
    Me.Left = (Screen.Width - Me.ScaleWidth) / 2        '窗体居中显示
    Me.Top = (Screen.Height - Me.ScaleHeight) / 2
    For i = 2003 To 2030     '初始化窗体元素属性
        Combo1.AddItem Trim(Str(i))
    Next i
    For i = 1 To 12
        Combo2.AddItem Trim(Str(i))
    Next i
    Text1.Text = ""
    Combo1.Text = ""
    Combo2.Text = ""
End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    DbHandle.DbClose       '窗体关闭时关闭数据库连接
End Sub