frmcustcheck.frm

客户关系管理系统(打包+源程序)是数据库系统开发项目方案精解系列丛书VB数据库管理中附带CD中的程序

      Left            =   840
      TabIndex        =   0
      Top             =   5280
      Width           =   1320
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   2175
      Left            =   240
      TabIndex        =   16
      Top             =   2880
      Width           =   6975
      _ExtentX        =   12303
      _ExtentY        =   3836
      _Version        =   393216
      Rows            =   6
      Cols            =   25
      FixedCols       =   0
      ScrollTrack     =   -1  'True
      AllowUserResizing=   1
      FormatString    =   $"frmCustCheck.frx":0008
   End
End
Attribute VB_Name = "frmCustCheck"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
    Private blCombo_NationName As Boolean   '判断是否选择了国家
    Private blCombo_ProvName As Boolean     '判断是否选择了省份
    
Private Sub cmdCheck_Click()
    Dim Rst As New ADODB.Recordset
    Dim Rst2 As New ADODB.Recordset '用于打开第二个临时记录集
    Dim strCheck As String
    Dim strSQL As String
    Dim strSQL2 As String           '用于第二个记录集的查询语句
    Dim strItem As String
    
On Error GoTo ErrorExit
    strCheck = ""
    Me.MSFlexGrid1.Rows = 1     'MSFlexGrid控件的初始行数为1
    If Me.txtCustName.Text <> "" Then     '客户名称的查询条件
        strCheck = " tb_customer.CustName LIKE '" & Me.txtCustName.Text & "'"
    End If
    If Me.txtCustID.Text <> "" Then       '客户代码的查询条件
        If strCheck <> "" Then
            strCheck = strCheck & " AND "
        End If
        strCheck = strCheck & " tb_customer.CustID = 'C" & Me.txtCustID.Text & "'"
    End If
    If Me.txtJurperson.Text <> "" Then    '法人代表的查询条件
        If strCheck <> "" Then
            strCheck = strCheck & " AND "
        End If
        strCheck = strCheck & " tb_customer.Jurperson ='" & Me.txtJurperson.Text & "'"
    End If
    If Me.txtViaperson.Text <> "" Then    '联系人查询条件
        If strCheck <> "" Then
            strCheck = strCheck & " AND "
        End If
        strCheck = strCheck & " tb_customer.Viaperson ='" & Me.txtViaperson.Text & "'"
    End If
    If Me.Combo_CustType.Text <> "" Then    '客户类型查询条件
        If strCheck <> "" Then
            strCheck = strCheck & " AND "
        End If
        strCheck = strCheck & " tb_customer.CustType ='" & Me.Combo_CustType.Text & "'"
    End If
    If strCheck <> "" Then
        strCheck = strCheck & " AND "
    End If
    strCheck = strCheck & "tb_customer.Cust_Area_ID = tb_Area.Area_ID "

    If Me.Combo_NationName.Text <> "" Then  '填写了国家，省份，城市的查询条件了
        strCheck = strCheck & " AND tb_Area.NationName ='" & Me.Combo_NationName.Text & "'"  '国家/地区查询条件
        If Me.Combo_ProvName.Text <> "" Then  '填写了国家，省份，城市的查询条件了
            strCheck = strCheck & " AND tb_Area.ProvName ='" & Me.Combo_ProvName.Text & "'"  '省份的查询条件
            If Me.Combo_CityName.Text <> "" Then  '填写了国家，省份，城市的查询条件了
                strCheck = strCheck & " AND tb_Area.CityName ='" & Me.Combo_CityName.Text & "'"  '城市的查询条件
            End If
        End If
    End If
    
    strSQL = "select tb_customer.*, tb_Area.* from tb_customer, tb_Area"       '定义SQL查询语句
    If strCheck <> "" Then
        strSQL = strSQL & " WHERE " & strCheck
    End If
    Rst.Open strSQL, CnnDatabase, adOpenStatic, adLockReadOnly      '打开一个数据集
    If Rst.RecordCount = 0 Then
        MsgBox "数据库中无相关客户记录！", vbInformation, "请注意"
        Exit Sub
    Else
        Rst.MoveFirst
        Do Until Rst.EOF
            strSQL2 = "select NationCode from tb_NationCode where NationName='" & Rst!NationName & "'"
            Rst2.Open strSQL2, CnnDatabase, adOpenStatic
            If Rst2.RecordCount > 1 Then
                MsgBox "国家名称出现多国际区号重复对应！", vbCritical, "数据库错误"
                Exit Sub
            End If
            If Rst2.RecordCount = 0 Then
                MsgBox "没有此国家的国际区号"
                Exit Sub
            End If
    '^ 客户名称 |^ 客户代码 |^ 国家/地区 |^ 国际区号 |^ 省份 |^ 城市 |^ 邮编 |^ 城市区号 |
    '^ 详细地址 |^ 电话 |^ 传真 |^ 电子邮件 |^ 主页 |^ 年收入 |^ 员工数 |^ 行业 |^ 客户类型 |
    '^ 客户来源 |^ 客户状态 |^ 联系人 |^ 联系人电话 |^ 联系人传真 |^ 法人代表 |^ 法人电话 |^ 法人传真
            strItem = Rst!CustName & Chr(9) & Rst!CustID & Chr(9) & Rst!NationName & Chr(9) & _
                Rst2!NationCode & Chr(9) & Rst!ProvName & Chr(9) & Rst!CityName & Chr(9) & _
                Rst!ZipCode & Chr(9) & Rst!CityCode & Chr(9) & Rst!Address & Chr(9) & Rst!CustTel & _
                Chr(9) & Rst!CustFax & Chr(9) & Rst!Email & Chr(9) & Rst!WebSite & Chr(9) & _
                Rst!Incoming & Chr(9) & Rst!PeopleNum & Chr(9) & Rst!Business & Chr(9) & Rst!CustType & _
                Chr(9) & Rst!CustFrom & Chr(9) & Rst!CustState & Chr(9) & Rst!Viaperson & Chr(9) & _
                Rst!ViaTel & Chr(9) & Rst!Viafax & Chr(9) & Rst!JurPerson & Chr(9) & _
                Rst!JurTel & Chr(9) & Rst!Jurfax
            Me.MSFlexGrid1.AddItem strItem      '向MSFlexGrid控件中添加数据
            Module1.MSFBackColor Me.MSFlexGrid1, Me.MSFlexGrid1.Rows - 1
            Rst2.Close
            Rst.MoveNext
        Loop
    End If
    Set Rst = Nothing   '关闭数据集
    Exit Sub
    
ErrorExit:
    MsgBox Err.Description, vbInformation, Me.Caption
End Sub

Private Sub cmdClear_Click()
    Initial
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub Combo_NationName_Click()
    Dim Rst As New ADODB.Recordset
    Dim strSQL As String
    
    If blCombo_NationName = False Then
        Exit Sub
    End If
    strSQL = "select distinct ProvName from tb_Area where NationName='" & Me.Combo_NationName.Text & "'"
    Rst.Open strSQL, CnnDatabase, adOpenDynamic '动态打开记录集
    If Rst.BOF = True And Rst.EOF = True Then   '记录集为空
        MsgBox "此国家/地区中的省份未登录数据库中！", vbCritical, Me.Caption
        Exit Sub
    End If
    Me.Combo_ProvName.Enabled = True            '使控件可用
    Me.Combo_ProvName.Clear                     '先清空控件
    Do While Rst.EOF = False
        Me.Combo_ProvName.AddItem Rst!ProvName  '向combo控件中添加项目
        Rst.MoveNext
    Loop
    Rst.Close
End Sub

Private Sub Combo_ProvName_Click()
    Dim Rst As New ADODB.Recordset
    Dim strSQL As String
    
    If blCombo_ProvName = False Then
        Exit Sub
    End If
    strSQL = "select CityName from tb_Area where NationName ='" & Me.Combo_NationName.Text & _
        "' AND ProvName ='" & Me.Combo_ProvName.Text & "'"
    Rst.Open strSQL, CnnDatabase, adOpenDynamic '打开静态记录集
    If Rst.BOF = True And Rst.EOF = True Then   '记录集为空
        MsgBox "此国家/地区的省份中的城市未登录数据库中！", vbCritical, Me.Caption
        Exit Sub
    End If
    Me.Combo_CityName.Enabled = True                '使combo控件可用
    Me.Combo_CityName.Clear                         '先清空控件内
    Do While Rst.EOF = False
        Me.Combo_CityName.AddItem Rst!CityName      '向combo控件中添加项目
        Rst.MoveNext
    Loop
    Rst.Close
End Sub

Private Sub Form_Load()
    Dim i As Integer
    Initial
    For i = 0 To 24     '设置MSFlexGrid控件每列宽度
        MSFlexGrid1.ColWidth(i) = 1200
    Next
    With Combo_CustType    '添加项目:客户类型
        .Clear
        .AddItem "正式客户"
        .AddItem "试用客户"
        .AddItem "潜在客户"
        .AddItem "竞争对手"
        .AddItem "代理商"
        .AddItem "内部员工"
    End With
End Sub

Private Sub Initial()               '初始化界面
    Dim Rst As New ADODB.Recordset
    Dim strSQL As String
    
    blCombo_NationName = False
    blCombo_ProvName = False
    Me.txtCustName = ""             '清除txtUserID控件的内容
    Me.txtCustID = ""               '清除txtcustID控件的内容
    Me.txtJurperson = ""            '清除txtJurperson控件的内容
    Me.txtViaperson = ""            '清除txtViaperson控件的内容
    Me.Combo_NationName.ListIndex = -1
    Me.Combo_ProvName.ListIndex = -1
    Me.Combo_ProvName.Enabled = False
    Me.Combo_CityName.ListIndex = -1
    Me.Combo_CityName.Enabled = False
    Me.Combo_CustType.ListIndex = -1
    Me.MSFlexGrid1.Rows = 1
    strSQL = "select NationName from tb_NationCode order by NationName ASC"
    Rst.Open strSQL, CnnDatabase, adOpenDynamic, adLockReadOnly
    If Rst.BOF = True And Rst.EOF = True Then
        MsgBox "数据库中无任何国家/地区！", vbCritical, "数据库错误！"
        Exit Sub
    End If
    Combo_NationName.Clear
    Do While Rst.EOF = False    '给国家/地区添加项目
        Combo_NationName.AddItem Rst.Fields("NationName").Value
        Rst.MoveNext
    Loop
    blCombo_NationName = True
    blCombo_ProvName = True
End Sub

Private Sub MSFlexGrid1_Click()

End Sub