error in insert into table

[megatronixs]

Hi all,

I have the below code to insert some data from a search form into the table according the record_id that is on the current open record from the from.

CurrentDb.Execute "INSERT INTO tbl_recipients (forname, surname, employee_id, team_leader) Values ('" & strListResult & "', '" & strListResult2 & "', '" & strListResult3 & "', '" & strListResult4 & "') From tbl_recipients WHERE [record_id] = " & formrecordid & ";"

it tells me the ";" is missing, but it is there :-(

Any clue where I go wrong?

greetings.

 

[Ranman256]

you should use queries instead of sql. A query will show you the error, but
im guessing [employee_id] is NOT a string, but numeric so remove the quotes:

"INSERT INTO tbl_recipients (forname, surname, employee_id, team_leader)
Values ('" & strListResult & "', '" & strListResult2 & "', " & strListResult3 & ", '" & strListResult4 & "') From tbl_recipients WHERE [record_id] = " & formrecordid

 

[plog]

An INSERT INTO doesn't have a FROM and a WHERE. I know you can do a SELECT INTO (https://www.w3schools.com/sql/sql_insert_into_select.asp), but you don't have a SELECT clause. What exactly are you trying to insert? New data or duplicate an existing record?

 

[megatronixs]

Hi all, still the missing ;

 

[megatronixs]

Hi Plog,
I have a form where I have a subform where I get results from a search. I want to use this data to insert into another subform that is also on the main form. The table is the one from the query above. I guess I confused the query and it lacks the SELECT INTO

 

[plog]

I'm Lost.

If you have all the values that are going into a table, simply use an INSERT INTO. If you don't have the values, but do have the primary key of a record you want to duplicate, use a SELECT INTO.

 

[megatronixs]

Hi all,

I managed to solve it by creating a temporally table where I first add the values from the search table. Then I use a query to insert the record on the correct table from the record_id value in the main form.

Greetings.