Help Please : Insert Into Statement - A2000

  • Thread starter Thread starter Mike Wilson
  • Start date Start date
M

Mike Wilson

I seem to be going around in circles with this and would appreciate any
help or guidelines

I want to append selected product lines from one table to another without
transferring all the table fields and without creating an append query.

My attempts at various codings for this have only achieved a full
transfer.
"Docmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item
weight])"

FROM tblProducts.[ProductId, tblProducts.ProductName, tblProducts.[Item
Weight]] WHERE Select = Yes

"Select" is a checkbox used to confirm Product selection


Thanks again for all the assistance


Mike
 
The SQL statement should be

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item weight]
SELECT tblProducts.ProductId, tblProducts.ProductName, tblProducts.[Item Weight]
FROM tblProduct
WHERE tblProducts.[Select]=Ye

When I'm not sure, I build the query in design view and then just copy the SQL

You might also try building the SQL statement outside the RunSQL command

Dim strSQL As Strin
strSQL = "INSERT INTO tblOrderD1 ( .....
DoCmd.RunSQL strSQ

Hope this helps

Howard Brod

----- Mike Wilson wrote: ----

I seem to be going around in circles with this and would appreciate any
help or guideline

I want to append selected product lines from one table to another without
transferring all the table fields and without creating an append query

My attempts at various codings for this have only achieved a full
transfer
"Docmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item
weight])

FROM tblProducts.[ProductId, tblProducts.ProductName, tblProducts.[Item
Weight]] WHERE Select = Ye

"Select" is a checkbox used to confirm Product selectio


Thanks again for all the assistanc


Mik
 
Howard, thank you for your help, I am still having trouble
with this to the extent that I can't get any further than a
debug advising incorrect Insert into or Select Statement
I've tried amending to :


Docmd.RunSQL "INSERT INTO tblOrderD1
( ProductId, ProductName, [Item weight])"
Docmd.RunSQL "SELECT tblProducts.ProductId,
tblProducts.ProductName, tblProducts.[Item Weight]]
FROM tblProducts"

WHERE tblProducts.[Select] = Yes

and I get "Variable not defined" on the Where line

The query design and copy sql gives

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item Weight] )
SELECT tblProducts.ProductId, tblProducts.ProductName,
tblProducts.[Item Weight] FROM tblProducts
WHERE (((tblProducts.[Select])=Yes));

Any idea on where I am doing wrong ?

thanks

Mike
 
if the SQL in your last post works in the append query, it should work when
run from VBA, as well. i'm not sure if you posted your VBA code
character-by-character, but here's how i would clean up the posted SQL
statement for use in VBA:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=Yes", _
False

if that doesn't work, one last try:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=True",
_
False

omit the final , False from the function if you're running this code in
a transaction.

hth


Mike Wilson said:
Howard, thank you for your help, I am still having trouble
with this to the extent that I can't get any further than a
debug advising incorrect Insert into or Select Statement
I've tried amending to :


Docmd.RunSQL "INSERT INTO tblOrderD1
( ProductId, ProductName, [Item weight])"
Docmd.RunSQL "SELECT tblProducts.ProductId,
tblProducts.ProductName, tblProducts.[Item Weight]]
FROM tblProducts"

WHERE tblProducts.[Select] = Yes

and I get "Variable not defined" on the Where line

The query design and copy sql gives

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item Weight] )
SELECT tblProducts.ProductId, tblProducts.ProductName,
tblProducts.[Item Weight] FROM tblProducts
WHERE (((tblProducts.[Select])=Yes));

Any idea on where I am doing wrong ?

thanks

Mike




The SQL statement should be:

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item weight])
SELECT tblProducts.ProductId, tblProducts.ProductName, tblProducts.[Item Weight]]
FROM tblProducts
WHERE tblProducts.[Select]=Yes

When I'm not sure, I build the query in design view and then just copy the SQL.

You might also try building the SQL statement outside the RunSQL command:

Dim strSQL As String
strSQL = "INSERT INTO tblOrderD1 ( ....."
DoCmd.RunSQL strSQL

Hope this helps!

Howard Brody


----- Mike Wilson wrote: -----

I seem to be going around in circles with this and would appreciate any
help or guidelines

I want to append selected product lines from one table to another without
transferring all the table fields and without creating an append query.

My attempts at various codings for this have only achieved a full
transfer.
"Docmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item
weight])"

FROM tblProducts.[ProductId, tblProducts.ProductName, tblProducts.[Item
Weight]] WHERE Select = Yes

"Select" is a checkbox used to confirm Product selection


Thanks again for all the assistance


Mike
Click to expand...
Click to expand...
 
Tina

thank you - I've been a bit stupid trying to break the code onto
different lines.
Your last try worked a treat and I've used it to replace several
queries in my database.

Is the _ meant to be used as a continuation of the code on a
separate line ?

Thank you again, and to everyone else who takes the time to support
the group.

Isn't it great to learn ?

Mike




if the SQL in your last post works in the append query, it should work when
run from VBA, as well. i'm not sure if you posted your VBA code
character-by-character, but here's how i would clean up the posted SQL
statement for use in VBA:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=Yes", _
False

if that doesn't work, one last try:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=True",
_
False

omit the final , False from the function if you're running this code in
a transaction.

hth


Mike Wilson said:
Howard, thank you for your help, I am still having trouble
with this to the extent that I can't get any further than a
debug advising incorrect Insert into or Select Statement
I've tried amending to :


Docmd.RunSQL "INSERT INTO tblOrderD1
( ProductId, ProductName, [Item weight])"
Docmd.RunSQL "SELECT tblProducts.ProductId,
tblProducts.ProductName, tblProducts.[Item Weight]]
FROM tblProducts"

WHERE tblProducts.[Select] = Yes

and I get "Variable not defined" on the Where line

The query design and copy sql gives

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item Weight] )
SELECT tblProducts.ProductId, tblProducts.ProductName,
tblProducts.[Item Weight] FROM tblProducts
WHERE (((tblProducts.[Select])=Yes));
Click to expand...
Click to expand...
 
Mike,

When writing code, you can end a line of code with a space followed by an
underline and then continue the code on the next line. Example:

MsgBox "This is an example of continuing code on the next line" _
,,"Continuation Example"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Mike Wilson said:
Tina

thank you - I've been a bit stupid trying to break the code onto
different lines.
Your last try worked a treat and I've used it to replace several
queries in my database.

Is the _ meant to be used as a continuation of the code on a
separate line ?

Thank you again, and to everyone else who takes the time to support
the group.

Isn't it great to learn ?

Mike




if the SQL in your last post works in the append query, it should work when
run from VBA, as well. i'm not sure if you posted your VBA code
character-by-character, but here's how i would clean up the posted SQL
statement for use in VBA:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=Yes", _
False

if that doesn't work, one last try:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=True",
_
False

omit the final , False from the function if you're running this code in
a transaction.

hth


Mike Wilson said:
Howard, thank you for your help, I am still having trouble
with this to the extent that I can't get any further than a
debug advising incorrect Insert into or Select Statement
I've tried amending to :


Docmd.RunSQL "INSERT INTO tblOrderD1
( ProductId, ProductName, [Item weight])"
Docmd.RunSQL "SELECT tblProducts.ProductId,
tblProducts.ProductName, tblProducts.[Item Weight]]
FROM tblProducts"

WHERE tblProducts.[Select] = Yes

and I get "Variable not defined" on the Where line

The query design and copy sql gives

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item Weight] )
SELECT tblProducts.ProductId, tblProducts.ProductName,
tblProducts.[Item Weight] FROM tblProducts
WHERE (((tblProducts.[Select])=Yes));
Click to expand...
Click to expand...
Click to expand...
 
glad it worked for you - it is great to learn, and i do a lot more learning
here than i do teaching. :)

to answer your question:
yes, a space and then an underscore _
the code in my previous post illustrated breaking a single string into
multiple strings placed on separate lines (for readability only) - using the
ampersand to concatenate them "back together into one string" during code
execution.
to continue a *function* onto the next line, see PC Datasheet's posted
example in this thread. that example could also work as follows:

MsgBox "This is an example of continuing code on the next line",, _
"Continuation Example"

the "break" should work fine as long as you precede the underscore with a
space.


Mike Wilson said:
Tina

thank you - I've been a bit stupid trying to break the code onto
different lines.
Your last try worked a treat and I've used it to replace several
queries in my database.

Is the _ meant to be used as a continuation of the code on a
separate line ?

Thank you again, and to everyone else who takes the time to support
the group.

Isn't it great to learn ?

Mike




if the SQL in your last post works in the append query, it should work when
run from VBA, as well. i'm not sure if you posted your VBA code
character-by-character, but here's how i would clean up the posted SQL
statement for use in VBA:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=Yes", _
False

if that doesn't work, one last try:

DoCmd.RunSQL "INSERT INTO tblOrderD1 ( ProductId, " _
& "ProductName, [Item Weight] ) SELECT ProductId, ProductName, " _
& "[Item Weight] FROM tblProducts WHERE tblProducts.[Select]=True",
_
False

omit the final , False from the function if you're running this code in
a transaction.

hth


Mike Wilson said:
Howard, thank you for your help, I am still having trouble
with this to the extent that I can't get any further than a
debug advising incorrect Insert into or Select Statement
I've tried amending to :


Docmd.RunSQL "INSERT INTO tblOrderD1
( ProductId, ProductName, [Item weight])"
Docmd.RunSQL "SELECT tblProducts.ProductId,
tblProducts.ProductName, tblProducts.[Item Weight]]
FROM tblProducts"

WHERE tblProducts.[Select] = Yes

and I get "Variable not defined" on the Where line

The query design and copy sql gives

INSERT INTO tblOrderD1 ( ProductId, ProductName, [Item Weight] )
SELECT tblProducts.ProductId, tblProducts.ProductName,
tblProducts.[Item Weight] FROM tblProducts
WHERE (((tblProducts.[Select])=Yes));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top