当子表存在主表的外键时,用代码向主表和子表添加记录时,需要先获得主表记录增加时自动编号字段的ID值,然后依据这个ID值,向子表添加记录。为此,定义了一个函数,利用Bookmark,同步取得这个ID值!表的名字:库存变动表;表的字段:库存ID(自动编号字段)、货品所在位置 、操作员、库变动时间、备注。
Public Function inserSet_getID( _
goodsAdress As String, _
operater As String, _
goodsInOut As String, _
changeDate As Date, _
mark As String _
) As String '向主表插入数据并返回自动编号值
Dim rstParent As dao.Recordset
Dim dbrst As Database
Dim rstID As String
Set dbrst = CurrentDb
Set rstParent = dbrst.OpenRecordset("库存变动表", dbOpenDynaset)
With rstParent
.AddNew
!货品所在位置 = goodsAdress
!操作员 = operater
!库变动类别 = goodsInOut
!库变动时间 = changeDate
!备注 = mark
.Update
.Bookmark = .LastModified
rstID = rstParent("库存ID")
End With
rstParent.Close
inserSet_getID = rstID
End Function
自建一个表,名字、字段、同上述,自建一个窗体,在窗体增加一个按钮,的按钮点击事件中,调用该函数:MsgBox inserSet_getID("仓库1", "李丽", "入库", "2015 - 5 - 12", "平衡")
测试能够正确返回ID值