Allen Brown copy record from form and subform


D

DMWM

Hi,

I can't seem to get this to work i keep getting an error on the DBEngine
line of code. Can anyone help please?

'Duplicate the related records: append query.

strSql = "INSERT INTO [tblProducts_Quoted] (Product_ID,
Quantity, Actual_Price, Item_Specifics) " & _
"SELECT Product_ID, Quantity, Actual_Price,
Item_Specifics " & _
"FROM [tblProducts_Quoted] WHERE Products_Quoted_ID = "
& Me.Products_Quoted_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

I don't fully understand all of this code to be able to work it out myself.
Any help is greatly appreciated.

DMWM
 
Ad

Advertisements

S

Steve Schapel

DMWM,

Is this a copy/paste of your exact code you are using?
What precisely is the error message you receive?
 
D

DMWM

Hi Steve,

It is an exact copy of the code i am trying to use which i got from the
allen browne website.

I get an error saying run-time error '3075'

Its something to do with the Products_Quoted_ID. I have manipulated it a
little from the original code because before this error message i got an
error saying:

cannot find reference to field 'I' in the first line before the strgsql
(append query).

Thanks in advance

Steve Schapel said:
DMWM,

Is this a copy/paste of your exact code you are using?
What precisely is the error message you receive?

--
Steve Schapel, Microsoft Access MVP


DMWM said:
Hi,

I can't seem to get this to work i keep getting an error on the DBEngine
line of code. Can anyone help please?

'Duplicate the related records: append query.

strSql = "INSERT INTO [tblProducts_Quoted] (Product_ID,
Quantity, Actual_Price, Item_Specifics) " & _
"SELECT Product_ID, Quantity, Actual_Price,
Item_Specifics " & _
"FROM [tblProducts_Quoted] WHERE Products_Quoted_ID = "
& Me.Products_Quoted_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

I don't fully understand all of this code to be able to work it out
myself.
Any help is greatly appreciated.

DMWM
 
S

Steve Schapel

DMWM,

Run-time error '3075'? That's all it told you?? Well, that's not very
descriptive, is it?

Ok, so where is this code being called from. If it's an event on a form,
does that form have a control (textbox) named Products_Quoted_ID? And/or a
field, in the table/query that the form is based on, named
Products_Quoted_ID? And is this field a Text data type or a Number data
type?
 
A

Allen Browne

Can you post the exact error message?

Also, add this line just above the DBEngine line:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G), copy the string, and
paste it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


DMWM said:
Hi Steve,

It is an exact copy of the code i am trying to use which i got from the
allen browne website.

I get an error saying run-time error '3075'

Its something to do with the Products_Quoted_ID. I have manipulated it a
little from the original code because before this error message i got an
error saying:

cannot find reference to field 'I' in the first line before the strgsql
(append query).

Thanks in advance

Steve Schapel said:
DMWM,

Is this a copy/paste of your exact code you are using?
What precisely is the error message you receive?

--
Steve Schapel, Microsoft Access MVP


DMWM said:
Hi,

I can't seem to get this to work i keep getting an error on the
DBEngine
line of code. Can anyone help please?

'Duplicate the related records: append query.

strSql = "INSERT INTO [tblProducts_Quoted] (Product_ID,
Quantity, Actual_Price, Item_Specifics) " & _
"SELECT Product_ID, Quantity, Actual_Price,
Item_Specifics " & _
"FROM [tblProducts_Quoted] WHERE Products_Quoted_ID
= "
& Me.Products_Quoted_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

I don't fully understand all of this code to be able to work it out
myself.
Any help is greatly appreciated.

DMWM
 
D

DMWM

Hi I've run the code again which is the code for OnClick event of a button on
my form. I get the following error message:

Run-time error 3075: Syntax error (missing operator) in query expression
'Products_quoted_ID = '.

And this is what is written in the immediate window

INSERT INTO [tblProducts_Quoted] (Product_ID, Quantity, Actual_Price,
Item_Specifics) SELECT Product_ID, Quantity, Actual_Price, Item_Specifics
FROM [tblProducts_Quoted] WHERE Products_Quoted_ID = ;

Looking at it there is no criteria set for the Products_Quoted_ID. How do i
write it in to use the one that is currently displayed on the form as surely
this would solve the problem??

Thanks

Allen Browne said:
Can you post the exact error message?

Also, add this line just above the DBEngine line:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G), copy the string, and
paste it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


DMWM said:
Hi Steve,

It is an exact copy of the code i am trying to use which i got from the
allen browne website.

I get an error saying run-time error '3075'

Its something to do with the Products_Quoted_ID. I have manipulated it a
little from the original code because before this error message i got an
error saying:

cannot find reference to field 'I' in the first line before the strgsql
(append query).

Thanks in advance

Steve Schapel said:
DMWM,

Is this a copy/paste of your exact code you are using?
What precisely is the error message you receive?

--
Steve Schapel, Microsoft Access MVP


Hi,

I can't seem to get this to work i keep getting an error on the
DBEngine
line of code. Can anyone help please?

'Duplicate the related records: append query.

strSql = "INSERT INTO [tblProducts_Quoted] (Product_ID,
Quantity, Actual_Price, Item_Specifics) " & _
"SELECT Product_ID, Quantity, Actual_Price,
Item_Specifics " & _
"FROM [tblProducts_Quoted] WHERE Products_Quoted_ID
= "
& Me.Products_Quoted_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

I don't fully understand all of this code to be able to work it out
myself.
Any help is greatly appreciated.

DMWM
 
Ad

Advertisements

S

Steve Schapel

DMWM,

I think this would indicate that there is a control on your form named
Products_Quoted_ID with no data in it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top