Office中国论坛/Access中国论坛

标题: 如何将当前子窗体的数据有条件的保存到表中 [打印本页]

作者: kent_73 时间: 2011-6-30 08:49
标题: 如何将当前子窗体的数据有条件的保存到表中
如何将当前子窗体的数据有条件的保存到表中？
具体请看附件。
谢谢
[attach]45972[/attach]

作者: todaynew 时间: 2011-6-30 09:21

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 时间: 2011-6-30 09:51

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

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

作者: todaynew 时间: 2011-6-30 16:52
本帖最后由 todaynew 于 2011-6-30 16:53 编辑

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 时间: 2011-7-1 09:49

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 时间: 2011-7-1 10:08
本帖最后由 todaynew 于 2011-7-1 11:47 编辑

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 时间: 2011-7-1 15:07

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
[attach]45985[/attach]

作者: todaynew 时间: 2011-7-1 15:31

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 时间: 2011-7-1 16:09
问题解决
谢谢啦

作者: qczvba 时间: 2011-7-1 16:39
回复 todaynew 的帖子

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

作者: todaynew 时间: 2011-7-1 16:56

kent_73 发表于 2011-7-1 16:09
问题解决
谢谢啦

注意两个函数处理的思路上的差异，用记录集时，采取追加+更新的方法，用操作查询时，采取删除+追加。这两种思路在处理此类问题中是惯常手段。

作者: st1988330 时间: 2013-4-27 13:20

todaynew 发表于 2011-7-1 15:31
ssql语句一修改了呀。ssql(1) = "select * from (" & ssql(1) & ") where " & strwh

也可以用以下函数 ...

代码看的好晕，厉害啊，好想学，不知该如何下手

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)