frmquerymain.frm

教务管理系统,用VB 完成,以SQL SERVER 2000作为后台数据库

         Width           =   1095
      End
      Begin VB.Label Label2 
         Alignment       =   1  'Right Justify
         Caption         =   "姓名"
         ForeColor       =   &H00FF0000&
         Height          =   255
         Left            =   180
         TabIndex        =   34
         Top             =   210
         Width           =   1095
      End
      Begin VB.Label Label1 
         Alignment       =   1  'Right Justify
         Caption         =   "学号"
         ForeColor       =   &H00FF0000&
         Height          =   255
         Left            =   180
         TabIndex        =   33
         Top             =   480
         Width           =   1095
      End
   End
   Begin VB.Line Line2 
      BorderColor     =   &H00000000&
      X1              =   0
      X2              =   1920
      Y1              =   0
      Y2              =   0
   End
   Begin VB.Line Line1 
      BorderColor     =   &H00FF0000&
      X1              =   60
      X2              =   4320
      Y1              =   -30
      Y2              =   -30
   End
   Begin VB.Menu MNU1 
      Caption         =   "系统[&F]"
      Begin VB.Menu MNU11 
         Caption         =   "退出[&X]"
      End
   End
   Begin VB.Menu MNUQH 
      Caption         =   "功能切换[&C]"
      Begin VB.Menu MNUQH1 
         Caption         =   "添加记录"
      End
      Begin VB.Menu MNUQH2 
         Caption         =   "修改记录"
      End
      Begin VB.Menu MNUQH3 
         Caption         =   "信息统计"
      End
   End
   Begin VB.Menu MNU2 
      Caption         =   "帮助[&H]"
      Begin VB.Menu MNU21 
         Caption         =   "查询使用说明[&N]"
      End
   End
End
Attribute VB_Name = "frmQuery"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub cmdDelete_Click()
On Error Resume Next
GiveUp
End Sub

Private Sub cmdExit_Click()
On Error Resume Next
Me.Hide
Unload Me
'Frmstart.Show
End Sub

Private Sub cmdLook_Click()
On Error Resume Next
sqlForMain = "select xh,xm,csny,xb,mz,yx,bj,hksx,nj,sy,zzmm,xl,tc,sfzhm,ltkhm,ss,dh,zy,pyfs,byzx from zbqkb"
sqlForPrint = " "
frmGrid.Show 1
End Sub

Private Sub cmdQuery_Click()
On Error Resume Next
Dim Start As Integer '从此开始组建SQL语句
Dim EmpetyAll As Boolean '为空则无法组建SQL语句
Dim EmpetyThis As Boolean
Dim recForMain As Recordset
Dim I As Integer
sqlForMain = "select xh,xm,csny,xb,mz,yx,bj,hksx,nj,sy,zzmm,zy,pyfs,byzx,xl,tc,sfzhm,ltkhm,ss,dh from zbqkb where "
sqlForPrint = ""
EmpetyAll = True
EmpetyThis = False
'寻找第一个有效选项 院系
For I = 0 To 14
  Start = I
  If chkYX(I).Value = 1 Then
      sqlForMain = sqlForMain + "(yx='" + Trim(chkYX(I).Caption) + "' "
      sqlForPrint = sqlForPrint + "({zbqkb.yx}='" + Trim(chkYX(I).Caption) + "' "
      EmpetyAll = False
      EmpetyThis = True
      Exit For
  End If
Next I
'组建完整的SQL语句 院系
If Start < 14 Then
    For I = Start + 1 To 14
      If chkYX(I).Value = 1 Then
          sqlForMain = sqlForMain + "or yx='" + Trim(chkYX(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "or {zbqkb.yx}='" + Trim(chkYX(I).Caption) + "' "
      End If
    Next I
End If
If EmpetyThis Then
    sqlForMain = sqlForMain + ") "
    sqlForPrint = sqlForPrint + ") "
End If
'寻找第一个有效选项 生源
EmpetyThis = False
For I = 0 To 29
  Start = I
  If chkSY(I).Value = 1 Then
      If Right(RTrim(sqlForMain), 5) = "where" Then
          sqlForMain = sqlForMain + "(sy like '" + chkSY(I).Caption + "*'"
          sqlForPrint = sqlForPrint + "({zbqkb.sy} like '" + chkSY(I).Caption + "*'"
      Else
          sqlForMain = sqlForMain + "and (sy like '" + chkSY(I).Caption + "*'"
          sqlForPrint = sqlForPrint + "and ({zbqkb.sy} like '" + chkSY(I).Caption + "*'"
      End If
      EmpetyAll = False
      EmpetyThis = True
      Exit For
  End If
Next I
'组建完整的SQL语句 生源
If Start < 29 Then
    For I = Start + 1 To 29
      If chkSY(I).Value = 1 Then
          sqlForMain = sqlForMain + "or  SY like '" + chkSY(I).Caption + "*' "
          sqlForPrint = sqlForPrint + "or {zbqkb.sy} like '" + chkSY(I).Caption + "*' "
      End If
    Next I
End If
If EmpetyThis Then
    sqlForMain = sqlForMain + ") "
    sqlForPrint = sqlForPrint + ") "
End If
'寻找第一个有效选项 民族
EmpetyThis = False
For I = 0 To 4
  Start = I
  If chkMZ(I).Value = 1 Then
      If Right(RTrim(sqlForMain), 5) = "where" Then
          sqlForMain = sqlForMain + "(mz='" + Trim(chkMZ(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "({zbqkb.mz}='" + Trim(chkMZ(I).Caption) + "' "
      Else
          sqlForMain = sqlForMain + "and (mz='" + Trim(chkMZ(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "and ({zbqkb.mz}='" + Trim(chkMZ(I).Caption) + "' "
      End If
      EmpetyAll = False
      EmpetyThis = True
      Exit For
  End If
Next I
'组建完整的SQL语句 民族
If Start < 4 Then
    For I = Start + 1 To 4
      If chkMZ(I).Value = 1 Then
          sqlForMain = sqlForMain + "or mz='" + Trim(chkMZ(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "or {zbqkb.mz}='" + Trim(chkMZ(I).Caption) + "' "
      End If
    Next I
End If
'少数民族特殊处理
If chkMZ(5).Value = 1 Then
    EmpetyAll = False
    EmpetyThis = True
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(mz<>'汉族' and mz<>'回族' and mz<>'满族' and mz<>'壮族' and mz<>'藏族' "
        sqlForPrint = sqlForPrint + "({zbqkb.mz}<>'汉族' and {zbqkb.mz}<>'回族' and {zbqkb.mz}<>'壮族' and {zbqkb.mz}<>'满族' and {zbqkb.mz}<>'藏族' "
    Else
        If Start = 4 And chkMZ(4).Value = 0 Then
            sqlForMain = sqlForMain + "and (mz<>'汉族' and mz<>'回族' and mz<>'满族' and mz<>'壮族' and mz<>'藏族' "
            sqlForPrint = sqlForPrint + "and ({zbqkb.mz}<>'汉族' and {zbqkb.mz}<>'回族' and {zbqkb.mz}<>'满族' and {zbqkb.mz}<>'壮族' and {zbqkb.mz}<>'藏族' "
        Else
            sqlForMain = sqlForMain + "or (mz<>'汉族' and mz<>'回族' and mz<>'满族' and mz<>'壮族' and mz<>'藏族') "
            sqlForPrint = sqlForPrint + "or ({zbqkb.mz}<>'汉族' and {zbqkb.mz}<>'回族' and {zbqkb.mz}<>'满族' and {zbqkb.mz}<>'壮族' and {zbqkb.mz}<>'藏族' "
        End If
    End If
End If
If EmpetyThis Then
    sqlForMain = sqlForMain + ") "
    sqlForPrint = sqlForPrint + ") "
End If
'寻找第一个有效选项 学历
EmpetyThis = False
For I = 0 To 5
  Start = I
  If chkXL(I).Value = 1 Then
      If Right(RTrim(sqlForMain), 5) = "where" Then
          sqlForMain = sqlForMain + "(xl='" + Trim(chkXL(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "({zbqkb.xl}='" + Trim(chkXL(I).Caption) + "' "
      Else
          sqlForMain = sqlForMain + "and (xl='" + Trim(chkXL(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "and ({zbqkb.xl}='" + Trim(chkXL(I).Caption) + "' "
      End If
      EmpetyAll = False
      EmpetyThis = True
      Exit For
  End If
Next I
'组建完整的SQL语句 学历
If Start < 5 Then
    For I = Start + 1 To 5
      If chkXL(I).Value = 1 Then
          sqlForMain = sqlForMain + "or xl='" + Trim(chkXL(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "or {zbqkb.xl}='" + Trim(chkXL(I).Caption) + "' "
      End If
    Next I
End If
If EmpetyThis Then
    sqlForMain = sqlForMain + ") "
    sqlForPrint = sqlForPrint + ") "
End If
'寻找第一个有效选项 年级
EmpetyThis = False
For I = 0 To 6
  Start = I
  If chkNJ(I).Value = 1 Then
      If Right(RTrim(sqlForMain), 5) = "where" Then
          sqlForMain = sqlForMain + "(nj='" + Trim(chkNJ(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "({zbqkb.nj}='" + Trim(chkNJ(I).Caption) + "' "
      Else
          sqlForMain = sqlForMain + "and (nj='" + Trim(chkNJ(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "and ({zbqkb.nj}='" + Trim(chkNJ(I).Caption) + "' "
      End If
      EmpetyAll = False
      EmpetyThis = True
      Exit For
  End If
Next I
'组建完整的SQL语句 年级
If Start < 6 Then
    For I = Start + 1 To 6
      If chkNJ(I).Value = 1 Then
          sqlForMain = sqlForMain + "or nj='" + Trim(chkNJ(I).Caption) + "' "
          sqlForPrint = sqlForPrint + "or {zbqkb.nj}='" + Trim(chkNJ(I).Caption) + "' "
      End If
    Next I
End If
If txtNJ <> " " Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "nj='" + Trim(txtNJ) + "'"
        sqlForPrint = sqlForPrint + "{zbqkb.nj}='" + Trim(txtNJ) + "'"
    Else
        sqlForMain = sqlForMain + "and nj='" + Trim(txtNJ) + "'"
        sqlForPrint = sqlForPrint + "and {zbqkb.nj}='" + Trim(txtNJ) + "'"
    End If
End If
If EmpetyThis Then
    sqlForMain = sqlForMain + ") "
    sqlForPrint = sqlForPrint + ") "
End If
'学号
If txtXH <> " " Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(xh='" + Trim(txtXH) + "') "
        sqlForPrint = sqlForPrint + "({zbqkb.xh}='" + Trim(txtXH) + ") "
    Else
        sqlForMain = sqlForMain + "and (xh='" + Trim(txtXH) + "') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.xh}='" + Trim(txtXH) + "') "
    End If
End If
'姓名
If txtXM <> " " Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(xm like '" + Trim(txtXM) + "*') "
        sqlForPrint = sqlForPrint + "({zbqkb.xm} like '" + Trim(txtXH) + "*') "
    Else
        sqlForMain = sqlForMain + "and (xm like '" + Trim(txtXM) + "*') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.xm like '" + Trim(txtXH) + "*') "
    End If
End If
'身份证
If txtSFZ <> " " Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(sfzhm='" + Trim(txtSFZ) + "') "
        sqlForPrint = sqlForPrint + "({zbqkb.sfzhm}='" + Trim(txtSFZ) + "') "
    Else
        sqlForMain = sqlForMain + "and (sfzhm='" + Trim(txtSFZ) + "') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.sfzhm}='" + Trim(txtSFZ) + "') "
    End If
End If
'户口属性
If optCity = True Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(hksx='城市') "
        sqlForPrint = sqlForPrint + "({zbqkb.hksx}='城市') "
    Else
        sqlForMain = sqlForMain + "and (hksx='城市') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.hksx}='城市') "
    End If
End If
If optCountry = True Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(hksx='农村') "
        sqlForPrint = sqlForPrint + "({zbqkb.hksx}='农村') "
    Else
        sqlForMain = sqlForMain + "and (hksx='农村') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.hksx}='农村') "
    End If
End If
'性别
If optMan = True Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(xb='男') "
        sqlForPrint = sqlForPrint + "({zbqkb.xb}='男') "
    Else
        sqlForMain = sqlForMain + "and (xb='男') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.xb}='男') "
    End If
End If
If optWoman = True Then
    EmpetyAll = False
    If Right(RTrim(sqlForMain), 5) = "where" Then
        sqlForMain = sqlForMain + "(xb='女') "
        sqlForPrint = sqlForPrint + "({zbqkb.xb}='女') "
    Else
        sqlForMain = sqlForMain + "and (xb='女') "
        sqlForPrint = sqlForPrint + "and ({zbqkb.xb}='女') "
    End If
End If
'如果无选择项,则退出过程
If EmpetyAll Then
    MsgBox "无选择条件"
    Exit Sub
End If
'完成查询动作
frmGrid.Show 1
End Sub

Private Sub Command1_Click()
On Error Resume Next
Unload Me
End Sub


Public Sub GiveUp()
On Error Resume Next
Dim I As Integer
For I = 0 To 14
  chkYX(I).Value = 0
Next I
For I = 0 To 29
  chkSY(I).Value = 0
Next I
For I = 0 To 5
  chkMZ(I).Value = 0
Next I
For I = 0 To 5
  chkXL(I).Value = 0
Next
For I = 0 To 6
  chkNJ(I).Value = 0
Next I
txtNJ = " "
txtXM = " "
txtXH = " "
txtSFZ = " "
optCity = False
optCountry = False
optMan = False
optWoman = False
End Sub

Private Sub Form_Load()
On Error Resume Next
Line2.X1 = 0
Line2.X2 = frmQuery.Width
'frmQuery.Picture = LoadPicture(App.Path + "\picture\sky.bmp")

End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
Unload Me
End Sub

Private Sub mnu11_Click()
On Error Resume Next
Unload Me
'Frmstart.Show
End Sub

Private Sub MNUQH1_Click()
On Error Resume Next
frmBaseInput.Show
End Sub

Private Sub MNUQH2_Click()
On Error Resume Next
frmGridModify.Show

End Sub

Private Sub MNUQH3_Click()
On Error Resume Next
frmCount.Show
End Sub