gjfind.frm

来自「功能强大的个人工作通讯录」· FRM 代码 · 共 1,596 行 · 第 1/4 页

               Name            =   "楷体_GB2312"
               Size            =   14.25
               Charset         =   134
               Weight          =   700
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            ForeColor       =   &H00FF0000&
            Height          =   285
            Left            =   195
            TabIndex        =   15
            Top             =   2580
            Width           =   1260
         End
      End
   End
End
Attribute VB_Name = "GJfind"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim sqlH
Dim sqlH1
Dim sqlH2
Dim Log1
Dim Log2

Private Sub cmdAll_Click()
For i = 0 To 14
       CheckBox1(i).Value = 1
Next i
End Sub



Private Sub cmdCancel_Click()
For i = 0 To 14
       CheckBox1(i).Value = 0
Next i
End Sub



Private Sub cmdClear_Click()
Dim txt As Control
For Each txt In Controls
   If TypeName(txt) = "TextBox" Then
      txt.Text = ""
   End If
Next txt
End Sub

Private Sub cmdCZ_Click()

If txtXM <> "" Then
tjian1 = "姓名 like '%" & txtXM & "%' and "
Else
tjian1 = ""
End If

If txtFL <> "" Then
tjian2 = "分类 like '%" & txtFL & "%' and "
Else
tjian2 = ""
End If

If txtSR <> "" Then
tjian3 = "生日 like '%" & txtSR & "%' and "
Else
tjian3 = ""
End If

If txtXB <> "" Then
tjian4 = "性别 like '%" & txtXB & "%' and "
Else
tjian4 = ""
End If

If txtZW <> "" Then
tjian5 = "职务 like '%" & txtZW & "%' and "
Else
tjian5 = ""
End If

If txtDH <> "" Then
tjian6 = "电话 like '%" & txtDH & "%' and "
Else
tjian6 = ""
End If

If txtCZ <> "" Then
tjian7 = "传真 like '%" & txtCZ & "%' and "
Else
tjian7 = ""
End If

If txtJG <> "" Then
tjian8 = "籍贯 like '%" & txtJG & "%' and "
Else
tjian8 = ""
End If

'================================
If txtEmail <> "" Then
tjian9 = "电子邮箱 like '%" & txtEmail & "%' and "
Else
tjian9 = ""
End If

If txtSS <> "" Then
tjian10 = "所在省市 like '%" & txtSS & "%' and "
Else
tjian10 = ""
End If

If txtCD <> "" Then
tjian11 = "存档时间 like '%" & txtCD & "%' and "
Else
tjian11 = ""
End If

If txtGS <> "" Then
tjian12 = "公司名称 like '%" & txtGS & "%' and "
Else
tjian12 = ""
End If

If txtDZ <> "" Then
tjian13 = "公司地址 like '%" & txtDZ & "%' and "
Else
tjian13 = ""
End If

If txtXG <> "" Then
tjian14 = "性格爱好 like '%" & txtXG & "%' and "
Else
tjian14 = ""
End If
'================================
sqlH1 = "select distinct * from 联系人档案 where " & tjian1 & tjian2 & tjian3 & tjian4 & tjian5 & tjian6 & tjian7 & tjian8 & tjian9 & tjian10 & tjian11 & tjian12 & tjian13 & tjian14
sqlH1 = Left(sqlH1, Len(sqlH1) - 5)
Call addxx1
End Sub

Private Sub Command5_Click()
logicKeyWord1 = ""
logicKeyWord2 = ""
logicKeyWord3 = ""

End Sub

Private Sub fixCZ_Click()
ZJM.lv.ListItems.clear
 For i = 0 To 14
    If CheckBox1(i).Value Then
         xmmc = CheckBox1(i).Caption
        Call OpenConn1
        sql1 = "select * from 联系人档案 where " & xmmc & " like '%" & fixKeyWord & "%'"
             rs1.Open sql1, cn1, 3, 3
             If rs1.RecordCount > 0 Then
                
                   Do While Not rs1.EOF
                   
                        Set Item = ZJM.lv.FindItem(rs1.Fields("编号"), , , lvwPartial) '判断是否是重复客户
                        
                        If Item Is Nothing Then
                            it = 1
                        Else
                            it = 0
                        End If
                        
                        
                         If it = 1 Then
                          Set addlist = ZJM.lv.ListItems.add(, , IIf(IsNull(rs1.Fields(rs1.Fields(0).Name)), "", rs1.Fields(rs1.Fields(0).Name)), , 0)
                               
                            For h = 1 To rs1.Fields.Count - 1
                         
                            addlist.SubItems(h) = IIf(IsNull(rs1.Fields(rs1.Fields(h).Name)), "", rs1.Fields(rs1.Fields(h).Name))
                          
                            Next h
                         End If
                         
                        rs1.MoveNext
                   Loop
                
             End If
     End If
  
 Next i
End Sub

Private Sub Form_Load()
Me.Width = 11730
Me.Height = 5025
Me.Top = ZJM.Top + ZJM.lv.Top + ZJM.lv.Height - Me.Height
Me.Left = ZJM.lv.Left + ZJM.Left
sql = "select * from 联系人档案"
Call OpenConn
rs.Open sql, cn, 3, 3

 For i = 0 To rs.Fields.Count - 1
    logicCombo1.AddItem rs.Fields(i).Name
    logicCombo2.AddItem rs.Fields(i).Name
    logicCombo3.AddItem rs.Fields(i).Name
 Next i
Call CloseConn
logicCombo4.AddItem "且"
logicCombo4.AddItem "或"
logicCombo4.AddItem "非"
logicCombo5.AddItem "且"
logicCombo5.AddItem "或"
logicCombo5.AddItem "非"
End Sub

Private Sub logicCZ_Click()
Call logicWord

If logicKeyWord1 <> "" Then
    key1 = logicCombo1 & " like '%" & logicKeyWord1 & "%'"
Else
    key1 = ""
End If
'================================================================
If logicKeyWord2 <> "" Then
    key2 = logicCombo2 & " like '%" & logicKeyWord2 & "%'"
Else
    key2 = ""
End If
'================================================================
If logicKeyWord3 <> "" Then
    key3 = logicCombo3 & " like '%" & logicKeyWord3 & "%'"
Else
    key3 = ""
End If
'================================================================
If Log1 = " NOT " Then
    Log1 = " AND "
    key2 = Replace(key2, "like", "NOT like")
End If

If Log2 = " NOT " Then
    Log2 = " AND "
    key3 = Replace(key3, "like", "NOT like")
End If

sqlH2 = "select distinct * from 联系人档案 where " & key1 & Log1 & key2 & Log2 & key3
Call Addxx2

End Sub

Sub Addxx2()
On Error GoTo err
 '----------------------------------------------------加载默认列表项目
    ZJM.lv.ColumnHeaders.clear '清除列头
    
  
    Call OpenConn
    rs.Open sqlH2, cn, 3, 3
    
    ZJM.lv.ListItems.clear
    
    For i = 0 To rs.Fields.Count - 1
    
       ZJM.lv.ColumnHeaders.add = rs.Fields(i).Name
        
        Next i
    
    ZJM.lv.ColumnHeaders(1).Width = 800
    
    
   ZJM.lv.ListItems.clear '清除列表项目
         
If rs.RecordCount > 0 Then
    
    Do While Not rs.EOF
    
        Set addlist = ZJM.lv.ListItems.add(, , IIf(IsNull(rs.Fields(rs.Fields(0).Name)), "", rs.Fields(rs.Fields(0).Name)), , 0)
        
       
            For k = 1 To rs.Fields.Count - 1
         
            addlist.SubItems(k) = IIf(IsNull(rs.Fields(rs.Fields(k).Name)), "", rs.Fields(rs.Fields(k).Name))
          
        Next k
        
        rs.MoveNext
    Loop

 End If

Call CloseConn

'----------------------设置前四列列宽

t = 1
 
err:
 If err.Description <> "" Then
 End If
End Sub
Sub addxx1()
On Error GoTo err
 '----------------------------------------------------加载默认列表项目
    ZJM.lv.ColumnHeaders.clear '清除列头
    
  
    Call OpenConn
    rs.Open sqlH1, cn, 3, 3
    
    ZJM.lv.ListItems.clear
    
    For i = 0 To rs.Fields.Count - 1
    
       ZJM.lv.ColumnHeaders.add = rs.Fields(i).Name
        
        Next i
    
    ZJM.lv.ColumnHeaders(1).Width = 800
    
    
   ZJM.lv.ListItems.clear '清除列表项目
         
If rs.RecordCount > 0 Then
    
    Do While Not rs.EOF
    
        Set addlist = ZJM.lv.ListItems.add(, , IIf(IsNull(rs.Fields(rs.Fields(0).Name)), "", rs.Fields(rs.Fields(0).Name)), , 0)
        
       
            For k = 1 To rs.Fields.Count - 1
         
            addlist.SubItems(k) = IIf(IsNull(rs.Fields(rs.Fields(k).Name)), "", rs.Fields(rs.Fields(k).Name))
          
        Next k
        
        rs.MoveNext
    Loop

 End If

Call CloseConn

'----------------------设置前四列列宽

t = 1
 
err:
 If err.Description <> "" Then
 End If
End Sub
Sub logicWord()

If logicKeyWord1 = "" Then
    Log1 = ""
    logicCombo4 = ""
Else
    Select Case logicCombo4
        Case "且"
            Log1 = " AND "
        Case "或"
            Log1 = " OR "
        Case "非"
            Log1 = " NOT "
    End Select
    
End If

If logicKeyWord3 = "" Then
    Log2 = ""
    logicCombo5 = ""
Else
    Select Case logicCombo5
        Case "且"
            Log2 = " AND "
        Case "或"
            Log2 = " OR "
        Case "非"
            Log2 = " NOT "
    End Select
    
End If

If logicKeyWord2 = "" Then
    Log1 = ""
    logicCombo4 = ""
End If

If logicKeyWord1 = "" And logicKeyWord2 = "" Then
    Log1 = ""
    Log2 = ""
    logicCombo4 = ""
    logicCombo5 = ""
End If

End Sub