querystudent.frm

简单的数据库系统

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form querystudent 
   Caption         =   "Form1"
   ClientHeight    =   8595
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   10545
   Icon            =   "querystudent.frx":0000
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   MDIChild        =   -1  'True
   ScaleHeight     =   8595
   ScaleWidth      =   10545
   WindowState     =   2  'Maximized
   Begin VB.Data Data1 
      Caption         =   "Data1"
      Connect         =   "Access"
      DatabaseName    =   "F:\schoolgroup\school.mdb"
      DefaultCursorType=   0  '缺省游标
      DefaultType     =   2  '使用 ODBC
      Exclusive       =   0   'False
      Height          =   405
      Left            =   960
      Options         =   0
      ReadOnly        =   0   'False
      RecordsetType   =   1  'Dynaset
      RecordSource    =   "student"
      Top             =   7560
      Width           =   1695
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Bindings        =   "querystudent.frx":0442
      Height          =   4215
      Left            =   240
      TabIndex        =   18
      Top             =   2880
      Width           =   11295
      _ExtentX        =   19923
      _ExtentY        =   7435
      _Version        =   393216
      BackColor       =   16777215
      ForeColor       =   16711680
      ForeColorSel    =   8454143
      GridLines       =   2
      AllowUserResizing=   3
      FormatString    =   ""
   End
   Begin VB.TextBox Text6 
      Height          =   495
      Left            =   9000
      TabIndex        =   16
      Top             =   1920
      Width           =   2295
   End
   Begin VB.CommandButton Command4 
      Caption         =   "关闭(&C)"
      Height          =   495
      Left            =   4440
      TabIndex        =   15
      Top             =   2040
      Width           =   1215
   End
   Begin VB.CommandButton Command3 
      Caption         =   "打印预览(&P)"
      Height          =   495
      Left            =   3120
      TabIndex        =   14
      Top             =   2040
      Width           =   1215
   End
   Begin VB.CommandButton Command2 
      Caption         =   "清除(&E)"
      Height          =   495
      Left            =   1800
      TabIndex        =   13
      Top             =   2040
      Width           =   1215
   End
   Begin VB.CommandButton Command1 
      Caption         =   "查询(&Q)"
      Height          =   495
      Left            =   480
      TabIndex        =   12
      Top             =   2040
      Width           =   1215
   End
   Begin VB.Frame Frame2 
      Height          =   855
      Left            =   240
      TabIndex        =   11
      Top             =   1800
      Width           =   5775
   End
   Begin VB.TextBox Text5 
      Height          =   495
      Left            =   9000
      TabIndex        =   10
      Top             =   960
      Width           =   2295
   End
   Begin VB.TextBox Text4 
      Height          =   495
      Left            =   6120
      TabIndex        =   9
      Top             =   960
      Width           =   2655
   End
   Begin VB.TextBox Text3 
      Height          =   495
      Left            =   3960
      TabIndex        =   8
      Top             =   960
      Width           =   1935
   End
   Begin VB.TextBox Text2 
      Height          =   495
      Left            =   2160
      TabIndex        =   7
      Top             =   960
      Width           =   1575
   End
   Begin VB.TextBox Text1 
      Height          =   495
      Left            =   480
      TabIndex        =   6
      Top             =   960
      Width           =   1455
   End
   Begin VB.OptionButton Option1 
      Caption         =   "按宿舍号"
      Height          =   495
      Index           =   4
      Left            =   9000
      TabIndex        =   5
      Top             =   360
      Width           =   1215
   End
   Begin VB.OptionButton Option1 
      Caption         =   "按家庭住址"
      Height          =   495
      Index           =   3
      Left            =   6120
      TabIndex        =   4
      Top             =   360
      Width           =   1215
   End
   Begin VB.OptionButton Option1 
      Caption         =   "按学号"
      Height          =   495
      Index           =   2
      Left            =   3960
      TabIndex        =   3
      Top             =   360
      Width           =   1215
   End
   Begin VB.OptionButton Option1 
      Caption         =   "按姓名"
      Height          =   495
      Index           =   1
      Left            =   2160
      TabIndex        =   2
      Top             =   360
      Width           =   1215
   End
   Begin VB.OptionButton Option1 
      Caption         =   "按班级"
      Height          =   495
      Index           =   0
      Left            =   480
      TabIndex        =   1
      Top             =   360
      Width           =   1215
   End
   Begin VB.Frame Frame1 
      Height          =   1575
      Left            =   240
      TabIndex        =   0
      Top             =   120
      Width           =   11295
   End
   Begin VB.Label Label1 
      Caption         =   "记录共数"
      Height          =   495
      Left            =   8040
      TabIndex        =   17
      Top             =   2160
      Width           =   855
   End
End
Attribute VB_Name = "querystudent"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
 
Private Sub Command1_Click()
    MSFlexGrid1.Clear 'use to clear old records
    Dim dd(5) As Boolean
    UserName = ""
    txtSQL = "select * from student where "
    If Option1(0).Value Then
       Text2.Text = ""
       Text3.Text = ""
       Text4.Text = ""
       Text5.Text = ""
        If Trim(Text1.Text) = " " Then
            sMeg = "班级不能为空"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
             Text1.SetFocus
            Exit Sub
        Else
            dd(0) = True
            If dd(0) Then
             UserName = Trim(Text1.Text) '
             txtSQL = txtSQL & "班级 = '" & UserName & "' "
            ElseIf UserName <> " " Then
             txtSQL = txtSQL & "班级 = '" & UserName & "%' "
            End If
        End If
     ElseIf Option1(1).Value Then
         Text1.Text = ""
         Text3.Text = ""
         Text4.Text = ""
         Text5.Text = ""
         If Trim(Text2.Text) = "" Then
            sMeg = "姓名不能为空"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
            Text2.SetFocus
            Exit Sub
         Else
            dd(1) = True
            If dd(1) Then
               txtSQL = txtSQL & " 姓名 = '" & Text2.Text & "%'Or 姓名 like '%" & Text2.Text & "'"
            Else
             ' txtSQL = txtSQL & " 姓名 like '" & Text2.Text & "%'" Or " 姓名 like '%" & Text2.Text & "'"
            End If
        End If
    'End If
'----------------------------
    ElseIf Option1(2).Value Then
           Text1.Text = ""
           Text2.Text = ""
           Text4.Text = ""
           Text5.Text = ""
          If Trim(Text3.Text) = " " Then
             sMeg = "学号不能为空"
             MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
             Text3.SetFocus
             Exit Sub
          Else
             dd(2) = True
             If dd(2) Then
                 txtSQL = txtSQL & " 学号 = '" & Text3.Text & "'"
             Else
                 txtSQL = txtSQL & " 学号 like '" & Text3.Text & "%'or 学号 like '%" & Text2.Text & "'"
             End If
          End If
     ElseIf Option1(3).Value Then
         Text1.Text = ""
         Text2.Text = ""
         Text3.Text = ""
         Text5.Text = ""
        If Trim(Text4.Text) = " " Then
             sMeg = "家庭住址不能为空"
             MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
             Text4.SetFocus
             Exit Sub
        Else
         dd(3) = True
         If dd(3) Then
           txtSQL = txtSQL & " 家庭住址 = '" & Text4.Text & "  'or  家庭住址 like '%" & Text4.Text & " ' "
         Else
         ' txtSQL = txtSQL & " 家庭住址 like  '" & Text4.Text & "%'"
         End If
       End If
      ElseIf Option1(4).Value Then
          Text1.Text = ""
          Text2.Text = ""
          Text3.Text = ""
          Text4.Text = ""
         If Trim(Text5.Text) = "" Then
            sMeg = "宿舍号不能为空"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
            Text5.SetFocus
            Exit Sub
         Else
            'xuehao is number ?
             If Not IsNumeric(Trim(Text5.Text)) Then
                MsgBox "请输入数字！", vbOKOnly + vbExclamation, "警告"
                Exit Sub
                Text5.Text = " "
                Text5.SetFocus
             End If
             dd(4) = True
             If dd(4) Then
                 txtSQL = txtSQL & " 宿舍号 = '" & Text5.Text & "'"
             Else
                txtSQL = txtSQL & " 宿舍号 like '" & Text5.Text & "%'"
             End If
         End If
    End If
        
     If Not (dd(0) Or dd(1) Or dd(2) Or dd(3) Or dd(4)) Then
          MsgBox "请设置查询条件！", vbOKOnly + vbExclamation, "警告"
          Exit Sub
     End If
     txtSQL = txtSQL & " order by 学号 "
     Set dream = ExecuteSQL(txtSQL, Msgtext)
     If dream.EOF = True Then        'exsit  record ?
        MsgBox "没有此记录！", 48, "警告"
        Text1.Text = ""
        Text2.Text = ""
        Text3.Text = ""
        Text4.Text = ""
        Text5.Text = ""
     Else
     With MSFlexGrid1
       .Rows = 1
     .TextMatrix(0, 1) = "学号"
     .TextMatrix(0, 2) = "姓名"
     .TextMatrix(0, 3) = "性别"
     .TextMatrix(0, 4) = "民族"
     .TextMatrix(0, 5) = "团员与否"
     .TextMatrix(0, 6) = "出生日期"
     .TextMatrix(0, 7) = "入校日期"
     .TextMatrix(0, 8) = "班级"
     .TextMatrix(0, 9) = "宿舍号"
     .TextMatrix(0, 10) = "宿舍电话号码"
     .TextMatrix(0, 11) = "家长姓名"
     .TextMatrix(0, 12) = "联系电话号码"
     .TextMatrix(0, 13) = "中考成绩"
     .TextMatrix(0, 14) = "家庭住址"
     .TextMatrix(0, 15) = "备注"
      
        Do While Not dream.EOF
            .Rows = .Rows + 1
            .CellAlignment = 0
            .TextMatrix(.Rows - 1, 1) = dream.Fields(0)
            .TextMatrix(.Rows - 1, 2) = dream.Fields(1)
            .TextMatrix(.Rows - 1, 3) = dream.Fields(2)
            .TextMatrix(.Rows - 1, 4) = dream.Fields(3)
            .TextMatrix(.Rows - 1, 5) = dream.Fields(4)
            .TextMatrix(.Rows - 1, 6) = Format(dream.Fields(5), "yyyy-mm-dd")
            .TextMatrix(.Rows - 1, 7) = Format(dream.Fields(6), "yyyy-mm-dd")
            .TextMatrix(.Rows - 1, 8) = dream.Fields(7)
            .TextMatrix(.Rows - 1, 9) = dream.Fields(8)
            .TextMatrix(.Rows - 1, 10) = dream.Fields(9)
            .TextMatrix(.Rows - 1, 11) = dream.Fields(10)
            .TextMatrix(.Rows - 1, 12) = dream.Fields(11)
            .TextMatrix(.Rows - 1, 13) = dream.Fields(12)
            .TextMatrix(.Rows - 1, 14) = dream.Fields(13)
            .TextMatrix(.Rows - 1, 15) = dream.Fields(14)
             dream.MoveNext
      Loop
    End With
  End If
    dream.Close
    
End Sub

Private Sub Command2_Click()
    Data1.Recordset.Sort = "学号 "
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    Text5.Text = ""
End Sub

Private Sub Command3_Click()

Dim i As Integer, j As Integer
Dim slapp As Excel.Application
Dim row, col As Integer
Screen.MousePointer = 11
Set slapp = New Excel.Application
slapp.Visible = True '
Set xlbook = slapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)

 With slapp
    slapp.Range("A1").Value = "学号"
    slapp.Range("B1").Value = "姓名"
    slapp.Range("C1").Value = "性别"
    slapp.Range("D1").Value = "团员与否"
    slapp.Range("E1").Value = "出生日期"
    slapp.Range("F1").Value = "入校日期"
    slapp.Range("G1").Value = "班级"
    slapp.Range("H1").Value = "宿舍"
    slapp.Range("I1").Value = "宿舍电话号码"
    slapp.Range("J1").Value = "家长姓名"
    slapp.Range("K1").Value = "联系电话号码"
    slapp.Range("L1").Value = "中考成绩"
    slapp.Range("M1").Value = "家庭住址"
    slapp.Range("N1").Value = "备注"
End With
Data1.Recordset.MoveFirst
    i = 1
    Do While Data1.Recordset.EOF = False
      For j = 0 To 13 'txtnumber '8-----txtnumber
         If IsNull(MSFlexGrid1.Text) = False Then
          If IsDate(Data1.Recordset(j)) Then
              xlsheet.Cells(i + 1, j + 1) = Format(Data1.Recordset(j), "yyyy-mm-dd")
          Else
              xlsheet.Cells(i + 1, j + 1) = Data1.Recordset(j)
          End If
         End If
      Next j
     Data1.Recordset.MoveNext
      i = i + 1
   Loop
 Screen.MousePointer = 0
End Sub


Private Sub Command4_Click()
Unload Me
End Sub



Private Sub Form_Load()
Data1.Visible = False

'showtitle
 'txtSQL = "select *from student order by 学号"
' showData
End Sub


Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
   Call Command1_Click
 End If
End Sub


Private Sub Text2_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
   Call Command1_Click
 End If
End Sub



Private Sub Text3_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
   Call Command1_Click
 End If
End Sub



Private Sub Text4_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
   Call Command1_Click
 End If
End Sub



Private Sub Text5_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
   Call Command1_Click
 End If
End Sub


Private Sub Text6_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
   Call Command1_Click
 End If
End Sub