Solved

DOcmd in Access

Posted on 2010-08-26
26
552 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
  • 13
  • 12
26 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now