client_input.frm

小型客户管理系统

      Begin VB.Label Label3 
         Caption         =   "电话"
         Height          =   255
         Left            =   6120
         TabIndex        =   3
         Top             =   400
         Width           =   795
      End
      Begin VB.Label Label2 
         Caption         =   "客户地址"
         Height          =   255
         Left            =   240
         TabIndex        =   2
         Top             =   880
         Width           =   795
      End
      Begin VB.Label Label1 
         Caption         =   "客户名称"
         Height          =   255
         Left            =   240
         TabIndex        =   1
         Top             =   400
         Width           =   795
      End
   End
End
Attribute VB_Name = "Client_input"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private ListRS As New ADODB.Recordset
Private ClientRS As New ADODB.Recordset

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOK_Click()
Dim Sqlstring As String
Sqlstring = ""

Select Case OperateState
       Case "add"
               Sqlstring = "insert into Client_BASE (客户名称,客户地址,电话,传真,网址,电子邮件,邮政编码,所属区域,客户类型,客户来源,行业类型,客户状态,客户等级,国家,省份,城市,登记日期,所属人员) values('" _
                         & Khmc.Text & "','" & Khdz.Text & "','" & Dh.Text & "','" & Cz.Text & "','" & Wz.Text & "','" & Dzyj.Text & "','" _
                         & Yzbm.Text & "','" & Ssqy.Text & "','" & Khlx.Text & "','" & Khly.Text & "','" & Hylx.Text & "','" _
                         & Khzt.Text & "','" & Khdj.Text & "','" & Gj.Text & "','" & Sf.Text & "','" & Cs.Text & "','" _
                         & Djrq.Text & "','" & Ssry.Text & "')"
               'Sqlstring = "insert into Client_BASE (客户名称) values('aaa')"
               
               If Khmc.Text <> "" Then
                  Set ClientRS = Nothing
                  ClientRS.Open "select * from Client_BASE where 客户名称='" & Khmc.Text & "'", MainDB, adOpenStatic, adLockOptimistic
                  If ClientRS.RecordCount = 0 Then
                     MainDB.Execute (Sqlstring)
                     Unload Me
                  Else
                     MsgBox "存在相同客户名称", vbExclamation, "警告"
                  End If
               Else
                  MsgBox "请输入客户名称", vbExclamation, "警告"
               End If
       Case "modify"
            Sqlstring = "update Client_BASE set " _
                                                  & "客户名称='" & Khmc.Text & "'," _
                                                  & "客户地址='" & Khdz.Text & "'," _
                                                  & "电话='" & Dh.Text & "'," _
                                                  & "传真='" & Cz.Text & "'," _
                                                  & "网址='" & Wz.Text & "'," _
                                                  & "电子邮件='" & Dzyj.Text & "'," _
                                                  & "邮政编码='" & Yzbm.Text & "'," _
                                                  & "所属区域='" & Ssqy.Text & "'," _
                                                  & "客户类型='" & Khlx.Text & "'," _
                                                  & "客户来源='" & Khly.Text & "'," _
                                                  & "行业类型='" & Hylx.Text & "'," _
                                                  & "客户状态='" & Khzt.Text & "'," _
                                                  & "客户等级='" & Khdj.Text & "'," _
                                                  & "国家='" & Gj.Text & "'," _
                                                  & "省份='" & Sf.Text & "'," _
                                                  & "城市='" & Cs.Text & "'," _
                                                  & "登记日期='" & Djrq.Text & "'," _
                                                  & "所属人员='" & Ssry.Text _
                         & "' where 客户编号=" & ClientCode
            If ClientCode > 0 Then
               MainDB.Execute (Sqlstring)
               Unload Me
            End If
End Select

Set MainRS = Nothing
MainRS.Open "select * from " & OperateDB & " where 所属人员='" & UserName & "'", MainDB, adOpenStatic, adLockOptimistic
Set Data_list.Adodc1.Recordset = MainRS

End Sub

Private Sub Form_Load()
Dim I As Integer

Djrq.Text = Date
Ssry.AddItem UserName
For I = 0 To Ssry.ListCount
    Ssry.ListIndex = I
    If UserName = Ssry.Text Then
       Exit For
    End If
Next I

Set ListRS = Nothing
ListRS.Open "select * from JC_SSQY", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Ssqy.AddItem ListRS!区域名称
   ListRS.MoveNext
Wend

Set ListRS = Nothing
ListRS.Open "select * from JC_KHLX", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Khlx.AddItem ListRS!客户类型名称
   ListRS.MoveNext
Wend

Set ListRS = Nothing
ListRS.Open "select * from JC_KHLY", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Khly.AddItem ListRS!来源名称
   ListRS.MoveNext
Wend

Set ListRS = Nothing
ListRS.Open "select * from JC_HYLX", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Hylx.AddItem ListRS!行业类型名称
   ListRS.MoveNext
Wend

Set ListRS = Nothing
ListRS.Open "select * from JC_KHZT", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Khzt.AddItem ListRS!状态名称
   ListRS.MoveNext
Wend

Set ListRS = Nothing
ListRS.Open "select * from JC_KHDJ", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Khdj.AddItem ListRS!等级名称
   ListRS.MoveNext
Wend

Set ListRS = Nothing
ListRS.Open "select 国家 from JC_SSDQ group by 国家", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Gj.AddItem ListRS!国家
   ListRS.MoveNext
Wend

If OperateState = "modify" Then
   Call Read_Data
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
MDIForm1.Enabled = True
End Sub

Private Sub Gj_Click()
Sf.Clear
Set ListRS = Nothing
ListRS.Open "select 省份 from JC_SSDQ where 国家='" & Gj.Text & "' group by 省份", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Sf.AddItem ListRS!省份
   ListRS.MoveNext
Wend
End Sub

Private Sub Sf_Click()
Cs.Clear
Set ListRS = Nothing
ListRS.Open "select * from JC_SSDQ where 国家='" & Gj.Text & "' and 省份='" & Sf.Text & "'", MainDB, adOpenStatic, adLockOptimistic
While Not ListRS.EOF
   Cs.AddItem ListRS!城市
   ListRS.MoveNext
Wend
End Sub

'读取修改数据
Private Sub Read_Data()
Dim I As Integer

Khmc.Text = MainRS!客户名称
Khdz.Text = MainRS!客户地址
Dh.Text = MainRS!电话
Cz.Text = MainRS!传真
Wz.Text = MainRS!网址
Dzyj.Text = MainRS!电子邮件
Yzbm.Text = MainRS!邮政编码

If MainRS!所属区域 <> "" Then
For I = 0 To Ssqy.ListCount
    Ssqy.ListIndex = I
    If MainRS!所属区域 = Ssqy.Text Then
       Exit For
    End If
Next I
End If

If MainRS!客户类型 <> "" Then
For I = 0 To Khlx.ListCount
    Khlx.ListIndex = I
    If MainRS!客户类型 = Khlx.Text Then
       Exit For
    End If
Next I
End If

If MainRS!客户来源 <> "" Then
For I = 0 To Khly.ListCount
    Khly.ListIndex = I
    If MainRS!客户来源 = Khly.Text Then
       Exit For
    End If
Next I
End If

If MainRS!行业类型 <> "" Then
For I = 0 To Hylx.ListCount
    Hylx.ListIndex = I
    If MainRS!行业类型 = Hylx.Text Then
       Exit For
    End If
Next I
End If

If MainRS!客户状态 <> "" Then
For I = 0 To Khzt.ListCount
    Khzt.ListIndex = I
    If MainRS!客户状态 = Khzt.Text Then
       Exit For
    End If
Next I
End If

If MainRS!客户等级 <> "" Then
For I = 0 To Khdj.ListCount
    Khdj.ListIndex = I
    If MainRS!客户等级 = Khdj.Text Then
       Exit For
    End If
Next I
End If

If MainRS!国家 <> "" Then
For I = 0 To Gj.ListCount
    Gj.ListIndex = I
    If MainRS!国家 = Gj.Text Then
       Exit For
    End If
Next I
Call Gj_Click
End If


If MainRS!省份 <> "" Then
For I = 0 To Sf.ListCount
    Sf.ListIndex = I
    If MainRS!省份 = Sf.Text Then
       Exit For
    End If
Next I
Call Sf_Click
End If

If MainRS!城市 <> "" Then
For I = 0 To Cs.ListCount
    Cs.ListIndex = I
    If MainRS!城市 = Cs.Text Then
       Exit For
    End If
Next I
End If

Djrq.Text = MainRS!登记日期

If MainRS!所属人员 <> "" Then
For I = 0 To Ssry.ListCount
    Ssry.ListIndex = I
    If MainRS!所属人员 = Ssry.Text Then
       Exit For
    End If
Next I
End If

End Sub