frmstudentselect.frm

这是别人的数据库课程设计

         Enabled         =   0   'False
         Height          =   300
         Left            =   990
         TabIndex        =   1
         Text            =   "txtName"
         Top             =   240
         Width           =   930
      End
      Begin MSComCtl2.DTPicker dtEnd 
         Height          =   255
         Left            =   3915
         TabIndex        =   13
         Top             =   675
         Width           =   1980
         _ExtentX        =   3493
         _ExtentY        =   450
         _Version        =   393216
         Enabled         =   0   'False
         CalendarBackColor=   12632256
         CalendarTitleBackColor=   12632256
         Format          =   23724032
         CurrentDate     =   37375
      End
      Begin MSComCtl2.DTPicker dtStart 
         Height          =   255
         Left            =   1800
         TabIndex        =   14
         Top             =   675
         Width           =   1440
         _ExtentX        =   2540
         _ExtentY        =   450
         _Version        =   393216
         Enabled         =   0   'False
         CalendarBackColor=   -2147483638
         Format          =   23724032
         CurrentDate     =   37375
      End
      Begin VB.Label Label2 
         Caption         =   "到："
         Height          =   255
         Left            =   3420
         TabIndex        =   16
         Top             =   720
         Width           =   375
      End
      Begin VB.Label Label1 
         Caption         =   "从："
         Height          =   210
         Left            =   1305
         TabIndex        =   15
         Top             =   720
         Width           =   420
      End
   End
End
Attribute VB_Name = "frmStudentSelect"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub FormInitialize()
txtName.Text = ""
txtStatusID.Text = ""

With cobSex
  .Clear
  
  .AddItem "男"
  .AddItem "女"
  .ListIndex = 0
  .Enabled = False
  
End With
Set adoRS = adoCon.Execute _
    ("select Min(birthday) as sTart,max(birthday) as sEnd from Student")
dtStart.Value = adoRS("sTart")
dtEnd.Value = adoRS("sEnd")

'设置民族
 Set adoRS = adoCon.Execute("select name from Nation")
With cobNation
  .Clear
  While Not adoRS.EOF
    .AddItem adoRS("name")
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  
End With
'设置专业
Set adoRS = adoCon.Execute("select name from Speciality")
With cobSpeciality
  .Clear
  While Not adoRS.EOF
    .AddItem adoRS("name")
    adoRS.MoveNext
    
   Wend
  .ListIndex = 0
  .Enabled = False
  
End With


End Sub

Private Sub ChkBirthday_Click()
If ChkBirthday.Value = 1 Then
   dtStart.Enabled = True
   dtEnd.Enabled = True
Else
   dtEnd.Enabled = False
   dtStart.Enabled = False
End If

End Sub

Private Sub ChkName_Click()
    If ChkName.Value = Checked Then
       txtName.BackColor = &HC0FFFF   '淡黄色
       txtName.Enabled = True
       txtName.SetFocus
    ElseIf ChkName.Value = Unchecked Then
       txtName.BackColor = &H8000000F  '灰色
       txtName.Enabled = False
    End If


End Sub

Private Sub chkNation_Click()
If chkNation.Value = Checked Then
    cobNation.BackColor = &HC0FFFF   '淡黄色
    cobNation.Enabled = True
    cobNation.SetFocus
 ElseIf chkNation.Value = Unchecked Then
    cobNation.BackColor = &H8000000F '灰色
    cobNation.Enabled = False
 End If
End Sub

Private Sub ChkSex_Click()
 If ChkSex.Value = Checked Then
    cobSex.BackColor = &HC0FFFF   '淡黄色
    cobSex.Enabled = True
    cobSex.SetFocus
 ElseIf ChkSex.Value = Unchecked Then
    cobSex.BackColor = &H8000000F '灰色
    cobSex.Enabled = False
 End If
End Sub

Private Sub ChkSpeciality_Click()
If ChkSpeciality.Value = Checked Then
    cobSpeciality.BackColor = &HC0FFFF   '淡黄色
    cobSpeciality.Enabled = True
    cobSpeciality.SetFocus
 ElseIf ChkSpeciality.Value = Unchecked Then
    cobSpeciality.BackColor = &H8000000F '灰色
    cobSpeciality.Enabled = False
 End If
End Sub

Private Sub chkStatusID_Click()
If chkStatusID.Value = Checked Then
    txtStatusID.BackColor = &HC0FFFF   '淡黄色
    txtStatusID.Enabled = True
    txtStatusID.SetFocus
 ElseIf chkStatusID.Value = Unchecked Then
    txtStatusID.BackColor = &H8000000F '灰色
    txtStatusID.Enabled = False
 End If
End Sub

Private Sub CmdExit_Click()
Unload Me

End Sub

Private Sub CmdFind_Click()
Dim sName, sSex, Sbirthday, sNation, sSpeciality, sStatusID As String
Dim SQL As String
If ChkName.Value = 1 Then  '是否对姓名查询
   sName = "Name like '" & Trim(txtName.Text) & "%'"
Else
   sName = "Name like '%'"
End If
'性别
If ChkSex.Value = 1 Then
   sSex = "Sex='" & cobSex.Text & "'"
Else
   sSex = "Sex in('男','女')"
End If
'出生日期
If ChkBirthday.Value = 1 Then
  Sbirthday = "Birthday Between '" & Format(dtStart.Value, "yyyy-mm-dd") & "' and '" & Format(dtEnd.Value, "yyyy-mm-dd") & "'"
Else
  Sbirthday = "Birthday like '%'"
End If



'专业
If ChkSpeciality.Value = 1 Then
  sSpeciality = "Speciality like '" & Trim(cobSpeciality.Text) & "%'"
  
Else
  sSpeciality = "Speciality like '%'"
  
End If

'民族
If chkNation.Value = 1 Then
  sNation = "Nation like '" & Trim(cobNation.Text) & "%'"
  
Else
  sNation = "Nation like '%'"
  
End If
'身份证号
If chkStatusID.Value = 1 Then
  sStatusID = " like '" & Trim(txtStatusID.Text) & "%'"
  
Else
  sStatusID = "StatusID like '%'"
  
End If

 '根据上面的子条件构造Select 语句
  SQL = "Select 学号=ID,姓名=Name,性别=Sex,出生日期=Birthday,"
  SQL = SQL & "民族=Nation,专业=Speciality,联系电话=Phone,"
  SQL = SQL & "身份证号=StatusID,电子信箱=Email,家长姓名=HouseName,"
  SQL = SQL & "家长电话=HousePhone,家庭地址=HouseAddress,邮政编码=PostCode,"
  SQL = SQL & "备注=Memo from Student where "
  SQL = SQL & sName
  SQL = SQL & " And " & sSex
  SQL = SQL & " And " & Sbirthday
  SQL = SQL & " And " & sNation
  SQL = SQL & " and " & sSpeciality
  SQL = SQL & " And " & sStatusID
 
 'MsgBox Sql
 MousePointer = vbHourglass
CmdShowAll.Enabled = False
CmdFind.Enabled = False
CmdExit.Enabled = False

AdoFind.ConnectionString = "provider=sqloledb.1;server=sfw007\lover;user id=sa;pwd=;database=MyDB"
AdoFind.RecordSource = SQL
AdoFind.Refresh
If AdoFind.Recordset.BOF And AdoFind.Recordset.EOF Then
  MsgBox "没有找到你需要的数据！", vbOKOnly + vbExclamation, "系统提示"
End If
MousePointer = Default
CmdFind.Enabled = True
CmdShowAll.Enabled = True
CmdExit.Enabled = True
End Sub


Private Sub CmdShowAll_Click()
Dim SQL As String
Dim sCon As String
  SQL = "Select 学号=ID,姓名=Name,性别=Sex,出生日期=Birthday,"
  SQL = SQL & "民族=Nation,专业=Speciality,联系电话=Phone,"
  SQL = SQL & "身份证号=StatusID,电子信箱=Email,家长姓名=HouseName,"
  SQL = SQL & "家长电话=HousePhone,家庭地址=HouseAddress,邮政编码=PostCode,"
  SQL = SQL & "备注=Memo from Student"
  sCon = "provider=sqloledb.1;Server=sfw007\lover;Uid=sa;Pwd=;Database=MyDB"
  AdoFind.ConnectionString = sCon
  AdoFind.RecordSource = SQL
  AdoFind.Refresh
  
End Sub

Private Sub Form_Load()
 '错误检测
On Error GoTo ErrMsg
'MDI子窗体居中
Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2 - 600
'调用填写控件过程
Call FormInitialize

'错误处理
ErrMsg:
  If Err.Number <> 0 Then
      MsgBox Err.Number & Err.Description, vbOKOnly + vbCritical, "出错提示"
     Exit Sub
  End If
End Sub