Convert SQL to VBA (1 Viewer)

Oreynolds

Member
Local time
Today, 10:40
Joined
Apr 11, 2020
Messages
110
Hi,

I have the following SQL append query which works fine:

Code:
INSERT INTO [Quote Details] ( ProductID, ProductCode, ProductSFACode, UnitPrice, Quantity, Markup, QuoteID )
SELECT QuotationTemplatesDetails.ProductID, QuotationTemplatesDetails.ProductCode, QuotationTemplatesDetails.ProductSFACode, QuotationTemplatesDetails.UnitPrice, QuotationTemplatesDetails.Quantity, QuotationTemplatesDetails.Markup, [Forms]![Quotes]![QuoteID] AS QuoteNumber
FROM QuotationTemplatesDetails
WHERE (((QuotationTemplatesDetails.QuoteID)=[Forms]![Quotes]![txtTemplateNumber]));

I need to convert this SQL to VBA and so far have the following:

Code:
CurrentDb.Execute "INSERT INTO [Quote Details] ( ProductID, ProductCode, ProductSFACode, UnitPrice, Quantity, Markup, QuoteID ) " & _
" SELECT QuotationTemplatesDetails.ProductID, QuotationTemplatesDetails.ProductCode, QuotationTemplatesDetails.ProductSFACode, QuotationTemplatesDetails.UnitPrice, QuotationTemplatesDetails.Quantity, QuotationTemplatesDetails.Markup, " & Me.QuoteID & " AS QuoteNumber " & _
" FROM QuotationTemplatesDetails " & _
" WHERE (((QuotationTemplatesDetails.QuoteID)=" & Me.txtTemplateNumber & "));"

After some debugging and trial and error it all works fine except the AS part: " & Me.QuoteID & " AS QuoteNumber " &

This is picking up the number in the QuoteID field on the loaded form and allocating it to the QuoteNumber Field for each record appended.

Can anyone see what I am doing wrong?

Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:40
Joined
Sep 21, 2011
Messages
7,897
Whenever I have problems like this I put it all into a string variable, then Debug.Print the variable.

That always shows the error.

This works though?

Code:
quoteID=1234
txtTemplateNumber=456
strtt="INSERT INTO [Quote Details] ( ProductID, ProductCode, ProductSFACode, UnitPrice, Quantity, Markup, QuoteID ) " & _
" SELECT QuotationTemplatesDetails.ProductID, QuotationTemplatesDetails.ProductCode, QuotationTemplatesDetails.ProductSFACode, QuotationTemplatesDetails.UnitPrice, QuotationTemplatesDetails.Quantity, QuotationTemplatesDetails.Markup, " & QuoteID & " AS QuoteNumber " & _
" FROM QuotationTemplatesDetails " & _
" WHERE QuotationTemplatesDetails.QuoteID=" & txtTemplateNumber 
debug.Print strtt
INSERT INTO [Quote Details] ( ProductID, ProductCode, ProductSFACode, UnitPrice, Quantity, Markup, QuoteID )  SELECT QuotationTemplatesDetails.ProductID, QuotationTemplatesDetails.ProductCode, QuotationTemplatesDetails.ProductSFACode, QuotationTemplatesDetails.UnitPrice, QuotationTemplatesDetails.Quantity, QuotationTemplatesDetails.Markup, 1234 AS QuoteNumber  FROM QuotationTemplatesDetails  WHERE QuotationTemplatesDetails.QuoteID=456
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:40
Joined
Jan 20, 2009
Messages
12,268
Often due to delimiters not being included. Access handles all that stuff automatically when you refer to a control but Execute is a method of the database engine so you have construct all the syntax yourself.
 

giddyhead

Registered User.
Local time
Today, 05:40
Joined
Jul 3, 2014
Messages
88
I found this SQL2VBA App a while back and have used it as well. What I have found out is after converting and applying you may have to modify it a little to suit you.
 

Attachments

  • SQL2VBA.accdb
    488 KB · Views: 25

Micron

AWF VIP
Local time
Today, 05:40
Joined
Oct 20, 2018
Messages
3,472
This is picking up the number in the QuoteID field on the loaded form and allocating it to the QuoteNumber Field for each record appended.
I take it that this is what it's doing but that is not what you want? Clarification might be in order; i.e. what you want vs what you got.

Your insert will use the QuoteID value from the form control for each record that satisfies the query criteria. If that value is 12345, then that is what you will insert to every record that applies. If you expect a different value for each query record, I would think that you have to navigate through form records after each insert so that the QuoteID relates to the current record.
 

isladogs

CID VIP
Local time
Today, 10:40
Joined
Jan 14, 2017
Messages
14,689

Users who are viewing this thread

Top Bottom