Sub Purchase_JV_Post_1()
Dim db As DAO.Database
Dim header_jv As String
Dim Details_jv_1 As String
Dim Details_jv_2 As String
Dim Details_jv_3 As String
Dim jv_post_check As String
'Header Variables
Dim Bill_Number As String
Dim branch_id As Integer
Dim Bill_Date As Date
Dim Bill_DES As String
Dim Bill_REF As String
Bill_Number = Forms("Bills-or-Expenses").Bill_Number
branch_id = Forms("Bills-or-Expenses").branch_id
Bill_Date = Forms("Bills-or-Expenses").Bill_Date
Bill_DES = Forms("Bills-or-Expenses").Bill_DES
Bill_REF = Forms("Bills-or-Expenses").Bill_REF
'JV DETAILS_1
Dim Account_Num As String
Dim Thirdparty_id As Integer
Dim Credit_value As Currency
Dim VAT_VALUE As Currency
Account_Num = Forms("Bills-or-Expenses").ACC_NUM
Thirdparty_id = Forms("Bills-or-Expenses").Vendor_Id
Credit_value = Forms("Bills-or-Expenses").Thirdparty_value
VAT_VALUE = Forms("Bills-or-Expenses").VAT_VALUE
'JV DETAILS_2
Dim Account_Num_2 As String
Dim Debit_value As Currency
Account_Num_2 = Forms("Bills-or-Expenses").Debit_ACC_NUM
Debit_value = Forms("Bills-or-Expenses").Debit_value
'JV DETAILS_3
Dim Account_Num_3 As String
Dim Debit_value_3 As Currency
Account_Num_3 = Forms("Bills-or-Expenses").VAT_ACC_NUM
Debit_value_3 = Forms("Bills-or-Expenses").VAT_VALUE
Dim BillID As Integer
BillID = Forms("Bills-or-Expenses").Bill_Id
Set db = CurrentDb
'insert data to header table one line
header_jv = "INSERT INTO TransactionsHead (REF, branch_id,JVDate, Description_english,Description_arabic,type,Status)" & _
"VALUES ('" & Bill_Number & "', '" & branch_id & "','" & Bill_Date & "','" & Bill_DES & "','" & Bill_REF & "','PINV','Pending');"
db.Execute header_jv
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 TransactionDetails (T_JV_Number,T_Account_Number ,T_acc_sub_number,branch_id, doc_ref,Description_english ,Description_arabic,Credit,VAT_VALUE,Debit)" & _
" VALUES (" & newID & ",'" & Account_Num & "','" & Thirdparty_id & "', '" & branch_id & "','" & Bill_Number & "','" & Bill_DES & "','" & Bill_REF & "','" & Credit_value & "','" & VAT_VALUE & "',0);"
db.Execute Details_jv_1
'insert data to details table line 2
Details_jv_2 = "INSERT INTO TransactionDetails (T_JV_Number,T_Account_Number ,T_acc_sub_number,branch_id, doc_ref,Description_english ,Description_arabic,Credit,VAT_VALUE,Debit )" & _
" values (" & newID & ",'" & Account_Num_2 & "',NULL, '" & branch_id & "','" & Bill_Number & "','" & Bill_DES & "','" & Bill_REF & "',0,0, '" & Debit_value & "') ; "
db.Execute Details_jv_2
'insert data to details table line 3
Details_jv_3 = "INSERT INTO TransactionDetails (T_JV_Number,T_Account_Number ,T_acc_sub_number,branch_id, doc_ref,Description_english ,Description_arabic,Credit,VAT_VALUE ,Debit)" & _
"VALUES (" & newID & ",'" & Account_Num_3 & "',NULL, '" & branch_id & "','" & Bill_Number & "','" & Bill_DES & "','" & Bill_REF & "',0,0, '" & Debit_value_3 & "');"
db.Execute Details_jv_3
'jv_post_check = "update Bill_Head set Bill_JV_POST = yes where Bill_Id = " & BillID
'db.Execute jv_post_check
Set db = Nothing
MsgBox "Journal Voucher Created successfully "
End Sub
this code is woking good and data goes perfect to 2 tables as TEST (caled test_header and other one called test_transactions )
but when i chose another 2 tables represent Real tables as seen in the code above it will run code no problem but no data goes there
the difference between test tables and the tables in code is that
tables in the code already have data and receive data from different screens