frmsearch.frm

用于电子行业打印复杂报表格式和不干胶标签

      Begin VB.ComboBox Cmbzd2 
         Height          =   300
         Left            =   180
         TabIndex        =   9
         Text            =   "Cmbzd2"
         Top             =   1230
         Width           =   1500
      End
      Begin VB.ComboBox Cmbzd1 
         Height          =   300
         Left            =   180
         TabIndex        =   7
         Text            =   "Cmbzd1"
         Top             =   450
         Width           =   1500
      End
      Begin VB.ComboBox Cmbbjz1 
         Height          =   300
         Left            =   4080
         TabIndex        =   6
         Text            =   "Cmbbjz1"
         Top             =   450
         Width           =   1500
      End
      Begin VB.Label Label4 
         Caption         =   "比较值"
         ForeColor       =   &H00FF0000&
         Height          =   240
         Left            =   4530
         TabIndex        =   3
         Top             =   225
         Width           =   690
      End
      Begin VB.Label Label3 
         Caption         =   "关系"
         ForeColor       =   &H00FF0000&
         Height          =   240
         Left            =   2700
         TabIndex        =   2
         Top             =   255
         Width           =   375
      End
      Begin VB.Label Label2 
         Caption         =   "字段"
         ForeColor       =   &H00FF0000&
         Height          =   210
         Left            =   705
         TabIndex        =   1
         Top             =   240
         Width           =   375
      End
   End
End
Attribute VB_Name = "frmSearch"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Dim cn As ADODB.Connection
Dim rsTemp As ADODB.Recordset
Dim fldname As Field
Dim tmp1, tmp2, tmp3, tmp4 As String
Dim strFind1, strFind2, strFind3, strFind4, strFind5 As String
 
Private Sub Form_Load()
  '将所选择表的所有字段加入到fldlist1,Cmbzd控件
  '**初始化ADO对象，用于储存操作记录，还有待于优化
  Set rsTemp = New ADODB.Recordset
  rsTemp.CursorType = adOpenKeyset
  rsTemp.LockType = adLockOptimistic
  rsTemp.Open "Select Top 1 * From " & tabname, cnSys  '为提高速度，只取前1条记录用于填充字段
  
  For Each fldname In rsTemp.Fields
    Cmbzd1.AddItem fldname.Name
    Cmbzd2.AddItem fldname.Name
    Cmbzd3.AddItem fldname.Name
    Cmbzd4.AddItem fldname.Name
    Cmbzd5.AddItem fldname.Name
  Next
  Cmbzd1.RemoveItem (0)    '清除第一字段
  Cmbzd2.RemoveItem (0)
  Cmbzd3.RemoveItem (0)
  Cmbzd4.RemoveItem (0)
  Cmbzd5.RemoveItem (0)
  'rsTemp.Close
  
  OptAnd1 = True
  OptAnd2 = True
  OptAnd3 = True
  OptAnd4 = True
  Cmbzd1.Text = Cmbzd1.List(0)
  Cmbgx1.Text = "="
  Cmbbjz1.Text = ""
  Cmbzd2.Text = Cmbzd2.List(0)
  Cmbgx2.Text = "="
  Cmbbjz2.Text = ""
  Cmbzd3.Text = Cmbzd3.List(0)
  Cmbgx3.Text = "="
  Cmbbjz3.Text = ""
  Cmbzd4.Text = Cmbzd4.List(0)
  Cmbgx4.Text = "="
  Cmbbjz4.Text = ""
  Cmbzd5.Text = Cmbzd5.List(0)
  Cmbgx5.Text = "="
  Cmbbjz5.Text = ""
End Sub

Private Sub Cmbbjz1_DropDown()  '将条件值予置组合列表框
  Cmbbjz1.Clear
  rsTemp.Close  '仅取前100条记录作为示例
  rsTemp.Open "Select Distinct Top 100 " & Cmbzd1.Text & " from " & tabname, cnSys  '用Open打开表
  Do While Not rsTemp.EOF
    If Not IsNull(rsTemp(0)) Then  '这里用索引值，不用名称
      Cmbbjz1.AddItem CStr(rsTemp(0))
    End If
    rsTemp.MoveNext
  Loop
End Sub

Private Sub Cmbbjz2_DropDown()  '将条件值予置组合列表框
  Cmbbjz2.Clear
  rsTemp.Close
  rsTemp.Open "Select Distinct Top 100 " & Cmbzd2.Text & " from " & tabname, cnSys  '用Open打开表
  Do While Not rsTemp.EOF
    If Not IsNull(rsTemp(0)) Then
      Cmbbjz2.AddItem CStr(rsTemp(0))
    End If
    rsTemp.MoveNext
  Loop
End Sub

Private Sub Cmbbjz3_DropDown()  '将条件值予置组合列表框
  Cmbbjz3.Clear
  rsTemp.Close
  rsTemp.Open "Select Distinct Top 100 " & Cmbzd3.Text & " from " & tabname, cnSys  '用Open打开表
  Do While Not rsTemp.EOF
    If Not IsNull(rsTemp(0)) Then
      Cmbbjz3.AddItem CStr(rsTemp(0))
    End If
    rsTemp.MoveNext
  Loop
End Sub

Private Sub Cmbbjz4_DropDown()  '将条件值予置组合列表框
  Cmbbjz4.Clear
  rsTemp.Close
  rsTemp.Open "Select Distinct Top 100 " & Cmbzd3.Text & " from " & tabname, cnSys  '用Open打开表
  Do While Not rsTemp.EOF
    If Not IsNull(rsTemp(0)) Then
      Cmbbjz4.AddItem CStr(rsTemp(0))
    End If
    rsTemp.MoveNext
  Loop
End Sub

Private Sub Cmbbjz5_DropDown()  '将条件值予置组合列表框
  Cmbbjz5.Clear
  rsTemp.Close
  rsTemp.Open "Select Distinct Top 100 " & Cmbzd3.Text & " from " & tabname, cnSys  '用Open打开表
  Do While Not rsTemp.EOF
    If Not IsNull(rsTemp(0)) Then
      Cmbbjz5.AddItem CStr(rsTemp(0))
    End If
    rsTemp.MoveNext
  Loop
End Sub

Private Sub cmdOK_Click() '"确定"按钮事件
  rsTemp.Close
  rsTemp.Open "Select * from " & tabname, cnSys  '用Open打开表
  
  strFind1 = ""    '置初值
  strFind2 = ""
  strFind3 = ""
  strFind4 = ""
  strFind5 = ""
  Text1.Text = "Select * From " & tabname & " Where"  '获得条件语句
  If Cmbbjz1.Text <> "" Then strFind1 = datatype(Cmbzd1.Text, Cmbgx1.Text, Cmbbjz1.Text)
  If Cmbbjz2.Text <> "" Then strFind2 = datatype(Cmbzd2.Text, Cmbgx2.Text, Cmbbjz2.Text)
  If Cmbbjz3.Text <> "" Then strFind3 = datatype(Cmbzd3.Text, Cmbgx3.Text, Cmbbjz3.Text)
  If Cmbbjz4.Text <> "" Then strFind4 = datatype(Cmbzd4.Text, Cmbgx4.Text, Cmbbjz4.Text)
  If Cmbbjz5.Text <> "" Then strFind5 = datatype(Cmbzd5.Text, Cmbgx5.Text, Cmbbjz5.Text)
  
  If OptAnd1.Value = True Then
    tmp1 = " And "
  Else
    tmp1 = " Or "
  End If
  If OptAnd2.Value = True Then
    tmp2 = " And "
  Else
    tmp2 = " Or "
  End If
  If OptAnd3.Value = True Then
    tmp3 = " And "
  Else
    tmp3 = " Or "
  End If
  If OptAnd4.Value = True Then
    tmp4 = " And "
  Else
    tmp4 = " Or "
  End If
  
  '下面假设条件是顺序输入的，条件组合为
  ' (A and (B and (C and (D and E)))) 同样适用于 or 关心
  If strFind1 <> "" Then
    If strFind2 <> "" Then
      If strFind3 <> "" Then
        If strFind4 <> "" Then
          If strFind5 <> "" Then
            Text1.Text = Text1.Text & " (" & strFind1 & tmp1 & " (" & strFind2 & tmp2 & " ("
            Text1.Text = Text1.Text & strFind3 & tmp3 & " (" & strFind4 & tmp4 & strFind5 & "))))"
          Else
            Text1.Text = Text1.Text & " (" & strFind1 & tmp1 & " (" & strFind2 & tmp2 & " ("
            Text1.Text = Text1.Text & strFind3 & tmp3 & strFind4 & ")))"
          End If
        Else
          Text1.Text = Text1.Text & " (" & strFind1 & tmp1 & " (" & strFind2 & tmp2 & strFind3 & "))"
        End If
      Else
        Text1.Text = Text1.Text & " (" & strFind1 & tmp1 & strFind2 & ")"
      End If
    Else
      Text1.Text = Text1.Text & " (" & strFind1 & ")"
    End If
  Else
    Text1.Text = "Select * From " & tabname
  End If
  
  strQuery = Text1.Text
  rsTemp.Close
  Set rsTemp = Nothing
  Unload Me
End Sub

Private Sub CmdAll_Click()
  strQuery = "Select * From " & tabname
  rsTemp.Close
  Set rsTemp = Nothing
  Unload Me
End Sub

Private Sub cmdCancel_Click()
  strQuery = ""
  rsTemp.Close
  Set rsTemp = Nothing
  Unload Me
End Sub

Private Function datatype(sfld As String, sgx As String, sbjz As String) As String '对表中不同的字段类型,SQL语句做相应的处理
  Select Case rsTemp.Fields(Trim(sfld)).Type
    Case 129, 200   '字段类型为字符型
      If Trim(sgx) = "包含" Then
        datatype = sfld & " like '%" & Trim(sbjz) & "%'"
      Else
        datatype = sfld & " " & Trim(sgx) & " " & "'" & Trim(sbjz) & "'"
      End If
    Case 2, 3, 5, 6  '字段类型为数值型
      If Trim(sgx) = "包含" Then
        datatype = sfld & " like '%" & Trim(sbjz) & "%'"
      Else
        datatype = sfld & " " & Trim(sgx) & " " & Trim(sbjz)
      End If
    Case 7, 135  '字段类型为日期型
      datatype = "(" & sfld & " " & Trim(sgx) & " " & "'" & Trim(sbjz) & "')"
  End Select
End Function