frmqujiangl.frm

由visual basic编写的完整的基于企业管理的

   Begin VB.Data Data1 
      Caption         =   "Data1"
      Connect         =   "Access"
      DatabaseName    =   ""
      DefaultCursorType=   0  'DefaultCursor
      DefaultType     =   2  'UseODBC
      Exclusive       =   0   'False
      Height          =   375
      Left            =   2280
      Options         =   0
      ReadOnly        =   0   'False
      RecordsetType   =   1  'Dynaset
      RecordSource    =   ""
      Top             =   4080
      Visible         =   0   'False
      Width           =   2775
   End
   Begin MSFlexGridLib.MSFlexGrid mskGrid 
      Bindings        =   "frmQuJiangL.frx":06BE
      Height          =   3555
      Left            =   30
      TabIndex        =   0
      Top             =   2820
      Width           =   9375
      _ExtentX        =   16536
      _ExtentY        =   6271
      _Version        =   327680
      FixedCols       =   0
      ForeColor       =   8388608
      ForeColorFixed  =   0
      BackColorSel    =   16711680
      AllowUserResizing=   1
   End
   Begin Threed.SSPanel SSPanel1 
      Height          =   2685
      Left            =   8340
      TabIndex        =   24
      Top             =   60
      Width           =   1035
      _ExtentX        =   1826
      _ExtentY        =   4736
      _Version        =   131073
      BevelInner      =   1
      Begin VB.CommandButton cmdExit 
         Caption         =   "退出"
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   405
         Left            =   150
         TabIndex        =   28
         Top             =   2040
         Width           =   735
      End
      Begin VB.CommandButton cmdPrint 
         Caption         =   "打印"
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   405
         Left            =   150
         TabIndex        =   27
         Top             =   1410
         Width           =   735
      End
      Begin VB.CommandButton cmdInit 
         Caption         =   "重置"
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   405
         Left            =   150
         TabIndex        =   26
         Top             =   780
         Width           =   735
      End
      Begin VB.CommandButton cmdQuery 
         Caption         =   "查询"
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   405
         Left            =   150
         TabIndex        =   25
         Top             =   150
         Width           =   735
      End
   End
End
Attribute VB_Name = "frmQuJiangL"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim rec As Recordset

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdInit_Click()
Dim I As Integer
For I = 0 To ShiJF.Count - 1
  ShiJF(I) = "__/__/____"
Next I
For I = 0 To ShiJT.Count - 1
  ShiJT(I) = "__/__/____"
Next I
For I = 0 To WenB.Count - 1
  WenB(I) = ""
Next I
For I = 0 To ShuZBJ.Count - 1
  ShuZBJ(I) = ""
Next I
For I = 0 To ShuZ.Count - 1
  ShuZ(I) = ""
Next I
Set rec = dbEstate.OpenRecordset("select * from JiangL where 工号=''", dbOpenSnapshot)
Set Data1.Recordset = rec
End Sub

Private Sub cmdPrint_Click()
Dim Excel As Object
Dim WorkSheet As Object
Dim WorkBook As Object

Kill App.Path + "\JiangL.xls"
Name App.Path + "\JiangLcopy.xls" As App.Path + "\JiangL.xls"
FileCopy App.Path + "\JiangL.xls", App.Path + "\JiangLcopy.xls"

PrintInExcel rec, "JiangL.xls", "am"

Set Excel = CreateObject("Excel.application")
Excel.Workbooks.Open App.Path + "\JiangL.xls"
Set WorkBook = Excel.ActiveWorkbook
WorkBook.Worksheets("sample").Select

Set WorkSheet = Excel.ActiveSheet
Excel.Visible = True

WorkBook.Saved = True
End Sub

Private Sub cmdQuery_Click()
Dim bChoice As Boolean
Dim sql As String
Dim nWenB As Integer
Dim nShuZ As Integer
Dim nShiJ As Integer

bChoice = False
nWenB = 0
nShuZ = 0
nShiJ = 0

sql = "select * from JiangL where "
Dim I As Integer
For I = 0 To rec.Fields.Count - 1

Select Case I
'文本型查询
Case 2, 3, 14:
  If Len(Trim(WenB(nWenB))) <> 0 Then
      If bChoice Then
          sql = sql & "and " + Trim(rec.Fields(I).Name) + " like '*" + Trim(WenB(nWenB)) + "*' "
      Else
          sql = sql & "" + Trim(rec.Fields(I).Name) + " like '*" + Trim(WenB(nWenB)) + "*' "
          bChoice = True
      End If
  End If
  nWenB = nWenB + 1

'数字型查询
Case 4, 5, 7, 9:
  If Len(Trim(ShuZBJ(nShuZ))) <> 0 Then
      If Len(Trim(ShuZ(nShuZ))) <> 0 Then
          If bChoice Then
              sql = sql & "and " + Trim(rec.Fields(I).Name) + "" & Trim(ShuZBJ(nShuZ)) & "" + Trim(ShuZ(nShuZ)) + " "
          Else
              sql = sql & "" + Trim(rec.Fields(I).Name) + "" & Trim(ShuZBJ(nShuZ)) & "" + Trim(ShuZ(nShuZ)) + " "
              bChoice = True
          End If
      End If
  End If
  nShuZ = nShuZ + 1


'时间型查询
Case 6, 8, 10:
    If ShiJF(nShiJ) = "__/__/____" And ShiJT(nShiJ) <> "__/__/____" Then
        If Not IsDate(ShiJT(nShiJ)) Then
            MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
            ShiJT(nShiJ) = "__/__/____"
            Exit Sub
        End If
        If bChoice Then
            sql = sql + "and " + Trim(rec.Fields(I).Name) + " <=" + "#" + "" + Trim(ShiJT(nShiJ).Text) + "" + "#" + " "
        Else
            sql = sql + "" + Trim(rec.Fields(I).Name) + " <=" + "#" + "" + Trim(ShiJT(nShiJ).Text) + "" + "#" + " "
            bChoice = True
        End If
    End If
    If ShiJF(nShiJ).Text <> "__/__/____" And ShiJT(nShiJ) = "__/__/____" Then
        If Not IsDate(ShiJF(nShiJ)) Then
            MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
            ShiJF(nShiJ) = "__/__/____"
            Exit Sub
        End If
        If bChoice Then
            sql = sql + "and " + Trim(rec.Fields(I).Name) + ">=" + "#" + "" + Trim(ShiJF(nShiJ).Text) + "" + "#" + " "
        Else
            sql = sql + "" + Trim(rec.Fields(I).Name) + ">=" + "#" + "" + Trim(ShiJF(nShiJ).Text) + "" + "#" + " "
            bChoice = True
        End If
    End If
    If ShiJF(nShiJ) <> "__/__/____" And ShiJT(nShiJ) <> "__/__/____" Then
        If Not IsDate(ShiJT(nShiJ)) Then
            MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
            ShiJT(nShiJ) = "__/__/____"
            Exit Sub
        End If
         If Not IsDate(ShiJF(nShiJ)) Then
            MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
            ShiJF(nShiJ) = "__/__/____"
            Exit Sub
        End If
        If bChoice Then
            sql = sql + "and " + Trim(rec.Fields(I).Name) + " between" + " #" + "" + Trim(ShiJF(nShiJ).Text) + "" + "#" + " and " + "#" + "" + Trim(ShiJT(nShiJ).Text) + "" + "#" + " "
        Else
            sql = sql + "" + Trim(rec.Fields(I).Name) + " between" + " #" + "" + Trim(ShiJF(nShiJ).Text) + "" + "#" + " and " + "#" + "" + Trim(ShiJT(nShiJ).Text) + "" + "#" + " "
            bChoice = True
        End If
    End If
    nShiJ = nShiJ + 1
End Select
Next I
If Not bChoice Then
    MsgBox "没有选择条件！", vbExclamation + vbOKOnly, "信息"
Else
    Set rec = dbEstate.OpenRecordset(sql, dbOpenSnapshot)
    If rec.RecordCount > 0 Then rec.MoveLast
    StatusBar1.Panels(1).Text = "共有" & CStr(rec.RecordCount) & "条记录"
    Set Data1.Recordset = rec
End If
End Sub

Private Sub Form_Load()
Dim recFind As Recordset
Data1.DatabaseName = App.Path & "\dbestate.mdb"
Set rec = dbEstate.OpenRecordset("select * from JiangL", dbOpenSnapshot)
InitCombo
End Sub
'添加Combo框的内容
Private Sub InitCombo()
Dim I As Integer
Dim recCombo As Recordset
Dim nWenB As String
nWenB = 0
For I = 0 To rec.Fields.Count - 1
Select Case I
Case 2, 3, 14:
  Dim sql As String
  sql = "select distinct " + Trim(rec.Fields(I).Name) + " from JiangL"
  Set recCombo = dbEstate.OpenRecordset(sql, dbOpenSnapshot)
  If recCombo.RecordCount > 0 Then
      recCombo.MoveLast
      recCombo.MoveFirst
      While Not recCombo.EOF
        If Not IsNull(recCombo.Fields(0)) Then WenB(nWenB).AddItem CStr(recCombo.Fields(0))
        recCombo.MoveNext
      Wend
  End If
  nWenB = nWenB + 1
End Select
Next I
End Sub