子窗体数据拷贝到另一表中

dalianliuliu　发表于：2011-06-09 09:52:30 　

复制

在窗体 [F00_ItemMaster]中有按钮[Delete]

想实现功能：

点击按钮[Delete]将主窗体中的记录自动拷贝到表 [T04_ItemMaster]中,

原记录内容变为Delete

目前,只实现点击[Delete]记录信息变为Delete

拷贝移动不会做！

密码123

请求救援！

点击下载此附件

Top

曹光耀发表于：2011-06-09 10:43:48

Sub removeRS() '先复制再删除cgy2011-6-9
Dim i As Long
Dim str As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Q00_ItemMaster", CurrentProject.Connection, 3, 3

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM T04_ItemMaster", CurrentProject.Connection, 3, 3

If rs.RecordCount < 1 Then
Exit Sub
End If

rs.MoveFirst
For i = 0 To rs.RecordCount - 1
    rs1.AddNew
        rs1("No") = rs("No")
        rs1("K3_Code") = rs("K3_Code")
        rs1("K3_Code2") = rs("K3_Code2")
        rs1("Part#") = rs("Part#")
        rs1("ItemCode") = rs("ItemCode")
        rs1("RT_Code") = rs("RT_Code")
        rs1("ItemName") = rs("ItemName")
        rs1("ItemName(Chinese)") = rs("ItemName(Chinese)")
        rs1("ManageUnit") = rs("ManageUnit")
        rs1("Unit_1st") = rs("Unit_1st")
        rs1("Unit_2st") = rs("Unit_2st")
        rs1("Packing") = rs("Packing")
        rs1("PackingUnit") = rs("PackingUnit")
        rs1("Con_Unit") = rs("Con_Unit")
        rs1("Subdivision") = rs("Subdivision")
        rs1("Source(factory or department)") = rs("Source(factory or department)")
        rs1("Factory_Code") = rs("Factory_Code")
        rs1("Agent1") = rs("Agent1")
        rs1("Agent2") = rs("Agent2")
        rs1("Agent3") = rs("Agent3")
        rs1("Source(internal or abroad)") = rs("Source(internal or abroad)")
        rs1("Purpose_Code") = rs("Purpose_Code")
        rs1("To_Department") = rs("To_Department")
        rs1("Class") = rs("Class")
        rs1("classification") = rs("classification")
        rs1("Product_Cate") = rs("Product_Cate")
        rs1("Department") = rs("Department")
        rs1("Production_Series") = rs("Production_Series")
        rs1("Packaging_Measure") = rs("Packaging_Measure")
        rs1("Final_sale") = rs("Final_sale")
        rs1("Final_sale_Reason") = rs("Final_sale_Reason")
        rs1("Duty") = rs("Duty")
        rs1("Start_Date") = rs("Start_Date")
        rs1("Stop_Date") = rs("Stop_Date")
        rs1("PM") = rs("PM")
        rs1("Memo") = rs("Memo")
        rs1("Change_Date") = rs("Change_Date")
        rs1("tool") = rs("tool")
    rs1.Update
    'rs.Delete'是为删除子窗体中的记录
rs.MoveNext
Next

rs.Close
Set rs = Nothing
rs1.Close
Set rs1 = Nothing
End Sub

点击下载此附件

Top

dalianliuliu 发表于：2011-06-09 11:03:50

太厉害了！

非常感谢！

但和我要的效果有点区别！

我想要的是：

点击按钮[Delete]将主窗体中的这1条记录自动拷贝追加到表 [T04_ItemMaster]中,(但附件实现的是将子窗体中的所有记录拷贝到表[T04_ItemMaster]中)

把删除前的记录内容保存在[T04_ItemMaster]中,以备日后查找使用

请帮忙！

谢谢！

Top

dalianliuliu 发表于：2011-06-09 14:07:58

哈哈哈

问题解决了

我把这4句不要了

rs.MoveFirst
For i = 0 To rs.RecordCount - 1

rs.MoveNext
Next

把所有的rs("No").....改为Me![No].....

非常感谢！

结帖给分！

Top

曹光耀发表于：2011-06-10 08:57:55

修改了一下,红色处为添加的,是为循环判断,是否为当前行,请看注释

Sub removeRS() '先复制再删除cgy2011-6-9
Dim i As Long
Dim str As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Q00_ItemMaster", CurrentProject.Connection, 3, 3

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM T04_ItemMaster", CurrentProject.Connection, 3, 3

If rs.RecordCount < 1 Then
Exit Sub
End If

rs.MoveFirst
For i = 0 To rs.RecordCount - 1
    If rs("NO") = Me.No Then ' 这行是添加的,限定只删除当前行cgy2011-6-10
        rs1.AddNew
            rs1("No") = rs("No")
            rs1("K3_Code") = rs("K3_Code")
            rs1("K3_Code2") = rs("K3_Code2")
            rs1("Part#") = rs("Part#")
            rs1("ItemCode") = rs("ItemCode")
            rs1("RT_Code") = rs("RT_Code")
            rs1("ItemName") = rs("ItemName")
            rs1("ItemName(Chinese)") = rs("ItemName(Chinese)")
            rs1("ManageUnit") = rs("ManageUnit")
            rs1("Unit_1st") = rs("Unit_1st")
            rs1("Unit_2st") = rs("Unit_2st")
            rs1("Packing") = rs("Packing")
            rs1("PackingUnit") = rs("PackingUnit")
            rs1("Con_Unit") = rs("Con_Unit")
            rs1("Subdivision") = rs("Subdivision")
            rs1("Source(factory or department)") = rs("Source(factory or department)")
            rs1("Factory_Code") = rs("Factory_Code")
            rs1("Agent1") = rs("Agent1")
            rs1("Agent2") = rs("Agent2")
            rs1("Agent3") = rs("Agent3")
            rs1("Source(internal or abroad)") = rs("Source(internal or abroad)")
            rs1("Purpose_Code") = rs("Purpose_Code")
            rs1("To_Department") = rs("To_Department")
            rs1("Class") = rs("Class")
            rs1("classification") = rs("classification")
            rs1("Product_Cate") = rs("Product_Cate")
            rs1("Department") = rs("Department")
            rs1("Production_Series") = rs("Production_Series")
            rs1("Packaging_Measure") = rs("Packaging_Measure")
            rs1("Final_sale") = rs("Final_sale")
            rs1("Final_sale_Reason") = rs("Final_sale_Reason")
            rs1("Duty") = rs("Duty")
            rs1("Start_Date") = rs("Start_Date")
            rs1("Stop_Date") = rs("Stop_Date")
            rs1("PM") = rs("PM")
            rs1("Memo") = rs("Memo")
            rs1("Change_Date") = rs("Change_Date")
            rs1("tool") = rs("tool")
        rs1.Update
        rs.Delete '是为删除子窗体中的记录
    End If '这行是添加的cgy2011-6-10
rs.MoveNext
Next

rs.Close
Set rs = Nothing
rs1.Close
Set rs1 = Nothing
End Sub

Top

dalianliuliu 发表于：2011-06-10 10:01:10

太感动了！

在其他论坛没有遇到这么负责的老师！

谢谢！

老师的方法很好，但我表的结构是：

有很多部门,各部门信息No都从1开始依次排列，

所以[No+部门]是唯一项

老师的方法是需要判断一下，再拷贝移动

我的是不需判断,直接等于主窗体数据

功能是实现了

老师的是规范的方法吧！

再次表示感谢！

Top

总记录：5篇页次：1/1 9 1 :

Access交流中心

子窗体数据拷贝到另一表中