为方便大家指教,把链接中煮江品茶老师代码的主要部分贴出来:
Private Sub 追加_Click()
Dim rsE As New ADODB.Recordset
Dim ssql As String
Dim i As Integer, m1 As Integer, m2 As Integer
ssql = "select a.立案号,min(a.起保日期) as 起保日期,min(a.终保日期) as 终保日期,min(a.立案日期) as 立案日期,max(a.赔付日期) as 赔付日期,sum(a.赔付金额) as 赔付金额 from (" & Me.Excel表.RowSource & ") as a group by a.立案号"
rsE.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
m1 = 0: m2 = 0
For i = 1 To rsE.RecordCount
If DCount("立案号", "tblP09_yj", "立案号='" & rsE!立案号.Value & "'") > 0 Then
If DLookup("赔付日期", "tblP09_yj", "立案号='" & rsE!立案号.Value & "'") < rsE!赔付日期.Value Then
ssql = "update tblP09_yj set 赔付日期=#" & rsE!赔付日期.Value & "#,"
ssql = ssql & "赔付金额=赔付金额+" & rsE!赔付金额.Value
ssql = ssql & " where 立案号='" & rsE!立案号.Value & "'"
CurrentDb.Execute ssql
m1 = m1 + 1
End If
Else
ssql = "insert into tblP09_yj (立案号,起保日期,终保日期,立案日期,赔付日期,赔付金额) "
ssql = ssql & "values ('" & rsE!立案号.Value & "',#"
ssql = ssql & rsE!起保日期.Value & "#,#"
ssql = ssql & rsE!终保日期.Value & "#,#"
ssql = ssql & rsE!立案日期.Value & "#,#"
ssql = ssql & rsE!赔付日期.Value & "#,"
ssql = ssql & rsE!赔付金额.Value & ")"
CurrentDb.Execute ssql
m2 = m2 + 1
End If
rsE.MoveNext
Next
rsE.Close: Set rsE = Nothing
Me.tblP09_yj.Requery
MsgBox "修改赔付金额记录数:" & m1 & Chr(13) & Chr(10) & "追加新纪录数:" & m2
End Sub