ERROR 3137 (1 Viewer)

[mhakim]

here is the code
trying to insert two line in same time
by the way one line is working but when trying to add another line it give error 3137


"INSERT INTO test_table_transactions (T_Account_Number ,T_acc_sub_number,branch_id)" & _
" VALUES ('" & Account_Num & "','" & Thirdparty_id & "', '" & branch_id & "'), " & _
" ('" & Account_Num_2 & "',NULL, '" & branch_id_2 & "') ; "

 

[cheekybuddha]

Not possible in Access using the VALUES form of the INSERT statement.

You will need to use 2 INSERT statements unless you are able to convert to:

INSERT INTO test_table_transactions (T_Account_Number ,T_acc_sub_number,branch_id)
SELECT .... 2 records FROM ....

 

[The_Doc_Man]

Just for clarity: The INSERT INTO ... VALUES syntax is ALWAYS a single-record insertion. One record per SQL activation. David's suggested response uses the other syntax: INSERT INTO ... SELECT .... FROM - which allows as many insertions as were implied in the SELECT.

 

[mhakim]

Just for clarity: The INSERT INTO ... VALUES syntax is ALWAYS a single-record insertion. One record per SQL activation. David's suggested response uses the other syntax: INSERT INTO ... SELECT .... FROM - which allows as many insertions as were implied in the SELECT.

i have seach online and google bard i found that you can do same as i do

but how i will use select the data i want to record on a form not on table

 

[cheekybuddha]

Just for clarity: The INSERT INTO ... VALUES syntax is ALWAYS a single-record insertion

Not always!
MySQL, Oracle, Postgres (I think) allow the syntax used by the OP where you can use multiple comma delimited sets of VALUES

Edited to add: I think also possible in SQLServer too since SQLServer 2008

 

[cheekybuddha]

but how i will use select the data i want to record on a form not on table

Then, in Access, you must just insert one record at a time.

 

[cheekybuddha]

Just shows that you really need to double-check AI results which are presented so definitively.

It's the new equivalent of "Well it must be true because I read it on the internet"!

 

[mhakim]

Then, in Access, you must just insert one record at a time.

thanks dear
but i have to search more because i need to but at least two lines like accounting entries a line for debit and a line for credit

so if you have clear idea how to use select with insert into to put two lines

 

[cheekybuddha]

With CurrentDb
  strSQL = "INSERT INTO test_table_transactions (T_Account_Number ,T_acc_sub_number,branch_id)" & _
           " VALUES ('" & Account_Num & "','" & Thirdparty_id & "', '" & branch_id & "');"
  .Execute strSQL, dbFailOnError
  If .RecordsAffected = 1 Then
    strSQL = "INSERT INTO test_table_transactions (T_Account_Number ,T_acc_sub_number,branch_id)" & _
             " VALUES ('" & Account_Num_2 & "',NULL, '" & branch_id_2 & "') ; "
    .Execute strSQL, dbFailOnError
  End If
End With

Or you can use a transaction to wrap the two statements.

 

[The_Doc_Man]

For absolute clarity, do it one insert at a time. Like David is suggesting. Is it SO hard to just repeat the INSERT INTO ... VALUES statement once for each of two records?

For the record, I was unaware that external SQL engines allowed that. Access does not. When I worked with ORACLE, it wasn't with an Access FE.

 

[cheekybuddha]

Looks like this syntax only available as of Oracle 23c