通过VBA将一个表中创建的记录的ID传递给另一个表 [英] Passing ID of created record in one Table to another Table by VBA

问题描述

我有2张桌子



tlbLeave

tlbLeaveDetails



也a表单的形式是tblLeave



但是我解除了所有盒子只是我不想使用任何子表单



我创建了一个VBA代码来创建新记录

I have 2 tables

tlbLeave
tlbLeaveDetails

also a Form the sorce of the form is tblLeave

but i unbound all Boxes just i dont want to use any subform

I created a VBA code to create new records

Private Sub addrecord_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim i As Integer


    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblLeave")
    Set rs2 = db.OpenRecordset("tblLeaveDate")


        rs.AddNew

        rs!EmployeeNr = txtEmployeeNr
        rs!ReasonType = txtReasonType
        rs!Details = txtDetails
        rs!RTWCompleted = txtRTW

        rs.Update

    For i = 0 To txtDays

        rs2.AddNew

        rs2!LID = Me.LID
        rs2!CalendarDate = txtStartDate + i


        rs2.Update

    Next i


    ' Clean Up and Close
    rs.Close
    rs2.Close

    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing

    MsgBox "The Adding Process was Successful"
End Sub

这两个表中ID都设置为自动

现在问题是

in both tables the ID are set to Automatic
now the problem is

rs2!LID = Me.LID

大部分时间它都是通过这一点但是在记录中的tblLeaveDetails应该在哪里是LID什么都没有



有时它会显示错误对象被删除



我是新手这个Access VBA及其旧的2003 Acces



我只是想通过LID什么ID创建自动贩卖机创建离开记录到LeaveDate以确保在两个表之间进行关联，我知道哪些日期属于什么离开等等



任何可以帮助????

Most of the time it's Going thru this point but in tblLeaveDetails in record where should be LID is nothing

sometimes it will show a error Object is Deleted

I'm New to this Access VBA and its old 2003 Acces

and i just want to pass LID whats ID created automatic after creating Leave Record to LeaveDate to make sure reletion is made between two tables and i know what dates belong to what Leave ect

Any on can help ????

推荐答案

OK NVM我找到解决方案



我创建和对象X

OK NVM i find the solution

I create and Object X

Dim x As Integer

然后我做那个

then i make that

x = db.OpenRecordset("SELECT @@Identity")(0)

我忘了你可以使用那个Qury然后我将X传递给

I just forgot you can use that Qury then i pass the X to

rs!LID = x

rs2!LID = x

和vualaaaa所有工作正常我的ID从tblLeave中的新行被传递到tblLeaveDate中的LID字段



我有我的关系：）

and vualaaaa all working fine my ID from new row in tblLeave is passed to LID field in tblLeaveDate

and i have my relation :)

这篇关于通过VBA将一个表中创建的记录的ID传递给另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！