insert into troubles

Posted on 2013-02-01
Medium Priority
Last Modified: 2013-02-01
I can't seem to get the syntax correct for this query in vba.
Any ideas?

Dim strsql As String
      strsql = "INSERT INTO tbltrad_commandes_FPdetails" & _
      "(trad_commandesID,ContractQuantity,ContractPrice,NegociatedRate,tmpSelect) " & _
      "(SELECT " & Forms![frmtrad_commandes2]![trad_commandesID] & ",ContractQuantity,ContractPrice,NegociatedRate,-1 " & _
"FROM tblTranslationContractPricing WHERE ContractQuantity = " & arr(i) & " AND Active = True)"
Question by:Shawn
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38845097
what is the error?

what Data Type is field ContractQuantity ? , Active ?
LVL 50

Accepted Solution

Dale Fye earned 2000 total points
ID: 38845102
strsql = "INSERT INTO tbltrad_commandes_FPdetails" _
          & "(trad_commandesID,ContractQuantity,ContractPrice,NegociatedRate,tmpSelect) " _
          & "SELECT " & Forms![frmtrad_commandes2]![trad_commandesID] _
                               & ",ContractQuantity,ContractPrice,NegociatedRate,-1 " _
          & "FROM tblTranslationContractPricing " _
          & "WHERE ContractQuantity = " & arr(i) & " AND Active = -1"

I removed a set of () around the SELECT statement, and did some reformatting (the way I prefer), but I think your main problem was the ( ) around the SELECT statement.

Author Closing Comment

ID: 38845114
worked perfectly. thanks for the extra format help.

the only thing I changed was from Active = -1 to Active = True.
For some reason the -1 did not convert well.

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question