Insert Into - Invalid Operation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

Could you tell me where is the mistake? I got an error "Invalid Operation"
when I try to run it.

[Forms]![frmSupplierInvoice]![txtIDInvoice] is a string
[Forms]![frmSupplierInvoice]![PurchaseOrderID] is a Integer
[Forms]![frmSupplierInvoice]![SupplierID] is a Integer

I think that the mistake is on "As Expr1"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID, IDMoneda,
dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT
tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda, tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered," & Chr$(34) &
[Forms]![frmSupplierInvoice]![txtIDInvoice] & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" &
[Forms]![frmSupplierInvoice]![PurchaseOrderID] & ") AND
((tblPurchaseOrderTransaction.SupplierID)=" &
[Forms]![frmSupplierInvoice]![SupplierID] & "))"

Thanks in advance
MS
 
Martin said:
Could you tell me where is the mistake? I got an error "Invalid Operation"
when I try to run it.

[Forms]![frmSupplierInvoice]![txtIDInvoice] is a string
[Forms]![frmSupplierInvoice]![PurchaseOrderID] is a Integer
[Forms]![frmSupplierInvoice]![SupplierID] is a Integer

I think that the mistake is on "As Expr1"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID, IDMoneda,
dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT
tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda, tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered," & Chr$(34) &
[Forms]![frmSupplierInvoice]![txtIDInvoice] & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" &
[Forms]![frmSupplierInvoice]![PurchaseOrderID] & ") AND
((tblPurchaseOrderTransaction.SupplierID)=" &
[Forms]![frmSupplierInvoice]![SupplierID] & "))"
Click to expand...


Nothing wrong in there jumps out at me. The only question I
would have is to double check that the txtIDInvoice string
does not contain a quote character.

A useful debugging technique for this kind of thing is to
add a Debug.Print strSQL2 so you can see the result of all
the concatenations. If that doesn't help clarify the
problem, then Copy/Paste the SQL statement to a nerw query
and try to run it to get more specific error messages.
 
Hello Marshall,

Thanks for your answer. Could you tell me how to use Debug.Pring strSQL2? I
added the line after the assigment strSQL = "..." and nothing happen.

Best Regards
Martin

Marshall Barton said:
Martin said:
Could you tell me where is the mistake? I got an error "Invalid Operation"
when I try to run it.

[Forms]![frmSupplierInvoice]![txtIDInvoice] is a string
[Forms]![frmSupplierInvoice]![PurchaseOrderID] is a Integer
[Forms]![frmSupplierInvoice]![SupplierID] is a Integer

I think that the mistake is on "As Expr1"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID, IDMoneda,
dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT
tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda, tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered," & Chr$(34) &
[Forms]![frmSupplierInvoice]![txtIDInvoice] & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" &
[Forms]![frmSupplierInvoice]![PurchaseOrderID] & ") AND
((tblPurchaseOrderTransaction.SupplierID)=" &
[Forms]![frmSupplierInvoice]![SupplierID] & "))"
Click to expand...


Nothing wrong in there jumps out at me. The only question I
would have is to double check that the txtIDInvoice string
does not contain a quote character.

A useful debugging technique for this kind of thing is to
add a Debug.Print strSQL2 so you can see the result of all
the concatenations. If that doesn't help clarify the
problem, then Copy/Paste the SQL statement to a nerw query
and try to run it to get more specific error messages.
Click to expand...
 
Hi Chris,

Thanks for your answer. I forget to mention that I wrote all the sentence in
one line.

Best Regards
MS

Chris2 said:
Martin said:
Hello all,

Could you tell me where is the mistake? I got an error "Invalid Operation"
when I try to run it.

[Forms]![frmSupplierInvoice]![txtIDInvoice] is a string
[Forms]![frmSupplierInvoice]![PurchaseOrderID] is a Integer
[Forms]![frmSupplierInvoice]![SupplierID] is a Integer

I think that the mistake is on "As Expr1"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID, IDMoneda,
dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT
tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda, tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered," & Chr$(34) &
[Forms]![frmSupplierInvoice]![txtIDInvoice] & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" &
[Forms]![frmSupplierInvoice]![PurchaseOrderID] & ") AND
((tblPurchaseOrderTransaction.SupplierID)=" &
[Forms]![frmSupplierInvoice]![SupplierID] & "))"

Thanks in advance
MS
Click to expand...

Martin,

The above VBA example is missing some double-quotes and
line-continuations. Each line must be its own double-quote
delimited string with continuation character.

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction (
PurchaseOrderID, " & _
"ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, " & _
"dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT "

etc.

Remember to make sure there are spaces so that characters don't get
concatenated against one another.

Example:

The following produces an SQL string that can be saved in a query
(so at least the syntax of the assembled string is correct).

Note: I had to change the control names to variable names in this
example because I don't have your forms, but the control names
should work fine for you.

I apologize for any line-breaks that you must undo in order to make
this work (if you decide to run it).

Public Sub StringAssembly()

Dim strSQL2 As String
Dim txtIDInvoice As String
Dim PurchaseOrderID As String
Dim SupplierID As String
txtIDInvoice = "111111"
PurchaseOrderID = "222222"
SupplierID = "333333"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction (
PurchaseOrderID, " & _
"ProductID, SupplierID, intCategoryID,
intSupplierProductID, IDMoneda, " & _
"dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT " & _
"tblPurchaseOrderTransaction.PurchaseOrderID, " & _
"tblPurchaseOrderTransaction.ProductID, " & _
"tblPurchaseOrderTransaction.SupplierID, " & _
"tblPurchaseOrderTransaction.intCategoryID, " & _
"tblPurchaseOrderTransaction.intSupplierProductID, " & _
"tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice, " & _
"tblPurchaseOrderTransaction.prcDiscount, " & _
"tblPurchaseOrderTransaction.intUnitsOrdered , " &
Chr$(34) & _
txtIDInvoice & Chr$(34) & " AS Expr1 FROM " & _
"tblPurchaseOrderTransaction WHERE " & _
"(((tblPurchaseOrderTransaction.PurchaseOrderID)=" & _
PurchaseOrderID & ") AND " & _
"((tblPurchaseOrderTransaction.SupplierID)=" & _
SupplierID & "))"

Debug.Print strSQL2

End Sub



Sicnerely,

Chris O.
Click to expand...
 
Martin said:
Hello all,

Could you tell me where is the mistake? I got an error "Invalid Operation"
when I try to run it.

[Forms]![frmSupplierInvoice]![txtIDInvoice] is a string
[Forms]![frmSupplierInvoice]![PurchaseOrderID] is a Integer
[Forms]![frmSupplierInvoice]![SupplierID] is a Integer

I think that the mistake is on "As Expr1"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID, IDMoneda,
dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT
tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda, tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered," & Chr$(34) &
[Forms]![frmSupplierInvoice]![txtIDInvoice] & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" &
[Forms]![frmSupplierInvoice]![PurchaseOrderID] & ") AND
((tblPurchaseOrderTransaction.SupplierID)=" &
[Forms]![frmSupplierInvoice]![SupplierID] & "))"

Thanks in advance
MS
Click to expand...

Martin,

The above VBA example is missing some double-quotes and
line-continuations. Each line must be its own double-quote
delimited string with continuation character.

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction (
PurchaseOrderID, " & _
"ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, " & _
"dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT "

etc.

Remember to make sure there are spaces so that characters don't get
concatenated against one another.

Example:

The following produces an SQL string that can be saved in a query
(so at least the syntax of the assembled string is correct).

Note: I had to change the control names to variable names in this
example because I don't have your forms, but the control names
should work fine for you.

I apologize for any line-breaks that you must undo in order to make
this work (if you decide to run it).

Public Sub StringAssembly()

Dim strSQL2 As String
Dim txtIDInvoice As String
Dim PurchaseOrderID As String
Dim SupplierID As String
txtIDInvoice = "111111"
PurchaseOrderID = "222222"
SupplierID = "333333"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction (
PurchaseOrderID, " & _
"ProductID, SupplierID, intCategoryID,
intSupplierProductID, IDMoneda, " & _
"dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT " & _
"tblPurchaseOrderTransaction.PurchaseOrderID, " & _
"tblPurchaseOrderTransaction.ProductID, " & _
"tblPurchaseOrderTransaction.SupplierID, " & _
"tblPurchaseOrderTransaction.intCategoryID, " & _
"tblPurchaseOrderTransaction.intSupplierProductID, " & _
"tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice, " & _
"tblPurchaseOrderTransaction.prcDiscount, " & _
"tblPurchaseOrderTransaction.intUnitsOrdered , " &
Chr$(34) & _
txtIDInvoice & Chr$(34) & " AS Expr1 FROM " & _
"tblPurchaseOrderTransaction WHERE " & _
"(((tblPurchaseOrderTransaction.PurchaseOrderID)=" & _
PurchaseOrderID & ") AND " & _
"((tblPurchaseOrderTransaction.SupplierID)=" & _
SupplierID & "))"

Debug.Print strSQL2

End Sub



Sicnerely,

Chris O.
 
Martin said:
Hi Chris,

Thanks for your answer. I forget to mention that I wrote all the sentence in
one line.
Click to expand...

Martin,

Wow! That must take quite a bit of horizontal scrolling to examine
in the VBE.


I copied and pasted your original example into my "StringAssembly"
sub.

I manually removed the line-breaks. The line was quite lengthy.

After doing this, I hit enter to see what would happen, and I
received the error: "Compile error: Expected: end of statement" It
had highlighted "ProductID" near the beginning of the string. There
was an extra double-quote right before ProductID. I removed it.

I then received another error. The text segment "As Expr1" was
highlighted.

The text segment (just before the error point noted above):

& Chr$(34) & " [Forms]![frmSupplierInvoice]![txtIDInvoice]

I *think* needs to be:

& Chr$(34) & [Forms]![frmSupplierInvoice]![txtIDInvoice]

Note the double-quote I removed.

It stopped giving me an error at this point.

I ran the sub (I manually changed the control names to variable name
again, for the reasons stated in my previous post), and I copied and
pasted the string from the immediate window (produced by the
debug.print strSQL2 line at the end of the sub) into a new query,
and it saved normally.


Example VBA Code (you must manually remove the line breaks and be
careful to preserve any necessary spacing between operators; this is
one good reason to use the format that appears with my previous
example. Copying and pasting this type of code is a huge pain in
the neck for others to deal with (for both of us, in this case).

Public Sub StringAssembly()

Dim strSQL2 As String
Dim txtIDInvoice As String
Dim PurchaseOrderID As String
Dim SupplierID As String
txtIDInvoice = "111111"
PurchaseOrderID = "222222"
SupplierID = "333333"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrd
erID, ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered , " & Chr$(34) &
txtIDInvoice & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" & PurchaseOrderID
& ") AND ((tblPurchaseOrderTransaction.SupplierID)=" & SupplierID &
"))"

Debug.Print strSQL2

End Sub


The following string resulted (this can be copied and pasted
directly into a query, and should save).

INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered , "111111" AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=222222) AND
((tblPurchaseOrderTransaction.SupplierID)=333333))


When you run the above VBA sub, you will be using your control names
instead of the variable names I am using (otherwise, your SQL string
will have three literals in it that probably won't mean a whole lot
when compared with your data).

Take the string that appears in the immediate window, copy and paste
it into a new query, and then try to run that query to see if there
are any additional problems.


Sincerely,

Chris O.
 
Debug.Print displays its arguments (in this case strSQL2) to
the Immediate/Debug window, which you can open by using the
View menu.

You should become familiar with this important debugging
tool even if Chris seems to have already done the work on
this query's problem.
--
Marsh
MVP [MS Access]

Thanks for your answer. Could you tell me how to use Debug.Pring strSQL2? I
added the line after the assigment strSQL = "..." and nothing happen.


Marshall Barton said:
Martin said:
Could you tell me where is the mistake? I got an error "Invalid Operation"
when I try to run it.

[Forms]![frmSupplierInvoice]![txtIDInvoice] is a string
[Forms]![frmSupplierInvoice]![PurchaseOrderID] is a Integer
[Forms]![frmSupplierInvoice]![SupplierID] is a Integer

I think that the mistake is on "As Expr1"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID, IDMoneda,
dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice ) SELECT
tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda, tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered," & Chr$(34) &
[Forms]![frmSupplierInvoice]![txtIDInvoice] & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" &
[Forms]![frmSupplierInvoice]![PurchaseOrderID] & ") AND
((tblPurchaseOrderTransaction.SupplierID)=" &
[Forms]![frmSupplierInvoice]![SupplierID] & "))"
Click to expand...


Nothing wrong in there jumps out at me. The only question I
would have is to double check that the txtIDInvoice string
does not contain a quote character.

A useful debugging technique for this kind of thing is to
add a Debug.Print strSQL2 so you can see the result of all
the concatenations. If that doesn't help clarify the
problem, then Copy/Paste the SQL statement to a nerw query
and try to run it to get more specific error messages.
Click to expand...
Click to expand...
 
Chris and Marshall,

thanks a lot for your help. I learnt how to use the debug.print and I
realized that the string is totally right (I copy and paste from the window
and I executed in the query windows and it worked) but I still have the error.

So, the next step is to go to the following line.

Set SourceSQL2 = db.OpenRecordset(strSQL2)

As the string is right, do you think that I have some mistake here? Should I
use another method?

Thanks again
Martin (happy holidays)



Chris2 said:
Martin said:
Hi Chris,

Thanks for your answer. I forget to mention that I wrote all the sentence in
one line.
Click to expand...

Martin,

Wow! That must take quite a bit of horizontal scrolling to examine
in the VBE.


I copied and pasted your original example into my "StringAssembly"
sub.

I manually removed the line-breaks. The line was quite lengthy.

After doing this, I hit enter to see what would happen, and I
received the error: "Compile error: Expected: end of statement" It
had highlighted "ProductID" near the beginning of the string. There
was an extra double-quote right before ProductID. I removed it.

I then received another error. The text segment "As Expr1" was
highlighted.

The text segment (just before the error point noted above):

& Chr$(34) & " [Forms]![frmSupplierInvoice]![txtIDInvoice]

I *think* needs to be:

& Chr$(34) & [Forms]![frmSupplierInvoice]![txtIDInvoice]

Note the double-quote I removed.

It stopped giving me an error at this point.

I ran the sub (I manually changed the control names to variable name
again, for the reasons stated in my previous post), and I copied and
pasted the string from the immediate window (produced by the
debug.print strSQL2 line at the end of the sub) into a new query,
and it saved normally.


Example VBA Code (you must manually remove the line breaks and be
careful to preserve any necessary spacing between operators; this is
one good reason to use the format that appears with my previous
example. Copying and pasting this type of code is a huge pain in
the neck for others to deal with (for both of us, in this case).

Public Sub StringAssembly()

Dim strSQL2 As String
Dim txtIDInvoice As String
Dim PurchaseOrderID As String
Dim SupplierID As String
txtIDInvoice = "111111"
PurchaseOrderID = "222222"
SupplierID = "333333"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrd
erID, ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered , " & Chr$(34) &
txtIDInvoice & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" & PurchaseOrderID
& ") AND ((tblPurchaseOrderTransaction.SupplierID)=" & SupplierID &
"))"

Debug.Print strSQL2

End Sub


The following string resulted (this can be copied and pasted
directly into a query, and should save).

INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered , "111111" AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=222222) AND
((tblPurchaseOrderTransaction.SupplierID)=333333))


When you run the above VBA sub, you will be using your control names
instead of the variable names I am using (otherwise, your SQL string
will have three literals in it that probably won't mean a whole lot
when compared with your data).

Take the string that appears in the immediate window, copy and paste
it into a new query, and then try to run that query to see if there
are any additional problems.


Sincerely,

Chris O.
Click to expand...
 
Hi Chris and Marshall,

I changed the method db.OpenRecordset by db.Execute and it worked !!!!.

Thanks for your help !!!!! and Happy holidays
Martin

Martin said:
Chris and Marshall,

thanks a lot for your help. I learnt how to use the debug.print and I
realized that the string is totally right (I copy and paste from the window
and I executed in the query windows and it worked) but I still have the error.

So, the next step is to go to the following line.

Set SourceSQL2 = db.OpenRecordset(strSQL2)

As the string is right, do you think that I have some mistake here? Should I
use another method?

Thanks again
Martin (happy holidays)



Chris2 said:
Martin said:
Hi Chris,

Thanks for your answer. I forget to mention that I wrote all the sentence in
one line.
Click to expand...

Martin,

Wow! That must take quite a bit of horizontal scrolling to examine
in the VBE.


I copied and pasted your original example into my "StringAssembly"
sub.

I manually removed the line-breaks. The line was quite lengthy.

After doing this, I hit enter to see what would happen, and I
received the error: "Compile error: Expected: end of statement" It
had highlighted "ProductID" near the beginning of the string. There
was an extra double-quote right before ProductID. I removed it.

I then received another error. The text segment "As Expr1" was
highlighted.

The text segment (just before the error point noted above):

& Chr$(34) & " [Forms]![frmSupplierInvoice]![txtIDInvoice]

I *think* needs to be:

& Chr$(34) & [Forms]![frmSupplierInvoice]![txtIDInvoice]

Note the double-quote I removed.

It stopped giving me an error at this point.

I ran the sub (I manually changed the control names to variable name
again, for the reasons stated in my previous post), and I copied and
pasted the string from the immediate window (produced by the
debug.print strSQL2 line at the end of the sub) into a new query,
and it saved normally.


Example VBA Code (you must manually remove the line breaks and be
careful to preserve any necessary spacing between operators; this is
one good reason to use the format that appears with my previous
example. Copying and pasting this type of code is a huge pain in
the neck for others to deal with (for both of us, in this case).

Public Sub StringAssembly()

Dim strSQL2 As String
Dim txtIDInvoice As String
Dim PurchaseOrderID As String
Dim SupplierID As String
txtIDInvoice = "111111"
PurchaseOrderID = "222222"
SupplierID = "333333"

strSQL2 = "INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrd
erID, ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered , " & Chr$(34) &
txtIDInvoice & Chr$(34) & " AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=" & PurchaseOrderID
& ") AND ((tblPurchaseOrderTransaction.SupplierID)=" & SupplierID &
"))"

Debug.Print strSQL2

End Sub


The following string resulted (this can be copied and pasted
directly into a query, and should save).

INSERT INTO tblSupplierInvoiceTransaction ( PurchaseOrderID,
ProductID, SupplierID, intCategoryID, intSupplierProductID,
IDMoneda, dblPrice, prcDiscount, intUnitsOrdered, txtIDInvoice )
SELECT tblPurchaseOrderTransaction.PurchaseOrderID,
tblPurchaseOrderTransaction.ProductID,
tblPurchaseOrderTransaction.SupplierID,
tblPurchaseOrderTransaction.intCategoryID,
tblPurchaseOrderTransaction.intSupplierProductID,
tblPurchaseOrderTransaction.IDMoneda,
tblPurchaseOrderTransaction.dblPrice,
tblPurchaseOrderTransaction.prcDiscount,
tblPurchaseOrderTransaction.intUnitsOrdered , "111111" AS Expr1 FROM
tblPurchaseOrderTransaction WHERE
(((tblPurchaseOrderTransaction.PurchaseOrderID)=222222) AND
((tblPurchaseOrderTransaction.SupplierID)=333333))


When you run the above VBA sub, you will be using your control names
instead of the variable names I am using (otherwise, your SQL string
will have three literals in it that probably won't mean a whole lot
when compared with your data).

Take the string that appears in the immediate window, copy and paste
it into a new query, and then try to run that query to see if there
are any additional problems.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
Back
Top