frmcustomermanagement.frm

保险定单管理系统,实现了保险的增删改查.

         Caption         =   "性别:"
         Height          =   315
         Left            =   3780
         TabIndex        =   20
         Top             =   240
         Width           =   795
      End
      Begin VB.Label Label7 
         Alignment       =   1  'Right Justify
         Caption         =   "备注:"
         Height          =   315
         Left            =   120
         TabIndex        =   12
         Top             =   2460
         Width           =   735
      End
      Begin VB.Label Label6 
         Alignment       =   1  'Right Justify
         Caption         =   "E-Mail:"
         Height          =   375
         Left            =   60
         TabIndex        =   11
         Top             =   2100
         Width           =   795
      End
      Begin VB.Label Label5 
         Alignment       =   1  'Right Justify
         Caption         =   "电话:"
         Height          =   375
         Left            =   240
         TabIndex        =   10
         Top             =   1680
         Width           =   615
      End
      Begin VB.Label Label4 
         Alignment       =   1  'Right Justify
         Caption         =   "手机:"
         Height          =   375
         Left            =   180
         TabIndex        =   9
         Top             =   1320
         Width           =   675
      End
      Begin VB.Label Label3 
         Alignment       =   1  'Right Justify
         Caption         =   "邮编:"
         Height          =   315
         Left            =   120
         TabIndex        =   8
         Top             =   960
         Width           =   735
      End
      Begin VB.Label Label2 
         Alignment       =   1  'Right Justify
         Caption         =   "住址:"
         Height          =   255
         Left            =   180
         TabIndex        =   7
         Top             =   600
         Width           =   675
      End
      Begin VB.Label Label1 
         Alignment       =   1  'Right Justify
         Caption         =   "姓名:"
         Height          =   255
         Left            =   180
         TabIndex        =   6
         Top             =   240
         Width           =   675
      End
   End
   Begin VB.Frame frameTop 
      Height          =   735
      Left            =   0
      TabIndex        =   0
      Top             =   0
      Width           =   10335
      Begin VB.CommandButton btnReturn 
         Caption         =   "返回"
         Height          =   315
         Left            =   2220
         TabIndex        =   4
         Top             =   240
         Width           =   555
      End
      Begin VB.CommandButton btnSave 
         Caption         =   "保存"
         Height          =   315
         Left            =   1560
         TabIndex        =   3
         Top             =   240
         Width           =   555
      End
      Begin VB.CommandButton btnRemove 
         Caption         =   "-"
         Height          =   315
         Left            =   900
         TabIndex        =   2
         Top             =   240
         Width           =   555
      End
      Begin VB.CommandButton btnAdd 
         Caption         =   "+"
         Height          =   315
         Left            =   240
         TabIndex        =   1
         Top             =   240
         Width           =   555
      End
   End
End
Attribute VB_Name = "frmCustomerManagement"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private id As Integer   '主键id
Dim msg As String '提示信息
Dim rs As ADODB.Recordset   '通用记录集

Private Sub btnAdd_Click()
'转换时间日期格式
Dim DTBirthDay As Variant
DTBirthDay = Format(Trim(dtpBirth.Value), "yyyy-mm-dd")

'生成查询字符串
Dim str As String
str = "INSERT INTO TCustomerInformation(Name,Sex,Birthday,IdCard,Address,ZipCode,Health,HandPhone,Phone,Email,UserGroup,SortId,Remarks,CompanyName,CompanyAddress,CompanyPhone,CompanyFax,Business,Job) values('" & txtName & "','" & combSex.text & "','" & DTBirthDay & "','" & txtIdCard.text & "','" & txtAddress.text & "','" & txtZipCode.text & "','" & combHealth.text & "','" & txtHandPhone.text & "','" & txtPhone.text & "','" & txtEMail.text & "','" & combGroup.text & "','" & txtOrder.text & "','" & txtRemarks.text & "','" & txtComName.text & "','" & txtComAddress.text & "','" & txtComPhone.text & "','" & txtComFax.text & "','" & combComBelongs.text & "','" & combTask & "')"
'插入数据
Set rs = ExecuteSQL(str, msg)
'更新MSFlexGrid视图信息
If (msg <> "Successful") Then
    MsgBox msg
End If
UpdateDataFromDatabase
End Sub

Private Sub btnRemove_Click()
Dim oldRow As Integer
oldRow = MSFlexGrid.Row
'获得用户选择的数据的id
id = CInt(MSFlexGrid.TextMatrix(MSFlexGrid.Row, 0))
Dim rs As ADODB.Recordset
'查询字符串
Dim str As String
str = "delete  from TCustomerInformation where Id=" & id
Set rs = ExecuteSQL(str, msg)   '删除
If (msg <> "Successful") Then
    MsgBox msg
End If
'更新MSFlexGrid视图信息
UpdateDataFromDatabase
MSFlexGrid.Row = oldRow - 1
MSFlexGrid.SetFocus
End Sub

Private Sub btnReturn_Click()
Unload Me
End Sub

Private Sub btnSave_Click()
'转换时间日期格式
Dim DTBirthDay As Variant
DTBirthDay = Format(Trim(dtpBirth.Value), "yyyy-mm-dd")

'生成查询字符串
Dim str As String
str = "update TCustomerInformation set Name='" & txtName.text & "',Sex='" & combSex.text & "',Birthday='" & DTBirthDay & "',IdCard='" & txtIdCard.text & "',Address='" & txtAddress.text & "',ZipCode='" & txtZipCode.text & "',Health='" & combHealth.text & "',HandPhone='" & txtHandPhone.text & "',Phone='" & txtPhone.text & "',Email='" & txtEMail.text & "',UserGroup='" & combGroup.text & "',SortId='" & txtOrder.text & "',Remarks='" & txtRemarks.text & "',CompanyName='" & txtComName.text & "',CompanyAddress='" & txtComAddress.text & "',CompanyPhone='" & txtComPhone.text & "',CompanyFax='" & txtComFax.text & "',Business='" & combComBelongs.text & "',Job='" & combTask & "' where Id=" & id
'插入数据
Set rs = ExecuteSQL(str, msg)
'更新MSFlexGrid视图信息
If (msg <> "Successful") Then
    MsgBox msg
End If
UpdateDataFromDatabase
End Sub

Private Sub Form_Load()
Dim Sex, Health, Group, ComBelongs, Task
Sex = Array("男", "女")
For i = 0 To 1
   combSex.AddItem Sex(i)
Next i
combSex.text = Sex(0)

Health = Array("良好", "一般", "无重大疾病")
For i = 0 To 2
   combHealth.AddItem Health(i)
Next i
combHealth.text = Health(0)

Group = Array("人寿保险组", "意外伤害保险组", "医疗保险组")
For i = 0 To 2
   combGroup.AddItem Group(i)
Next i
combGroup.text = Group(0)

ComBelongs = Array("金融业", "服务业", "制造页", "IT业", "政府机关")
For i = 0 To 4
   combComBelongs.AddItem ComBelongs(i)
Next i
combComBelongs.text = ComBelongs(0)
Task = Array("经理", "会计", "工程师", "服务员", "接线员")
For i = 0 To 4
   combTask.AddItem Task(i)
Next i
combTask.text = Task(0)
UpdateDataFromDatabase
MSFlexGrid_Click
End Sub

Private Sub Form_Resize()
frameTop.width = Me.width - 120
frameCenter.width = Me.width - 120
frameBottom.width = Me.width - 120
frameBottom.Height = Me.Height - 4700
MSFlexGrid.width = frameBottom.width - 120
MSFlexGrid.Height = frameBottom.Height - 300

End Sub
Public Sub UpdateDataFromDatabase() '更新MSFlexGrid视图信息
Dim width, text, Archives

'先清空原有数据
MSFlexGrid.Clear

'字段名和其宽度
width = Array("1100", "1000", "1300", "1000", "800", "1300", "800", "800", "2000", "800", "800", "800", "800", "800", "800", "800", "800", "800", "800", "800")
text = Array("客户编号", "姓名", "性别", "出生日期", "省份证号", "住址", "邮编", "身体状态", "手机", "电话", "E-Mail", "所属组", "显示顺序", "备注", "公司名称", "公司地址", "公司电话", "公司传真", "业务性质", "职务")
With MSFlexGrid
    .Rows = 2
    .Cols = 21
    .FixedCols = 0
    .FixedRows = 1
End With

'  表头数据
For i = 0 To 19
    MSFlexGrid.ColWidth(i) = width(i)
    MSFlexGrid.TextMatrix(0, i) = text(i)
Next i

'查询数据库
Set rs = ExecuteSQL("Select* from TCustomerInformation", msg)

If rs.EOF = False Then  '如果有数据
rs.MoveFirst
'将数据添加到视图
For i = 0 To rs.RecordCount - 1
    If i + 1 >= MSFlexGrid.Rows Then MSFlexGrid.Rows = MSFlexGrid.Rows + 1
    For j = 0 To rs.Fields.Count - 1
     MSFlexGrid.TextMatrix(i + 1, j) = rs.Fields(j)
    Next j
    rs.MoveNext
Next i
rs.Close
End If

End Sub
Private Sub MSFlexGrid_Click()  '当用户在视图上选择某行时，更新数据到编辑区

id = CInt(MSFlexGrid.TextMatrix(MSFlexGrid.Row, 0))
txtName.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 1)
combSex.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 2)
If (MSFlexGrid.TextMatrix(MSFlexGrid.Row, 3) <> "") Then dtpBirth.Value = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 3)
txtIdCard.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 4)
txtAddress.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 5)
txtZipCode.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 6)
combHealth.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 7)
txtHandPhone.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 8)
txtPhone.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 9)
txtEMail.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 10)
combGroup.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 11)
txtOrder.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 12)
txtRemarks.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 13)
txtComName.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 14)
txtComAddress.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 15)
txtComPhone.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 16)
txtComFax.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 17)
combComBelongs.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 18)
combTask = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 19)
End Sub