Insert Record in other table from form

[psatkar]

Hi,

I have following 3 tables
1. Payees - Payee_ID (Autonumber), Payee_Name (Text)
2. Transactions - various columns (one of the columns is Payee_id which is linked to table Payees)
3. Security - various columns

I have created a form which is bound to table Security. On the form there is a field call security_name. While adding the security details, I want to insert a record in Payees table with Payee_name as Security_Name. I am able to do it by writing code "CurrentDb.Execute "Insert into Payees.......etc" in the AfterUpdate event of the form.

Now, while being in the same event, I want to pick up the Payee_Id (which would have got generated as autonumber when I inserted the record in Payees table) and insert a separate record in Transactions table with one of the column values as Payee_Id (newly generated one in Payees table).

Can you please guide me as to how I should read the newly added record in Payees table while I am in AfterUpdate event of my form?

Thanks in advance.

Regards,
--Prasad

ps: For your reference, I am copying the code which I have written to insert records. What I need is how to locate the Payee_ID corresponding to newly inserted record from Payees table before I insert record in Transactions table :

    Dim varNewRecord As Integer
    Dim varOpt  As Integer
    Dim varPayeeId As Integer
 
    If Me.NewRecord Then
        varNewRecord = 1
    End If
 
    DoCmd.RunCommand acCmdSaveRecord
    If Me.Frame111 = 1 Then
        Me.Sec_Cum_Yes_No = -1
    Else
        Me.Sec_Cum_Yes_No = 0
    End If
 
    If varNewRecord = 1 Then
        If Not IsNull(Sec_debit_Acct) Then
            CurrentDb.Execute "Insert into Payees (Payee_Name) Values (" & Chr(34) & [Sec_Name] & Chr(34) & ")"
 
            CurrentDb.Execute "Insert into Transaction_Details (Trans_Type, Trans_Date, Trans_Ref, Trans_Acct, Trans_Currency, Payee, " _
            & "Trans_Amt_Spent, Trans_Amt_Received, Trans_Memo, Trans_Category, Trans_Classification, Trans_Balance, Trans_Reconcile_Flag, Trans_Transfer_To_Acct, Trans_Transfer_Trans_Id )" _
            & " Values (2, " & Chr(34) & [Sec_Purchase_Date] & Chr(34) & "," & Chr(34) & Chr(34) & "," & [Sec_debit_Acct] & "," & Chr(34) & "INR" & Chr(34) & ", 12" _
            & "," & [Sec_Pur_Value] & ", " & Chr(34) & Chr(34) & "," & Chr(34) & [Sec_Remarks] & Chr(34) & ", 106, " & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ", 2, " & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ")"
        End If
    End If

 

[psatkar]

Hi All,

I could fogure out the solution myself after doing some trial and error. I can use DLookup function and get the payee_id.

Thanks...
Prasad