?
Solved

DOcmd in Access

Posted on 2010-08-26
26
Medium Priority
?
564 Views
Last Modified: 2013-11-27
I have the following DOcmd

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

Where does this get executed while in access 2010 and how can I assign to a button on a form?
0
Comment
Question by:Matt Pinkston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
26 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33530704
that is executed in VBA codes..
to execute in the click event of a button

private sub cmdButton_click()

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

end sub


change cmdButton with the actual name of the button

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33530717
correction in your sql code


private sub cmdButton_click()

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage Code]")

end sub

0
 

Author Comment

by:Matt Pinkston
ID: 33530769
THe button does nothing when I click it
testdb.zip
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33530947
test this

see the changes made in the vba codes
testdb.accdb
0
 

Author Comment

by:Matt Pinkston
ID: 33531062
Still does not seem to work?

I expect to see sales stage in opportunities_solutiontrack updated with current sales stage from Opportunities
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33531249
well, it does.. are you using the file that i uploaded?
0
 

Author Comment

by:Matt Pinkston
ID: 33531359
yes I saved it opened in and clicked the button
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33531396
did you saved the file in a trusted location?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 33532055
Try saving the SQL statement to a strSQL variable, which you can then display in the Immediate Window using a Debug.Print statement.  This helps in debugging.  Also, you can (if necessary) copy the SQL Statement from the Immediate window to a new query, for further debugging.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33532118
the SQL statement that i used was from a saved query Query1 so it is correct,so no need  to debug the SQL statement.


pinkstonmp,

do you know about trusted location?

from the Ribbon

File > Access Option> trust Center
 Click on on Trust Center Setting > Trust Locations

0
 

Author Comment

by:Matt Pinkston
ID: 33536603
I got your code to wok by clicking the option to authorize however I imported the query1 and opportunity feeder into my production db and then updated query1 in design view just changing Current Sales Stage to Current Sales Stage Code and when I click the button I get the following in a pop-up box

Enter Paramater Value
Opportunities.Current Sales Stage

Even when the query1 does not have that in it?

How do I fix this? Sorry for being a newbie...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33537072
when the name of table or field have spaces, you have to enclosed them in square brackets, like this


          Opportunities.[Current Sales Stage]
0
 

Author Comment

by:Matt Pinkston
ID: 33537478
here is exactly what I have in the query

UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID]=Opportunities.[Opportunity  Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities].[Current Sales Stage Code];

yet when I run it byclicking the button it says

Enter Paramater Value
Opportunities.Current Sales Stage

Notice it is picking up the old name????????
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33537518
in the db you uploaded, you don't have any field named [Current Sales Stage Code]
what you got is [Current Sales Stage]


upload a copy of the db with the table that have the field [Current Sales Stage Code]
0
 

Author Comment

by:Matt Pinkston
ID: 33537562
My actual DB (PROD) is not a good example because it links to a sharepoint but the correct name is Current Sales Stage Code
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33537657

Notice it is picking up the old name?

then perhaps you are running the old query..

delete that query, then do a compact
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33537675
delete that query, then do a compact a compact and repair of the db.

create a new query, do not select any table and just go to the SQL VIEW of the new query,

copy this and paste to the SQL view window of the new query

UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID]=Opportunities.[Opportunity  Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities].[Current Sales Stage Code];

save the query and test
0
 

Author Comment

by:Matt Pinkston
ID: 33537703
How is query1 connected to the form Opportunity Feeder?

When I do a design view on that form and click on event details I see code that is old and does not match the query
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33537752
How is query1 connected to the form Opportunity Feeder?

query1 is not connected to the form Opportunity Feeder, on the db i uploaded.
0
 

Author Comment

by:Matt Pinkston
ID: 33537769
same result
0
 

Author Comment

by:Matt Pinkston
ID: 33537782
So okay as a newbie I need to know hos this darn thing works

I have two tables
Opportunities
Opportunities_SolutionTrack

Query1 (Has a query in it to do the work)
 
Opportunity Feeder (Form with a button not sure how its connected to Query 1)
0
 

Author Comment

by:Matt Pinkston
ID: 33537819
Okay so I decided to bring up Query1 in design view and from here I did a run in access.
 
First prompt comes back with
You won't be able to undo the changes this action query is about to make to the data in a linked table or tables Do you want to run this action "YES"

THen I get an error
You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 33537951
did you test the db i uploaded?  at http:#a33530947
0
 

Author Comment

by:Matt Pinkston
ID: 33540569
found the error to be strange...
My sharepoint list that is linked to has current sales stage as a miltiple choice variable and one of the variable being loaded did not match the choice.
0
 

Author Comment

by:Matt Pinkston
ID: 33570914
now the error is back and there is not data integrity issues that I can find the fields are all the same.  Fixe fields in question 4 text and one currency.
0
 

Author Comment

by:Matt Pinkston
ID: 33571592
tried to limit to one cmd and still get the error HELP!!!!

UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON [Opportunities_SolutionTrack].[Opportunity ID]=[Opportunities].[Opportunity Identifier] SET Opportunities_SolutionTrack.TCV = [Opportunities].[Total Opportunity Value USD];
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

801 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