?
Solved

update SQL Query in VBA Using Variables

Posted on 2007-11-27
3
Medium Priority
?
811 Views
Last Modified: 2012-05-05
I am trying to use an update query but cannot get the syntax correct.  Please advise.  This is obviously my hardest thing to grasp (SQL Query syntax with variables).

For Each varItm In Me.lstVendorPartsList.ItemsSelected
        varPartID = Me.lstVendorPartsList.ItemData(varItm)

            DoCmd.SetWarnings False
            Dim sqlStr As String
            sqlStr = "UPDATE " & varTmpTableName1 & " SET " & varTmpTableName1 & ".[qtytopurchase] = " & varUpdateQty _
                        & " WHERE (((" & varTmpTableName1 & ".PartID)= " & varPartID

                       
                 'MsgBox sqlStr
                DoCmd.RunSQL sqlStr
            DoCmd.SetWarnings True
    Next
0
Comment
Question by:Lasers07
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1400 total points
ID: 20360212
try

sqlStr = "UPDATE [" & varTmpTableName1 & "] SET [qtytopurchase] = " & varUpdateQty _
                        & " WHERE PartID= " & varPartID
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 20360218
What are data types of varUpdateQTY, carPartID?
You need not be so explicit in naming the table every time you mention a field in the table.
sqlStr = "UPDATE " & varTmpTableName1 & " SET  [qtytopurchase] = " & varUpdateQty _
                        & " WHERE  PartID)= " & varPartID
0
 

Author Closing Comment

by:Lasers07
ID: 31427377
Thanks Cap!  I think u are on a roll getting points off my syntax questions!  LOL.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

839 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