formfind.frm

一个VB做的考试日程安排管理系统

VERSION 5.00
Object = "{CFA7AFF4-3242-4269-9172-7389D695AE01}#1.0#0"; "StoneXP.ocx"
Object = "{DAAC6951-59A4-4C08-9D6E-FE3919B64861}#1.0#0"; "FlexCell.ocx"
Begin VB.Form formfind 
   Caption         =   "查询"
   ClientHeight    =   5535
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   8535
   Icon            =   "formfind.frx":0000
   LinkTopic       =   "Form1"
   ScaleHeight     =   5535
   ScaleWidth      =   8535
   StartUpPosition =   1  '所有者中心
   Begin StoneXP.XPButton XPButton1 
      Height          =   495
      Left            =   3240
      TabIndex        =   2
      Top             =   1080
      Width           =   1815
      _ExtentX        =   3201
      _ExtentY        =   873
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ButtonStyle     =   2
      Caption         =   "开始执行"
      MouseIcon       =   "formfind.frx":F84A
      MousePointer    =   99
   End
   Begin FlexCell.Grid Grid1 
      Height          =   555
      Left            =   0
      TabIndex        =   0
      Top             =   240
      Width           =   8535
      _ExtentX        =   15055
      _ExtentY        =   979
      Appearance      =   0
      Cols            =   6
      Rows            =   2
      ScrollBars      =   0
   End
   Begin FlexCell.Grid Grid2 
      Height          =   3495
      Left            =   0
      TabIndex        =   1
      Top             =   2040
      Width           =   8535
      _ExtentX        =   15055
      _ExtentY        =   6165
      Appearance      =   0
      Cols            =   7
      Rows            =   1
   End
   Begin VB.Label Label1 
      BackStyle       =   0  'Transparent
      Caption         =   "符合条件记录数："
      ForeColor       =   &H00FF0000&
      Height          =   255
      Left            =   5880
      TabIndex        =   3
      Top             =   1680
      Width           =   2535
   End
End
Attribute VB_Name = "formfind"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim n As Integer
Private Sub Form_Load()
'--------------------------
'以下窗体内容为查询系统的代码
'--------------------------
Grid1.SetRegisterInformation "CNwinndy", "W]vyY-nonvk-u\nty-Zbl_e-`hms^" '进行注册
Grid2.SetRegisterInformation "CNwinndy", "W]vyY-nonvk-u\nty-Zbl_e-`hms^" '进行注册
With Grid1 '以下是对表格控件进行一些颜色和字体等设置
    .AllowUserResizing = True
    .DisplayFocusRect = False
    .ExtendLastCol = True
    .Appearance = Flat
    .FixedRowColStyle = Flat
    .ScrollBarStyle = Flat
    
    .DefaultFont.Name = "Tahoma"
    .DefaultFont.SIZE = 8
    .BackColorFixed = RGB(84, 201, 134) 'RGB(90, 158, 214)
    .BackColorFixedSel = RGB(84, 201, 134) 'RGB(110, 180, 230)
    .BackColorBkg = RGB(84, 201, 134) 'RGB(90, 158, 214)
    .BackColorScrollBar = RGB(231, 235, 247)
    .BackColor1 = RGB(231, 235, 247)
    .BackColor2 = RGB(239, 243, 255)
    .GridColor = RGB(148, 190, 231)
    .Column(0).Width = 0
End With
With Grid2
    .AllowUserResizing = True
    .DisplayFocusRect = False
    .ExtendLastCol = True
    .Appearance = Flat
    .FixedRowColStyle = Flat
    .ScrollBarStyle = Flat
    
    .DefaultFont.Name = "Tahoma"
    .DefaultFont.SIZE = 8
    .BackColorFixed = RGB(90, 158, 214)
    .BackColorFixedSel = RGB(110, 180, 230)
    .BackColorBkg = RGB(90, 158, 214)
    .BackColorScrollBar = RGB(231, 235, 247)
    .BackColor1 = RGB(231, 235, 247)
    .BackColor2 = RGB(239, 243, 255)
    .GridColor = RGB(148, 190, 231)
    .Column(0).Width = 0
End With
Me.BackColor = RGB(84, 201, 134)
'---------------------
'以下cellcombobox是控件grid 的一个属性，即是在表格单元格可以出现下拉框，
'下拉框中就是用户所需的快捷内容
'----------------------
Grid1.Column(1).CellType = cellComboBox
Grid1.Column(2).CellType = cellComboBox
Grid1.Column(3).CellType = cellComboBox
Grid1.Cell(0, 1).Text = "查询模式" '将表格内添入固定内容
Grid1.Cell(0, 2).Text = "查询段"
Grid1.Cell(0, 3).Text = "条件"
Grid1.Cell(0, 4).Text = "关键字"
Grid1.Cell(0, 5).Text = "附加条件"
Grid1.ComboBox(1).AddItem "精确查询" '这里是类似combo框的操作，也是把内容添入combo控件的记录集
Grid1.ComboBox(1).AddItem "模糊查询"
Grid1.ComboBox(2).AddItem "系名"
Grid1.ComboBox(2).AddItem "年级"
Grid1.ComboBox(2).AddItem "课程名"
Grid1.ComboBox(2).AddItem "教室号"
Grid1.ComboBox(2).AddItem "考试日期"
Grid1.ComboBox(2).AddItem "时间"
Grid1.ComboBox(2).AddItem "楼层"
Grid1.ComboBox(2).AddItem "教师"
Grid1.ComboBox(2).AddItem "教师监考次数"
Grid1.Cell(1, 1).Text = "精确查询"
End Sub

Private Sub Grid1_RowColChange(ByVal Row As Long, ByVal Col As Long)
If Grid1.Cell(1, 1).Text <> "" Then
 If Grid1.Cell(1, 1).Text = "精确查询" Then
Grid1.ComboBox(3).Clear '以下清除表格中combo集中的内容,并加入以下内容
Grid1.ComboBox(3).AddItem "="
Grid1.ComboBox(3).AddItem ">"
Grid1.ComboBox(3).AddItem "<"
Grid1.ComboBox(3).AddItem ">="
Grid1.ComboBox(3).AddItem "<="
Grid1.ComboBox(3).AddItem "<>"
Else
Grid1.ComboBox(3).Clear
Grid1.ComboBox(3).AddItem "like"
End If
End If
End Sub

Private Sub XPButton1_Click()
On Error GoTo finish '防错代码，防止用户组织语句的错误或其它不可预见的错误发生
Dim strsql As String '定义一个组织变量
Dim moshi As Integer '这是主要判断选择模式(精确还模糊)
If Grid1.Cell(1, 1).Text = "精确查询" Then
   moshi = 0
Else
   moshi = 1
End If
Select Case Grid1.Cell(1, 2).Text '以下主要通过多项选择来决定SQL语句的组织方法
 Case "年级", "课程名", "考试日期", "时间", "系名" '此处几个都属通用的SQL语句，所以可以放在一起
 If moshi = 0 Then '通过已确定的模糊查询还是精确查询来决定语句的组成
  strsql = "select * from 生成 where " & Grid1.Cell(1, 2).Text & Grid1.Cell(1, 3).Text & "'" & Grid1.Cell(1, 4).Text & "'"
 Else
   strsql = "select * from 生成 where " & Grid1.Cell(1, 2).Text & " " & Grid1.Cell(1, 3).Text & " '%" & Grid1.Cell(1, 4).Text & "%'"
 End If
  numberkc = 6 '设置字段长和表格的列数
  Grid2.Cols = numberkc + 1
 Case "教室号"
  If moshi = 0 Then
  strsql = "select * from 生成 where " & Grid1.Cell(1, 2).Text & " like '%" & Grid1.Cell(1, 4).Text & "%'"
  '关于教室语句中用到%号，因为每个系并不是一个考场，所以在教定中含有多个教室，所以需要%来提取
 Else
   strsql = "select * from 生成 where " & Grid1.Cell(1, 2).Text & " " & Grid1.Cell(1, 3).Text & " '%" & Grid1.Cell(1, 4).Text & "%'"
 End If
  numberkc = 6
  Grid2.Cols = numberkc + 1
 Case "教师"
  strsql = "select 监考教师.教室号,教师姓名,日期,监考教师.时间,课程名,系名,年级 from 监考教师,生成 where 考试日期=日期 and 监考教师.时间=生成.时间 and 教师姓名 like '%" & Grid1.Cell(1, 4).Text & "%'"
  numberkc = 7
  Grid2.Cols = numberkc + 1
 Case "楼层"
  If moshi = 0 Then
   strsql = "select 考试日期,时间,课程名,生成.教室号,系名,年级,楼层 from 教室,生成 where 楼层='" & Grid1.Cell(1, 4).Text & "'"
  Else
     strsql = "select 考试日期,时间,课程名,生成.教室号,系名,年级,楼层 from 教室,生成 where 楼层 like '%" & Grid1.Cell(1, 4).Text & "%'"
  End If
   numberkc = 7
   Grid2.Cols = numberkc + 1
 Case "教师监考次数"
  If moshi = 0 Then
   strsql = "select * from 安排 where 空闲='1' and 教师姓名='" & Grid1.Cell(1, 4).Text & "'"
  Else
   strsql = "select * from 安排 where 空闲='1' and 教师姓名 like '%" & Grid1.Cell(1, 4).Text & "%'"
  End If
  numberkc = 4
End Select
   If Grid1.Cell(1, 5).Text <> "" Then '这是确定是否有附加条件，有则将其并入语句中一起执行
    strsql = strsql & " " & Grid1.Cell(1, 5).Text
   End If
Grid2.Rows = 1
Set ks1 = cnn.Execute(strsql) '这里执行已组织好的语句
For i = 1 To numberkc '通来字段的长度，将各字段应有的名称写入表格
Grid2.Cell(0, i).Text = ks1.Fields(i - 1).Name
Next
Do While Not ks1.EOF '以下是将查询到的内容依次写入表格
Grid2.Rows = Grid2.Rows + 1
For i = 1 To numberkc
Grid2.Cell(Grid2.Rows - 1, i).Text = ks1.Fields(i - 1)
Next
ks1.MoveNext
Loop
Label1.Caption = "符合条件的记录数:" & Grid2.Rows - 1 & "条!"
Exit Sub
finish:
  MsgBox Err.Description
End Sub