Click here to Skip to main content
15,883,606 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 2 tables


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!EmployeeNr = txtEmployeeNr
        rs!ReasonType = txtReasonType
        rs!Details = txtDetails
        rs!RTWCompleted = txtRTW


    For i = 0 To txtDays


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


    Next i

    ' Clean Up and Close

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

    MsgBox "The Adding Process was Successful"
End Sub

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

rs2!LID = Me.LID

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 ????

1 solution

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)

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

rs!LID = x

rs2!LID = x

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

and i have my relation :)
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900