insert into code (1 Viewer)

[mhakim]

private sub ()

Dim db As DAO.Database

Dim header_jv As String
Dim Details_jv_1 As String
Dim Details_jv_2 As String

'Header Variables
Dim Bill_Number As String
Dim branch_id As Integer
Dim Bill_Date As Date
Bill_Number = Forms("Bills-or-Expenses").Bill_Number
branch_id = Forms("Bills-or-Expenses").branch_id
Bill_Date = Forms("Bills-or-Expenses").Bill_Date


'JV DETAILS_1
Dim Account_Num As String
Account_Num = Forms("Bills-or-Expenses").ACC_NUM


'JV DETAILS_2
Dim Account_Num_2 As String
Account_Num_2 = Forms("Bills-or-Expenses").Debit_ACC_NUM


Set db = CurrentDb

'insert data to header table one line
header_jv = "INSERT INTO test_table_head (REF, branch_id,JVDate)" & _
"VALUES ('" & Bill_Number & "', '" & branch_id & "','" & Bill_Date & "');"
db.Execute header_jv


'insert data to details table line 1
Details_jv_1 = "INSERT INTO test_table_transactions (T_JV_Number,T_Account_Number )" & _
" VALUES (@@Identity,'" & Account_Num & "');"

db.Execute Details_jv_1

'insert data to details table line 2
Details_jv_2 = "INSERT INTO test_table_transactions (T_JV_Number,T_Account_Number )" & _
" values (@@IDENTITY ,'" & Account_Num_2 ) ; "
db.Execute Details_jv_2


Set db = Nothing

MsgBox "The data has been saved successfully."

end sub

problem is that third insert into statement in red color doesnot work

it will allow me to excute only one line the secone insert into in blue color in test_table_transactions

 

[cheekybuddha]

Collect the value of @@IDENTITY before the next two inserts, otherwise the third picks up the ID of the second:

' ...
'insert data to header table one line
header_jv = "INSERT INTO test_table_head (REF, branch_id,JVDate)" & _
"VALUES ('" & Bill_Number & "', '" & branch_id & "','" & Bill_Date & "');"
db.Execute header_jv, dbFailOnError

Dim newID As Long
With db.OpenRecordset("SELECT @@IDENTITY;")
  newID = .Fields(0)
  .Close
End With

'insert data to details table line 1
Details_jv_1 = "INSERT INTO test_table_transactions (T_JV_Number,T_Account_Number )" & _
" VALUES (" & newID & ",'" & Account_Num & "');"
db.Execute Details_jv_1, dbFailOnError

'insert data to details table line 2
Details_jv_2 = "INSERT INTO test_table_transactions (T_JV_Number,T_Account_Number )" & _
" values (" & newID & ",'" & Account_Num_2 ) ; "
db.Execute Details_jv_2, dbFailOnError

 

[mhakim]

Collect the value of @@IDENTITY before the next two inserts, otherwise the third picks up the ID of the second:

' ...
'insert data to header table one line
header_jv = "INSERT INTO test_table_head (REF, branch_id,JVDate)" & _
"VALUES ('" & Bill_Number & "', '" & branch_id & "','" & Bill_Date & "');"
db.Execute header_jv, dbFailOnError

Dim newID As Long
With db.OpenRecordset("SELECT @@IDENTITY;")
  newID = .Fields(0)
  .Close
End With

'insert data to details table line 1
Details_jv_1 = "INSERT INTO test_table_transactions (T_JV_Number,T_Account_Number )" & _
" VALUES (" & newID & ",'" & Account_Num & "');"
db.Execute Details_jv_1, dbFailOnError

'insert data to details table line 2
Details_jv_2 = "INSERT INTO test_table_transactions (T_JV_Number,T_Account_Number )" & _
" values (" & newID & ",'" & Account_Num_2 ) ; "
db.Execute Details_jv_2, dbFailOnError

Excellent thanks