如何将当前子窗体的数据有条件的保存到表中

kent_73

如何将当前子窗体的数据有条件的保存到表中？
具体请看附件。
谢谢

todaynew

kent_73 发表于 2011-6-30 08:49
如何将当前子窗体的数据有条件的保存到表中？
具体请看附件。
谢谢

Private Sub Command8_Click()
Dim str As String
str = "True "
If Not IsNull(Me.Combo2) Then
    str = str & " AND ([编号] ='" & Me.Combo2 & "')"
End If
If Not IsNull(Me.Combo4) Then
    str = str & " AND ([名称] ='" & Me.Combo4 & "')"
End If
Me.子窗体.Form.Filter = str
Me.子窗体.Form.FilterOn = True
If Me.Check9.Value = True Then
    Call insertTb(Me.子窗体.Form)
End If
End Sub

Sub insertTb(frm As Form)
Dim ssql As String
Dim strwh As String
ssql = frm.RecordSource
strwh = frm.Filter
ssql = Trim(ssql)
If Right(ssql, 1) = ";" Then
    ssql = Left(ssql, Len(ssql) - 1)
End If
ssql = "select 编号,名称,数量,单价 from (" & ssql & ") where " & strwh
ssql = "INSERT INTO 结算表 ( 编号, 名称, 数量, 单价 ) " & ssql
CurrentDb.Execute ssql
End Sub

kent_73

todaynew 发表于 2011-6-30 09:21
Private Sub Command8_Click()
Dim str As String
str = "True "

谢谢斑竹。
但是还是可以重复追加的

todaynew

kent_73 发表于 2011-6-30 09:51
谢谢斑竹。
但是还是可以重复追加的

Sub insertTb(frm As Form)
Dim ssql As String
Dim strwh As String
ssql = frm.RecordSource
strwh = frm.Filter
if Dcount("*","结算表",strwh)=0 then
    ssql = Trim(ssql)
    If Right(ssql, 1) = ";" Then
        ssql = Left(ssql, Len(ssql) - 1)
    End If
    ssql = "select 编号,名称,数量,单价 from (" & ssql & ") where " & strwh
    ssql = "INSERT INTO 结算表 ( 编号, 名称, 数量, 单价 ) " & ssql
    CurrentDb.Execute ssql
end if
End Sub

kent_73

todaynew 发表于 2011-6-30 16:52
Sub insertTb(frm As Form)
Dim ssql As String
Dim strwh As String

唉，依葫芦画瓢也不会，帮我再改改吧
Sub insertTb(frm As Form)
Dim ssql As String
Dim strwh As String
ssql = frm.RecordSource
strwh = frm.Filter
if Dcount("*","结算表",strwh)=0 then
    ssql = Trim(ssql)
    If Right(ssql, 1) = ";" Then
        ssql = Left(ssql, Len(ssql) - 1)
    End If
    ssql = "select 编号,名称,数量,单价 from (" & ssql & ") where " & strwh
    ssql = "INSERT INTO 结算表 ( 编号, 名称, 数量, 单价 ) " & ssql
    CurrentDb.Execute ssql

else ’已结算=true ，不更新

esle'如果编号+名称重复，且已结算=false，则更新【数量】和【单价】

ssql = Trim(ssql)
    If Right(ssql, 1) = ";" Then
        ssql = Left(ssql, Len(ssql) - 1)
    End If
   

    CurrentDb.Execute ssql
end if
End Sub

todaynew

kent_73 发表于 2011-7-1 09:49
唉，依葫芦画瓢也不会，帮我再改改吧
Sub insertTb(frm As Form)
Dim ssql As String

Sub insertTb(frm As Form)
Dim ssql(1) As String
Dim strwh As String
Dim rs(1) As New ADODB.Recordset
Dim i As Long
ssql(1) = frm.RecordSource
strwh = frm.Filter
ssql(1) = Trim(ssql(1))
If Right(ssql(1), 1) = ";" Then
     ssql(1) = Left(ssql(1), Len(ssql(1)) - 1)
End If
ssql(1) = "select * from (" & ssql(1) & ") where " & strwh
rs(1).Open ssql(1), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
For i = 1 To rs(1).RecordCount
    ssql(0) = "select * from 结算表 where 编号='" & rs(1)!编号.Value & "' and 名称='" & rs(1)!名称.Value & "'"
    rs(0).Open ssql(0), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If rs(0).RecordCount = 0 Then
        rs(0).AddNew
    End If
    If rs(0)!已结算.Value = False Then
        rs(0)!编号.Value = rs(1)!编号.Value
        rs(0)!名称.Value = rs(1)!名称.Value
        rs(0)!数量.Value = rs(1)!数量.Value
        rs(0)!单价.Value = rs(1)!单价.Value
        rs(0).Update
    End If
    rs(1).MoveNext
    rs(0).Close
Next
rs(1).Close
Set rs(0) = Nothing
Set rs(1) = Nothing
End Sub

kent_73

todaynew 发表于 2011-7-1 10:08
Sub insertTb(frm As Form)
Dim ssql(1) As String
Dim strwh As String

运行时出现错误，点击调试，定位在：
rs(1).Open ssql(1), CurrentProject.Connection, adOpenKeyset, adLockOptimistic

todaynew

kent_73 发表于 2011-7-1 15:07
运行时出现错误，点击调试，定位在：
rs(1).Open ssql(1), CurrentProject.Connection, adOpenKeyset, a ...

ssql语句一修改了呀。ssql(1) = "select * from (" & ssql(1) & ") where " & strwh

也可以用以下函数：
Sub insertTb(frm As Form)
Dim ssql As String
Dim strwh As String
ssql = frm.RecordSource
strwh = frm.Filter
CurrentDb.Execute "delete * from 结算表 where " & strwh & " and 已结算=false"
ssql = Trim(ssql)
If Right(ssql, 1) = ";" Then
     ssql = Left(ssql, Len(ssql) - 1)
End If
strwh = strwh & " and 编号 & 名称 not in (select 编号 & 名称 from 结算表 where 已结算=true)"
ssql = "select * from (" & ssql & ") where " & strwh
ssql = "INSERT INTO 结算表 ( 编号, 名称, 数量, 单价 ) " & ssql
CurrentDb.Execute ssql
End Sub

kent_73

问题解决
谢谢啦   

qczvba

回复 todaynew 的帖子

版主高人也，无语，佩服。