form1.frm

个人写的在我公司里用着的查找工具,更改数据库名,和我的SQL语句也就可以查你们的数据了,有电子表格输出功能

Private Sub Option17_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option18_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option19_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option2_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option20_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option21_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option22_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option23_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option24_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option25_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option26_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option27_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option28_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option29_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option3_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option30_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option31_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option32_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option34_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option35_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option38_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option4_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option5_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option6_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option7_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option8_Click()
Text1.SetFocus
Text2.Visible = False
End Sub

Private Sub Option9_Click()
Text1.SetFocus
Text2.Visible = False
End Sub



Private Sub SSTab1_Click(PreviousTab As Integer)
Text2.Visible = False
Option1.Value = False
Option2.Value = False
Option3.Value = False
Option4.Value = False
Option5.Value = False
Option6.Value = False
Option7.Value = False
Option8.Value = False
Option9.Value = False
Option10.Value = False
Option11.Value = False
Option12.Value = False
Option13.Value = False
Option14.Value = False
Option15.Value = False
Option16.Value = False
Option17.Value = False
Option18.Value = False
Option19.Value = False
Option20.Value = False
Option21.Value = False
Option22.Value = False
Option23.Value = False
Option24.Value = False
Option25.Value = False
Option26.Value = False
Option27.Value = False
Option28.Value = False
Option29.Value = False
Option30.Value = False
Option31.Value = False
Option32.Value = False
Option33.Value = False
Option34.Value = False
Option35.Value = False
Option36.Value = False
Option37.Value = False
Option38.Value = False
End Sub
Private Sub Text1_Click()
Text2.Visible = False
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
   Dim a, b, c, d, e, f, j, o
  
   If KeyAscii <> 13 Then
        Exit Sub
    Else
      
    End If
    
     
  
    
   MSFlexGrid1.Visible = True
     On Error GoTo err1
   Select Case True
        Case Option1.Value = True
            sql = "select * from sn_asse where ssn in('" & Text1.Text & "')"
        Case Option2.Value = True
            sql = "select * from pallet where label='" & Text1.Text & "'"
        Case Option3.Value = True
            sql = "select cartonid,ssn,line from carton where ssn in('" & Text1.Text & "')"
        Case Option4.Value = True
            sql = "select * from trn_log where ssn='" & Text1.Text & "' order by trn_time"
        Case Option5.Value = True
            sql = "select * from sn_pre where csn='" & Text1.Text & "'"
        Case Option6.Value = True
            sql = "select * from pallet where p_id='" & Text1.Text & "'"
        Case Option7.Value = True
            sql = "select * from sn_asse where wo='" & Text1.Text & "'"
            Label2.Caption = "Please Wait !"
        Case Option8.Value = True
            sql = "select * from trn_log where line='" & InputBox("please input Line Name !", , "B5") & "'" & "and trn_time>'" & InputBox("Please Input From time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "08:00:00") & "' and trn_time<'" & InputBox("Please Input To time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "20:00:00") & "' and stage='" & InputBox("Please Input Stage Name!", , "M") & "' order by trn_time"
        Case Option9.Value = True
            sql = "select * from trn_log where station='" & InputBox("please input Equipment Number!", , "MS1241339") & "'" & "and trn_time>'" & InputBox("Please Input From time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "08:00:00") & "' and trn_time<'" & InputBox("Please Input To time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "20:00:00") & "' order by trn_time"
        Case Option10.Value = True
            sql = "select * from op_work where line='" & Text1.Text & "'and end_time is null order by logdate"
        Case Option11.Value = True
            Dim user, use, use1, use2, pwd, pw, pw1, pw2 As String
            user = InputBox("Please Input USER !", , "USRE")
            pwd = InputBox("Please Input Passeword !", , "******")
            use = "200306092"
            pw = "BAISHGYYGY"
            use1 = "sfcs"
            pw1 = "acer"
            use2 = "200306294"
            pw2 = "LASTER"
            use3 = "200305154"
            pw3 = "baiihc"
            If user = use Or user = use1 Or user = use2 Or user = use3 Then
                If pwd = pw Or pwd = pw1 Or pwd = pw2 Or pwd = pw3 Then
                    sql = "update op_work set end_time='" & Format(Date, "yyyymmdd") & Format(Time, "hh:mm:ss") & "'" & " where op_id='" & InputBox("please input Operation !", , "T0603061") & "' and end_time is null"
                              conn.BeginTrans
                              conn.Execute sql
                              conn.CommitTrans
                                Exit Sub
                Else
                    Image3.Visible = False
                    Label2.BackColor = &HFF&
                    Label2.Caption = "Passeword Wrong ! @_@"
                    Exit Sub
             End If
             Else
             Image3.Visible = False
             Label2.BackColor = &HFF&
             Label2.Caption = "USER Wrong ! @_@"
              Exit Sub
            End If
         Case Option12.Value = True
            user = InputBox("Please Input USER !")
            pwd = InputBox("Please Input Passeword !")
            use = "sfcs"
            pw = "acer"
            If user = use Then
                If pwd = pw Then
                   sql = InputBox("Please Input Command!")
                 Else
                    Image3.Visible = False
                    Label2.BackColor = &HFF&
                    Label2.Caption = "Passeword Wrong ! @_@"
                    Exit Sub
             End If
             Else
             Image3.Visible = False
             Label2.BackColor = &HFF&
             Label2.Caption = "USER Wrong ! @_@"
              Exit Sub
            End If
          Case Option13.Value = True
                sql = "select * from accessorysn where ssn in('" & Text1.Text & "')"
          Case Option14.Value = True
                sql = "select count(ssn) from accessorysn where wo='" & Text1.Text & "'"
          Case Option15.Value = True
                sql = "select * from accessorysn_pre where accessorycsn='" & Text1.Text & "'"
          Case Option16.Value = True
                sql = "select * from accessorysn where wo='" & Text1.Text & "' and csn02 is null"
          Case Option17.Value = True
                sql = "select * from sn_asse where ssn in('" & Text1.Text & "')"
          Case Option18.Value = True
                sql = "select * from hddmcarton where ssn in('" & Text1.Text & "')"
          Case Option19.Value = True
                sql = "select * from trn_log where ssn='" & Text1.Text & "' order by trn_time"
          Case Option20.Value = True
                sql = "select * from hddmpallet where label='" & Text1.Text & "'"
          Case Option21.Value = True
                sql = "select * from hddmtestlog where ssn in('" & Text1.Text & "')"
          Case Option22.Value = True
                sql = "select * from hddmcarton where cartonid='" & Text1.Text & "'"
          Case Option23.Value = True
                sql = "select ssn,spn,model,line,section,stage,start_d from sn_asse where wo='" & Text1.Text & "' order by section"
          Case Option24.Value = True
                 sql = "select * from hddmcarton a,hddmpallet b where a.cartonid=b.label and b.palletid='" & Text1.Text & "'"
          Case Option25.Value = True
                sql = "select * from accessorypallet where ssn in('" & Text1.Text & "')"
          Case Option26.Value = True
                
          Case Option27.Value = True
                sql = "select * from accessorypallet where palletid='" & Text1.Text & "'"
          Case Option28.Value = True
                
          Case Option29.Value = True
                sql = "select * from sn_asse where wo='" & Text1.Text & "' and section='X'"
          Case Option30.Value = True
                sql = "select * from carton where cartonid='" & Text1.Text & "'"
          Case Option31.Value = True
                sql = "select * from sn_asse where wo='" & Text1.Text & "' and section='A'"
          Case Option32.Value = True
                sql = "select * from defect_cpn where ssn in('" & Text1.Text & "')"
          Case Option33.Value = True
                sql = "select * from trn_log where stage='F' and trn_data='" & Text1.Text & "'"
          Case Option34.Value = True
                sql = "select pn SKU,count(distinct(ssn)) Total from trn_log where " & "trn_time>'" & InputBox("Please Input From time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "08:00:00") & "' and trn_time<'" & InputBox("Please Input To time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "20:00:00") & "' and stage='" & InputBox("Please Input Stage Name!", , "M") & "' group by pn"
          Case Option35.Value = True
                sql = "select wo,count(distinct(ssn)) Total from trn_log where wo in('" & Text1.Text & "') and stage='" & InputBox("Please Input Stage Name!", , "M") & "' group by wo"
           Case Option36.Value = True
                sql = "select * FROM SN_ASSE_PR WHERE SSN in('" & Text1.Text & "')"
           Case Option37.Value = True
                sql = "select * FROM trn_log_PR WHERE SSN in('" & Text1.Text & "')"
           Case Option38.Value = True
                sql = "select pn PN,count(distinct(a.ssn)) total from accessorysn a,accessorywo b,accessorypallet c where a.wo=b.wo  and a.ssn=c.ssn and c.trndate between to_date('" & InputBox("Please Input From time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "08:00:00") & "','yyyymmddhh24:mi:ss') and to_date('" & InputBox("Please Input To time !" & Chr(13) & "2006010108:00:00", , Format(Date, "yyyymmdd") & "20:00:00") & "','yyyymmddhh24:mi:ss') group by pn"
                
                
                
    End Select
   MSFlexGrid1.Clear
    Dim rs As New ADODB.Recordset
    Dim ssn As Integer
    Dim Width
    On Error GoTo err2
    'Shell App.Path & "\1.exe"
    Form5.Show
    
    DoEvents
    rs.Open sql, conn, adOpenDynamic
    a = rs.Fields.Count
    b = rs.RecordCount
    MSFlexGrid1.Rows = b + 2
    MSFlexGrid1.Cols = a + 1
    MSFlexGrid1.ColWidth(0) = 420
    MSFlexGrid1.TextMatrix(0, 0) = "^_^"
    
    If b <= 0 Then
        Image3.Visible = True
    Else
        Image3.Visible = False
        Label2.Caption = "   ^_^    "
    End If
    For z = 0 To a          '计算出行的名子出来
    
      MSFlexGrid1.TextMatrix(0, z + 1) = (rs(z).Name)
    Next z
err2:
    If a <> "" Then     '算出序号出来
       Do Until rs.EOF         '把内容填进去
           v = v + 1
           c = c + 1
             For z = 0 To a
             
                If z = a Then
                 Exit For
                End If
              MSFlexGrid1.TextMatrix(v, z + 1) = check(rs(z), "")
            Next z
            rs.MoveNext
            MSFlexGrid1.TextMatrix(c, 0) = c
    Loop
     For z = 0 To a         '根据内容计算宽度
       
                If z = a + 1 Then
                   Exit For
                End If
                For o = 0 To b + 1
                   t = Len(MSFlexGrid1.TextMatrix(o, z))
                If t > p Then
                    p = t
                End If
                Next o
             MSFlexGrid1.ColWidth(z) = p * 145
               p = 0
      
      Next z
   rs.Close
   End If
  'Shell "ntsd -c q -pn 1.exe"
Unload Form5
sql = ""
SendKeys "{home}+{end}"
Text3.Text = ""
time1 = 1
err1:

End Sub
Sub Timer1_Timer()
   time1 = time1 + 1
   Form1.Caption = "TonyMa                                             " & Time
   If time1 = 60 * 5 Then
   Unload Form1
   End If
End Sub
Private Sub Text2_Click()
Text1.Text = Text2.Text
Text1.SetFocus
SendKeys "{home}+{end}"
End Sub