frmduty.frm

该系统的最终用户是访问本系统的工作人员

      Caption         =   "（精确查询）"
      Height          =   255
      Left            =   8160
      TabIndex        =   3
      Top             =   840
      Width           =   1095
   End
   Begin VB.Label Label4 
      BackStyle       =   0  'Transparent
      Caption         =   "寝室号"
      Height          =   255
      Left            =   6240
      TabIndex        =   2
      Top             =   840
      Width           =   615
   End
   Begin VB.Label Label1 
      BackStyle       =   0  'Transparent
      Caption         =   "查询条件："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   120
      TabIndex        =   1
      Top             =   840
      Width           =   1335
   End
End
Attribute VB_Name = "frmduty"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Option Explicit
Dim txtSQL As String



Private Sub Data1_Validate(Action As Integer, Save As Integer)

End Sub





Private Sub cominquire_Click()
' Dim txtSQL As String
    Dim MsgText As String
    Dim dd(5) As Boolean
    Dim mrc As ADODB.Recordset
    Dim rs As Boolean
    Dim sMeg As String
    rs = True
       
    txtSQL = "select * from hygiene_check where "
    If Check1.Value Then
        If Trim(Combo1.Text) = "" Then
            sMeg = "栋号不能为空"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
            Combo1.SetFocus
            Exit Sub
        Else
            If Not IsNumeric(Trim(Combo1.Text)) Then
                MsgBox "请输入数字！", vbOKOnly + vbExclamation, "警告"
                Exit Sub
                Combo1.SetFocus
            End If
            dd(0) = True
            txtSQL = txtSQL & "栋号 = '" & Val(Trim(Combo1.Text)) & "'"
        End If
    End If
    
    If Check2.Value Then
        If Trim(Combo2.Text) = "" Then
            sMeg = "栋号和寝室号不能为空"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
            Combo2.SetFocus
            Exit Sub
        Else
            dd(1) = True
            If dd(0) Then
                txtSQL = txtSQL & "and 寝室号= '" & Combo2.Text & "'"
            Else
                txtSQL = txtSQL & " 寝室号= '" & Combo2.Text & "'"
            End If
        End If
    End If
    
    If Check3.Value Then
        If Trim(txttime.Text) = "" Then
            sMeg = "请输入时间"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
            txttime.SetFocus
            Exit Sub
        Else
            dd(2) = True
            If dd(0) Or dd(1) Then
                txtSQL = txtSQL & "and 日期= '" & txttime.Text & "'"
            Else
                txtSQL = txtSQL & " 日期 = '" & txttime.Text & "'"
            End If
        End If
       If Not IsDate(txttime.Text) Then
           MsgBox "输入日期应输入日期格式(yyyy-mm-dd)!", vbOKOnly + vbExclamation, "警告"
           Exit Sub
           txttime.SetFocus
       Else
          txttime = Format(txttime, "yyyy-mm-dd")
       End If
    
    End If
    
    If Check4.Value Then
        If Trim(Combo7.Text) = "" Then
           sMeg = "请选择分数"
           MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
           Combo7.SetFocus
           Exit Sub
        Else
            dd(4) = True
            
            If dd(0) Or dd(1) Then
               txtSQL = txtSQL & " and 平均分='" & Combo7.Text & "'"
            Else
               txtSQL = txtSQL & "平均分 =' " & Combo7.Text & " '"
            End If

            
            
            If Not IsNumeric(Trim(Combo7.Text)) Then
                MsgBox "请输入具体数字！", vbOKOnly + vbExclamation, "警告"
                Combo7.SetFocus
                rs = False
            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
    
    If rs = True Then
    
       txtSQL = txtSQL & " order by 日期,栋号, 寝室号"
       Set mrc = ExecuteSQL(txtSQL, MsgText)
       If mrc.RecordCount > 0 Then
            Command1.Enabled = True
        Else
            Command1.Enabled = False
        End If
            
       With myflexgrid
           .Rows = 2
           .CellAlignment = 4
           .TextMatrix(1, 0) = "日期"
           .TextMatrix(1, 1) = "栋号"
           .TextMatrix(1, 2) = "寝室号"
           .TextMatrix(1, 3) = "评分1"
           .TextMatrix(1, 4) = "评分2"
           .TextMatrix(1, 5) = "评分3"
           .TextMatrix(1, 6) = "总分"
           .TextMatrix(1, 7) = "平均分"
   
     
        
           
        Do While Not mrc.EOF
            
          
            .Rows = .Rows + 1
            .CellAlignment = 4
            .TextMatrix(.Rows - 1, 0) = Format(mrc.Fields(0), "yyyy-mm-dd")
            .TextMatrix(.Rows - 1, 1) = mrc.Fields(1)
            .TextMatrix(.Rows - 1, 2) = mrc.Fields(2)
            .TextMatrix(.Rows - 1, 3) = mrc.Fields(3)
            .TextMatrix(.Rows - 1, 4) = mrc.Fields(4)
            .TextMatrix(.Rows - 1, 5) = mrc.Fields(5)
            .TextMatrix(.Rows - 1, 6) = mrc.Fields(6)
            .TextMatrix(.Rows - 1, 7) = mrc.Fields(7)
          
            mrc.MoveNext
         Loop
        
       End With
    
    mrc.Close
   End If
End Sub



Private Sub Command1_Click()
Dim i As Integer, j As Integer
    'Dim txtSQL As String
    Dim MsgText As String
    Dim expworkbook As Excel.Workbook
    Dim expexcel As Excel.Application
    Dim mrc As ADODB.Recordset
    Set expexcel = New Excel.Application
    expexcel.Visible = True
    expexcel.SheetsInNewWorkbook = 1
    Set expworkbook = expexcel.Workbooks.Add
    Call ConnectString
   ' txtSQL = "select * from hygiene_check "
     
   'myflexgrid
   ' ConnectDB
    'Set mrc = mrc.Execute("select * from hygiene_check")
     Set mrc = ExecuteSQL(txtSQL, MsgText)

    
    For i = 0 To mrc.Fields.Count - 1
        expexcel.Cells(2, i + 1) = mrc.Fields(i).Name
    Next
    mrc.MoveFirst
    For j = 1 To myflexgrid.Rows
        For i = 0 To mrc.Fields.Count - 1
            expexcel.Cells(j + 2, i + 1) = CStr(mrc.Fields(i))
        Next
        mrc.MoveNext
        If mrc.EOF Then Exit For
    Next
        
    Set expexcel = Nothing
End Sub

Private Sub Command2_Click()
Unload Me
End Sub

Private Sub Text2_Change()



End Sub

Private Sub UpDown1_Change()

End Sub


Private Sub SubWizard1_GotFocus()

End Sub

Private Sub Text1_Change()

End Sub

Private Sub txtdong_Change()

End Sub