frmpolicyinput.frm

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

         Height          =   255
         Left            =   240
         TabIndex        =   24
         Top             =   1320
         Width           =   1815
      End
      Begin VB.Label Label5 
         Alignment       =   1  'Right Justify
         Caption         =   "受益人编号:"
         Height          =   255
         Left            =   240
         TabIndex        =   23
         Top             =   1680
         Width           =   1815
      End
      Begin VB.Label Label6 
         Alignment       =   1  'Right Justify
         Caption         =   "保险类型:"
         Height          =   255
         Left            =   240
         TabIndex        =   22
         Top             =   2100
         Width           =   1815
      End
      Begin VB.Label Label7 
         Alignment       =   1  'Right Justify
         Caption         =   "投保人与被保人关系:"
         Height          =   255
         Left            =   240
         TabIndex        =   21
         Top             =   2460
         Width           =   1815
      End
      Begin VB.Label Label13 
         Alignment       =   1  'Right Justify
         Caption         =   "保险金额:"
         Height          =   255
         Left            =   4980
         TabIndex        =   20
         Top             =   300
         Width           =   975
      End
      Begin VB.Label label100 
         Alignment       =   1  'Right Justify
         Caption         =   "缴费期限:"
         Height          =   255
         Left            =   4920
         TabIndex        =   19
         Top             =   660
         Width           =   1035
      End
      Begin VB.Label label23 
         Alignment       =   1  'Right Justify
         Caption         =   "付费方式:"
         Height          =   315
         Left            =   4800
         TabIndex        =   18
         Top             =   1020
         Width           =   1155
      End
      Begin VB.Label Label16 
         Alignment       =   1  'Right Justify
         Caption         =   "下次缴费日期:"
         Height          =   255
         Left            =   3660
         TabIndex        =   17
         Top             =   1440
         Width           =   2295
      End
      Begin VB.Label Label17 
         Alignment       =   1  'Right Justify
         Caption         =   "每期保险费:"
         Height          =   315
         Left            =   4560
         TabIndex        =   16
         Top             =   1740
         Width           =   1395
      End
      Begin VB.Label Label18 
         Alignment       =   1  'Right Justify
         Caption         =   "保险费过期未付选择:"
         Height          =   315
         Left            =   4200
         TabIndex        =   15
         Top             =   2100
         Width           =   1755
      End
   End
   Begin VB.Frame frameBottom 
      Caption         =   "保单信息"
      Height          =   3915
      Left            =   0
      TabIndex        =   0
      Top             =   4140
      Width           =   10335
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid 
         Height          =   3675
         Left            =   60
         TabIndex        =   1
         Top             =   180
         Width           =   10215
         _ExtentX        =   18018
         _ExtentY        =   6482
         _Version        =   393216
         SelectionMode   =   1
         AllowUserResizing=   1
      End
   End
End
Attribute VB_Name = "frmPolicyInput"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rs As ADODB.Recordset   '通用记录集
Dim msg As String '提示信息
Private id As String   '主键id
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")
text = Array("保险合同编号", "投保日期", "投保人编号", "被保人编号", "受益人编号", "保险类型", "投保人与被保人关系", "投保人与受益人关系", "保险金额", "每期保险费", "缴费期限", "付费方式", "下次缴费日期", "保险费过期未付选择", "体检状况", "备注")
With MSFlexGrid
    .Rows = 2
    .Cols = 18
    .FixedCols = 0
    .FixedRows = 1
End With

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

'查询数据库
Set rs = ExecuteSQL("Select* from TPolicyForm", 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 - 3
    If (j < 11) Then '付费方式以前的字段
     MSFlexGrid.TextMatrix(i + 1, j) = rs.Fields(j)
     ElseIf j = 11 Then '对于付费方式单独处理，将用付费方式写入户选中的方式对应的字段，其他字段写入"未选"
        If (rs.Fields(j) <> "未选") Then
        MSFlexGrid.TextMatrix(i + 1, j) = rs.Fields(j)
        ElseIf (rs.Fields(j + 1) <> "未选") Then
        MSFlexGrid.TextMatrix(i + 1, j) = rs.Fields(j + 1)
        Else:
        MSFlexGrid.TextMatrix(i + 1, j) = rs.Fields(j + 2)
        End If
     Else
     '以后的字段后移2位
     MSFlexGrid.TextMatrix(i + 1, j) = rs.Fields(j + 2)
     End If
    Next j
    rs.MoveNext
Next i
rs.Close
End If

End Sub

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

'根据付费方式生成查询字符串
Dim str As String
If (combPayWay.text = "季付费") Then
    str = "INSERT INTO TPolicyForm values('" & txtContractId.text & "','" & dtInsuranceDate & "','" & txtPolicyholder.text & "','" & txtInsured.text & "','" & txtBeneficiary.text & "','" & combInsuranceType.text & "','" & combRelationshipHI.text & "','" & combRelationshipHB.text & "','" & txtInsurancAmount.text & "','" & txtEachPremium.text & "','" & txtPaymentDeadline.text & "','" & combPayWay.text & "','未选','未选','" & DTNextPayDate & "','" & combExpiredChoice.text & "','" & combMedicalsituation.text & "','" & txtRemarks.text & "')"
ElseIf combPayWay.text = "半年付费" Then
    str = "INSERT INTO TPolicyForm values('" & txtContractId.text & "','" & dtInsuranceDate & "','" & txtPolicyholder.text & "','" & txtInsured.text & "','" & txtBeneficiary.text & "','" & combInsuranceType.text & "','" & combRelationshipHI.text & "','" & combRelationshipHB.text & "','" & txtInsurancAmount.text & "','" & txtEachPremium.text & "','" & txtPaymentDeadline.text & "','未选','" & combPayWay.text & "','未选','" & DTNextPayDate & "','" & combExpiredChoice.text & "','" & combMedicalsituation.text & "','" & txtRemarks.text & "')"
Else
    str = "INSERT INTO TPolicyForm values('" & txtContractId.text & "','" & dtInsuranceDate & "','" & txtPolicyholder.text & "','" & txtInsured.text & "','" & txtBeneficiary.text & "','" & combInsuranceType.text & "','" & combRelationshipHI.text & "','" & combRelationshipHB.text & "','" & txtInsurancAmount.text & "','" & txtEachPremium.text & "','" & txtPaymentDeadline.text & "','未选','未选','" & combPayWay.text & "','" & DTNextPayDate & "','" & combExpiredChoice.text & "','" & combMedicalsituation.text & "','" & txtRemarks.text & "')"
End If
'插入数据
Set rs = ExecuteSQL(str, msg)
If (msg <> "Successful") Then
    MsgBox msg
End If
'更新MSFlexGrid视图信息
UpdateDataFromDatabase

End Sub

Private Sub btnReturn_Click()
Unload Me
End Sub

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

'生成查询字符串
Dim str As String

If (combPayWay.text = "季付费") Then
    str = "update TPolicyForm set ContractId='" & txtContractId.text & "',InsuranceDate='" & dtInsuranceDate & "',PolicyHoldersId='" & txtPolicyholder.text & "',InsuredId='" & txtInsured.text & "',BeneficiariesId='" & txtBeneficiary.text & "',InsuranceType='" & combInsuranceType.text & "',RelationshipHI='" & combRelationshipHI.text & "',RelationshipHB='" & combRelationshipHB.text & "',InsuranceAmount='" & txtInsurancAmount.text & "',EachPremium='" & txtEachPremium.text & "',PaymentDeadline='" & txtPaymentDeadline.text & "',QuarterlyPayment='" & combPayWay.text & "',HalfYearPayment='未选',YearPayment='未选',NextPaymentDate='" & DTNextPayDate & "',ExpiredChoice='" & combExpiredChoice.text & "',Medicalsituation='" & combMedicalsituation.text & "',Remarks='" & txtRemarks.text & "'where ContractId ='" & id & "'"
ElseIf combPayWay.text = "半年付费" Then
    str = "update TPolicyForm set ContractId='" & txtContractId.text & "',InsuranceDate='" & dtInsuranceDate & "',PolicyHoldersId='" & txtPolicyholder.text & "',InsuredId='" & txtInsured.text & "',BeneficiariesId='" & txtBeneficiary.text & "',InsuranceType='" & combInsuranceType.text & "',RelationshipHI='" & combRelationshipHI.text & "',RelationshipHB='" & combRelationshipHB.text & "',InsuranceAmount='" & txtInsurancAmount.text & "',EachPremium='" & txtEachPremium.text & "',PaymentDeadline='" & txtPaymentDeadline.text & "',QuarterlyPayment='未选',HalfYearPayment='" & combPayWay.text & "',YearPayment='未选',NextPaymentDate='" & DTNextPayDate & "',ExpiredChoice='" & combExpiredChoice.text & "',Medicalsituation='" & combMedicalsituation.text & "',Remarks='" & txtRemarks.text & "'where ContractId ='" & id & "'"
Else
   str = "update TPolicyForm set ContractId='" & txtContractId.text & "',InsuranceDate='" & dtInsuranceDate & "',PolicyHoldersId='" & txtPolicyholder.text & "',InsuredId='" & txtInsured.text & "',BeneficiariesId='" & txtBeneficiary.text & "',InsuranceType='" & combInsuranceType.text & "',RelationshipHI='" & combRelationshipHI.text & "',RelationshipHB='" & combRelationshipHB.text & "',InsuranceAmount='" & txtInsurancAmount.text & "',EachPremium='" & txtEachPremium.text & "',PaymentDeadline='" & txtPaymentDeadline.text & "',QuarterlyPayment='未选',HalfYearPayment='未选',YearPayment='" & combPayWay.text & "',NextPaymentDate='" & DTNextPayDate & "',ExpiredChoice='" & combExpiredChoice.text & "',Medicalsituation='" & combMedicalsituation.text & "',Remarks='" & txtRemarks.text & "'where ContractId ='" & id & "'"
End If

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

Private Sub Form_Load()
'初始化所有CombBox
Dim iType, R, PayWay, ExpiredChoice, Medicalsituation
'保险类型
iType = Array("意外险", "失业险", "医疗险")
For i = 0 To 2
   combInsuranceType.AddItem iType(i)
Next i
combInsuranceType.text = iType(0)

'投保人与被保人及受益人关系
R = Array("本人", "直系亲属", "配偶")
For i = 0 To 2
    combRelationshipHI.AddItem R(i)
    combRelationshipHB.AddItem R(i)
Next i
combRelationshipHI.text = R(0)
combRelationshipHB.text = R(0)
'付费方式
PayWay = Array("季付费", "半年付费", "年付费")
For i = 0 To 2
    combPayWay.AddItem PayWay(i)
Next i
combPayWay.text = PayWay(0)

'超期未缴费处理
ExpiredChoice = Array("中止合同", "延期缴付")
For i = 0 To 1
    combExpiredChoice.AddItem ExpiredChoice(i)
Next i
combExpiredChoice.text = ExpiredChoice(0)
'身体状况
Medicalsituation = Array("未体检", "已体检")
For i = 0 To 1
combMedicalsituation.AddItem Medicalsituation(i)
Next i
combMedicalsituation.text = Medicalsituation(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
Private Sub MSFlexGrid_Click()  '当用户在视图上选择某行时，更新数据到编辑区

If (MSFlexGrid.TextMatrix(MSFlexGrid.Row, 0) <> "") Then    '当视图内有数据时才填充
    txtContractId.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 0)
    id = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 0)
If (MSFlexGrid.TextMatrix(MSFlexGrid.Row, 1) <> "") Then    '保证时间不为空
    dtpInsuranceDate.Value = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 1)
End If
txtPolicyholder.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 2)
txtInsured.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 3)
txtBeneficiary.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 4)
combInsuranceType.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 5)
combRelationshipHI.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 6)
combRelationshipHB.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 7)
txtInsurancAmount.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 8)
txtEachPremium.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 9)
txtPaymentDeadline.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 10)
combPayWay.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 11)
If MSFlexGrid.TextMatrix(MSFlexGrid.Row, 12) <> "" Then '保证时间不为空
    dtpNextPayDate.Value = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 12)
End If
combExpiredChoice.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 13)
combMedicalsituation.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 14)
txtRemarks.text = MSFlexGrid.TextMatrix(MSFlexGrid.Row, 15)
End If
End Sub