clean insert into code but no data reach table (1 Viewer)

[mhakim]

Dears
good day

i have clean insert into code i used with empty 2 test tables and it work very good and the data go there smothly

but when i move to orignal tabels which have same cloumns names , but original tables has some data inside
it will run code also with no error but no data goes to tables

 

[CJ_London]

If you need help you need to provide more detail - what is the sql? And generated in the query builder or vba? Also some example data that doesn’t import.

best guess at the moment since you are not getting an error is your doesn’t meet the criteria requirements

 

[ebs17]

it will run code also with no error

How do you know?
Is the error display switched on and therefore possible?

 

[Pat Hartman]

Are you suppressing warning messages?

 

[jdraw]

We need more context in order to provide focused advice. More details; sample code; ... needed.

 

[mhakim]

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

 

[ebs17]

In any case, use dbFailOnError to show messages.

db.Execute header_jv, dbFailOnError

There are several reasons for data entry failure:
- Index errors, violations of a unique index
- Violations of defined validity rules
- Violations of the set referential integrity

 

[CJ_London]

few fields in there you are passing as text when would have thought they would be numeric -for example

,'" & Credit_value & "','" & VAT_VALUE & "'

 

[moke123]

Add a few debug.prints of your string variables so you can see what they resolved to, and hopefully spot any errors.

 

[The_Doc_Man]

I will simply add to what everyone else has suggested. If you don't have error messages enabled, and if you don't use dbFailOnError for the .Execute operation, you are missing crucial information that you should pass along to us to diagnose your problem. It is imperative that when something doesn't work, you need to tell us what you expected and what you got INCLUDING error messages. Error messages are your friends. And you can turn them off when you are finished debugging. (Hint: You are NEVER finished debugging if the code is still in use.)

Now, I'll add another comment that is a "code organizational" tip. In the code segment you showed us, you intermixed DIM statements and executable statements. This makes it harder for others to read - though I believe I understand why you laid it out that way. You were keeping variable declarations near data "fill" actions. For the rest of us, the intermixed DIM statements can be a distraction when trying to understand code that is actually quite linear for the most part.

By general practice, ALL of your DIM statements within a subroutine should be at the top of the routine BEFORE the first line of executable code. The compiler will correctly handle it anywhere since DIMs aren't executable instructions, but it is confusing for us humans who are far more accustomed to the general preferred layout of DIM statements.

Now, another question/comment that is organizational. By any chance is that code sample running as part of the class module of form "Bills-or-Expenses"? If so, you could use Me. rather than Forms("Bills-or-Expenses") and it would involve FAR less typing.

Alternatively, if this is NOT part of the class module for "Bills-and-Expenses", you can still save some typing by adding two lines:

DIM frmBE as Access.Form
...
SET frmBE = Forms("Bills-or-Expenses")

Then after that, instead of writing out Forms("Bills-or-Expenses") every time, you use frmBE which (a) requires no special characters when typing and (b) is faster to execute.. because Forms("Bills-or-Expenses") has to look up the form every time whereas the SET statement locks down an object variable to point to the form and there is no searching. It's a matter of internal efficiency.

 

[Pat Hartman]

Are you aware that you can run append queries that include a value for the identity column? That could simplify the conversion process for you. You seem to be splitting one record into two tables, a header and a details and further splitting to add three details for one header. And, you are doing it from a form so for this to actually work, you would need to scroll to each record and then run this procedure.

Using append queries that use the existing autonumber for the identity and FK, you would still need four of them but you would do the whole file in one go instead of having to scroll through record by record. If the target table is empty, then there will be no identity conflicts. If there is existing data, then it is more of a challenge. Figure out what the highest value is. Say it is 1593. In your append query, add 1600 to the existing Identity to make sure it doesn't conflict. Just also do the same in the three child appends for the FK.

It also looks like you are duplicating data in the child table that belongs in the header table so I'm not sure that the split is correct.

And finally, prefixing column names is simply annoying and unnecessary.