form4.frm

本人长期从事人事管理工作

      Height          =   255
      Left            =   4560
      TabIndex        =   18
      Top             =   5760
      Width           =   975
   End
   Begin VB.Label Label1 
      Caption         =   "员工编号："
      Height          =   255
      Left            =   120
      TabIndex        =   14
      Top             =   3480
      Width           =   975
   End
   Begin VB.Label Label2 
      Caption         =   "姓名："
      Height          =   255
      Left            =   2880
      TabIndex        =   13
      Top             =   3480
      Width           =   975
   End
   Begin VB.Label Label3 
      Caption         =   "性别："
      Height          =   255
      Left            =   5160
      TabIndex        =   12
      Top             =   3480
      Width           =   615
   End
   Begin VB.Label Label4 
      Caption         =   "所在部门："
      Height          =   255
      Left            =   120
      TabIndex        =   11
      Top             =   3960
      Width           =   975
   End
   Begin VB.Label Label5 
      Caption         =   "岗位名称："
      Height          =   255
      Left            =   4920
      TabIndex        =   10
      Top             =   3960
      Width           =   975
   End
   Begin VB.Label Label6 
      Caption         =   "出生日期："
      Height          =   255
      Left            =   120
      TabIndex        =   9
      Top             =   5760
      Width           =   975
   End
End
Attribute VB_Name = "Form4"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()

'本段为查询语句

'    1\首先判断是否填写查询条件,当没有输入任何查询条件时,提醒输入查询条件,不执行sql查询
'语句,因为如果没有任何查询条件时, 执行SQL查询语句时, 会产生错误: where后面没有语句

'    2\每一个查询项目均有"and xxx",但如果这个查询项目成为第一个项目,则"where and"的语法是错误的,
'为了避免这个错误出现,在"出生日期"字段设置chushengtime = " [出生日期]>=cdate('" & Text3.Text & "') "
'即:不论是否填写"出生日期",均让chushengtime 在SQL语句的第一个出现.


Dim i As Integer, lb(8) As String
Dim t As Integer, sd As String
Dim sql As String
Dim n As Long

'删除query中所有数据
Data1.Database.Execute "delete from query"

 
 '判断是否填写查询条件
If _
Text1.Text = "" And _
Text2.Text = "" And _
Text3.Text = "1900-01-01" And _
Text5.Text = "1900-01-01" And _
Text6.Text = "1900-01-01" And _
Text7.Text = "1900-01-01" And _
Text4.Text = "" And _
Text8.Text = "" And _
Check1(0).Value = 0 And _
Check1(1).Value = 0 And _
Check2(0).Value = 0 And _
Check2(1).Value = 0 And _
Check2(2).Value = 0 And _
Check2(3).Value = 0 And _
Check2(4).Value = 0 And _
Check2(5).Value = 0 And _
Check2(6).Value = 0 And _
Check2(7).Value = 0 And _
DBCombo1.Text = "" And _
DBCombo2.Text = "" Then


MsgBox "请输入查询条件!", vbOKOnly, "提示"
Text1.SetFocus

Exit Sub

End If


 '判断各查询项是否填写

If Text1.Text <> "" Then  '员工编号是否为空
   bianhao = " and [员工编号] = '" & Text1.Text & "' "
Else
   bianhao = ""
End If

If Text2.Text <> "" Then  '姓名是否为空
   xingming = " and [姓名] = '" & Text2.Text & "' "
Else
   xingming = ""
End If

                           '性别是否为空
If (Check1(0).Value = 1 And Check1(1) = 1) Or _
   (Check1(0).Value = 0 And Check1(1) = 0) Then
   xingbie = ""
Else
   If Check1(0).Value = 1 Then
      xingbie = "and [性别]='男'"
   Else
      xingbie = "and [性别]='女'"
   End If
End If

If DBCombo1.Text <> "" Then   '所在部门是否为空
   bumen = " and [所在部门] = '" & DBCombo1.Text & "' "
Else
   bumen = ""
End If

If DBCombo2.Text <> "" Then   '岗位名称是否为空
   gangwei = " and [岗位名称] = '" & DBCombo2.Text & "' "
Else
   gangwei = ""
End If

If Text8.Text <> "" Then     '所学专业是否为空
   zhuanye = " and [所学专业] = '" & Text8.Text & "' "
Else
   zhuanye = ""
End If

If Text4.Text <> "" Then     '毕业学校是否空
   xuexiao = " and [毕业学校] = '" & Text4.Text & "' "
Else
   xuexiao = ""
End If

If Check2(0).Value = 0 And _
   Check2(1).Value = 0 And _
   Check2(2).Value = 0 And _
   Check2(3).Value = 0 And _
   Check2(4).Value = 0 And _
   Check2(5).Value = 0 And _
   Check2(6).Value = 0 And _
   Check2(7).Value = 0 Then
   
   xueli0 = ""
   xueli1 = ""
   xueli2 = ""
   xueli3 = ""
   xueli4 = ""
   xueli5 = ""
   xueli6 = ""
   xueli7 = ""
      
End If
   
If Check2(0).Value = 1 Then
   xueli0 = "and [最高学历]='小学'"
Else
   xueli0 = ""
End If
      
If Check2(1).Value = 1 Then
   xueli1 = "and [最高学历]='初中'"
Else
   xueli1 = ""
End If

If Check2(2).Value = 1 Then
   xueli2 = "and [最高学历]='高中'"
Else
   xueli2 = ""
End If

If Check2(3).Value = 1 Then
   xueli3 = "and [最高学历]='大专'"
Else
   xueli3 = ""
End If

If Check2(4).Value = 1 Then
   xueli4 = "and [最高学历]='本科'"
Else
   xueli4 = ""
End If

If Check2(5).Value = 1 Then
   xueli5 = "and [最高学历]='硕士'"
Else
   xueli5 = ""
End If

If Check2(6).Value = 1 Then
   xueli6 = "and [最高学历]='博士'"
Else
   xueli6 = ""
End If

If Check2(7).Value = 1 Then
   xueli7 = "and [最高学历]='博士后'"
Else
   xueli7 = ""
End If

 
'判断出生日期  !注意: <=不能写成=>,也不能写成< = !

If CDate(Text6.Text) < CDate(Text3.Text) Then
    MsgBox "后面的日期应大于前面的!" + Chr(13) + "为了选出您想要的记录请修改过来.", 48, "日期错误"
   Text6.SetFocus
   Exit Sub

End If

If Text3.Text = "1900-01-01" And Text6.Text = "1900-01-01" Then
   chushengtime = " [出生日期]>=cdate('" & Text3.Text & "') "
Else
   chushengtime = " [出生日期]>=CDate('" & Text3.Text & "') and [出生日期]<=CDate('" & Text6.Text & "')"
End If


'判断毕业时间

If CDate(Text7.Text) < CDate(Text5.Text) Then
    MsgBox "后面的日期应大于前面的!" + Chr(13) + "为了选出您想要的记录请修改过来.", 48, "日期错误"
   Text7.SetFocus
  Exit Sub

End If

If Text5.Text = "1900-01-01" And Text7.Text = "1900-01-01" Then
   biyetime = ""
Else
  biyetime = " and [到岗时间]>=CDate('" & Text5.Text & "') and [到岗时间] <= CDate('" & Text7.Text & "') "
End If

                       
                       
 '将查询结果放入query表中
 
 sql = "insert into query select mang.*  from mang where " + chushengtime + bianhao + xingming + xingbie + bumen + gangwei + zhuanye + xuexiao + biyetime + xueli0 + xueli1 + xueli2 + xueli3 + xueli4 + xueli5 + xueli6 + xueli7
 
 Data1.Database.Execute sql
  
  
                         
'刷新数据库内容,以显示最新查询信息

Form4.Data1.Refresh


'每查询一次后,刷新查询条件

Text1.Text = ""
Text2.Text = ""
Text3.Text = "1900-01-01"
Text5.Text = "1900-01-01"
Text6.Text = "1900-01-01"
Text7.Text = "1900-01-01"
Text4.Text = ""
Text8.Text = ""


DBCombo1.Text = ""
DBCombo2.Text = ""

Check1(0).Value = 0
Check1(1).Value = 0

Check2(0).Value = 0
Check2(1).Value = 0
Check2(2).Value = 0
Check2(3).Value = 0
Check2(4).Value = 0
Check2(5).Value = 0
Check2(6).Value = 0
Check2(7).Value = 0


   n = Data1.Recordset.RecordCount

   Label15.Caption = "" & n & ""
   
End Sub

Private Sub Command2_Click()



Unload Form4

End Sub

Private Sub Command3_Click()

If Data1.Recordset.BOF And Data1.Recordset.EOF Then

MsgBox "没有查询结果，无法打印!"

Exit Sub

End If

Form6.Show

End Sub

Private Sub Command4_Click()
 Form2.Show                            '打开FRMNEW窗口
 Form2.Command2.Visible = False
 
 Form2.Data1.Recordset.FindFirst "员工编号 ='" & Form4.DBGrid1.Text & "'"
End Sub

Private Sub Form_Activate()

Text3.Text = "1900-01-01"
Text5.Text = "1900-01-01"
Text6.Text = "1900-01-01"
Text7.Text = "1900-01-01"


End Sub

Private Sub Form_Load()

Data1.DatabaseName = App.Path & "\mang.mdb"
Data1.RecordSource = "query"

Data2.DatabaseName = App.Path & "\mang.mdb"
Data2.RecordSource = "dep"

Data3.DatabaseName = App.Path & "\mang.mdb"
Data3.RecordSource = "func"

End Sub

Private Sub Form_Unload(Cancel As Integer)


'删除query中所有数据
Data1.Database.Execute "delete from query"
End Sub