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

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

VB
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



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

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