error in insert into table (1 Viewer)

M

megatronixs

Registered User.

Local time

Today, 11:42

Joined

Aug 17, 2012

Messages

719

• Nov 29, 2017

• #1

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.

Code:Copy to clipboard

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

Well-known member

Local time

Today, 05:42

Joined

Apr 9, 2015

Messages

4,337

• Nov 29, 2017

• #2

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

 

P

plog

Banishment Pending

Local time

Today, 04:42

Joined

May 11, 2011

Messages

11,645

• Nov 29, 2017

• #3

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?

 

M

megatronixs

Registered User.

Local time

Today, 11:42

Joined

Aug 17, 2012

Messages

719

• Nov 29, 2017

• #4

Hi all, still the missing ;

 

M

megatronixs

Registered User.

Local time

Today, 11:42

Joined

Aug 17, 2012

Messages

719

• Nov 29, 2017

• #5

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

 

P

plog

Banishment Pending

Local time

Today, 04:42

Joined

May 11, 2011

Messages

11,645

• Nov 29, 2017

• #6

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.

 

M

megatronixs

Registered User.

Local time

Today, 11:42

Joined

Aug 17, 2012

Messages

719

• Dec 1, 2017

• #7

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.