Help With INSERT INTO WHERE Clause

[CharlesWhiteman]

I am tring to run some code in Vb and ask my Db to Insert all rows from my TblQuoteItems and INSERT them into another table, TblOrderedItems WHERE the QuoteID in TblQuoteItems matches the QuoteReference in my FrmDoOrder

Can anybody help? This is the code ehich doesnt work yet...

INSERT INTO "TblOrderedItems"
From TblQuoteItems
WHERE (((TblQuoteItems.QuoteReference)=Me.txtQuoteReference));

 

[OlcayM]

Hi,

May be you should try it first in query view, then copy the sql string of that query to vba...

HTH
OlcayM

 

[ajetrumpet]

Insert all rows from my TblQuoteItems and INSERT them into another table, TblOrderedItems WHERE the QuoteID in TblQuoteItems matches the QuoteReference in my FrmDoOrder

INSERT INTO "TblOrderedItems"
From TblQuoteItems
WHERE (((TblQuoteItems.QuoteReference)=Me.txtQuoteReference));

Charles, the correct syntax for the statement is this...

INSERT INTO [fieldInTargetTable], [nextfield], [etc, etc...]

SELECT [fieldstoappend]

FROM TblQuoteItems

WHERE TblQuoteItems.QuoteID = Forms!YourActiveForm!txtQuoteReference

Do not use the period as a reference character. Use Bang(!)

Also, before you type the statement into Visual Basic, you might want type DoCmd.RunSQL before it.

 

[boblarson]

If using it in code you would use (get rid of the single quotes I provided if your QuoteID is a number and not text, but if it is text keep them):

Dim strSQL As String

strSQL = "INSERT INTO TblOrderedItems " & _
            "From TblQuoteItems " & _
            "WHERE (((TblQuoteItems.QuoteReference)='" & Me.txtQuoteReference & "'))"

CurrentDb.Execute strSQL, dbFailOnError

 

[CharlesWhiteman]

Thanks Bob, I've approached it from a different way now and use an append query instead; although i will steamline it in Vb at a later stage. The trouble I am having now is that the fields successfully append but i also need to take the value of a txt box in a form and append that too.

So each line would be:

QuoteReference Product Price QYT Description CostPrice

The Quote refernece exisits in my form and not the table from where the line items are pulled from and I can't seem to get it to work!