Access: Queries not running in code

Hi guys,

I'm running into a problem here.

Upgraded an access db from 2007 to 2013 and some queries won't run in code but will when I debug it.

Any suggestions?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Nicola, could you post the code that's executing the query (and the query's sql), please?
NicolaTMNSAuthor Commented:
Hi Paul,

Thank you for responding.

I have just taken the code to run one of the update queries I need run and placed it into a command button of its own to isolate it and see if I can get the query the run but I can't.  When I break point on the docmd line the changes I want happen in the table alright. When I run the query directly the changes happen but when I run the program and click on the button or have this line of code in a function call, the update query doesn't perform.


Simple code behind button that won't work but will work when breakpoint is put in:

Private Sub cmdRunAdjustments_Click()
On Error GoTo Err_cmdRunAdjustments_Click

DoCmd.OpenQuery "EXTRAS_UpdExtrasCatsValues_Yr1s"

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdRunAdjustments_Click
End Sub

Query's SQL is as follows:

UPDATE (Extras_CatValues_Yr1s INNER JOIN Extras_CatValues_Yr2s ON Extras_CatValues_Yr1s.ID = Extras_CatValues_Yr2s.ID) INNER JOIN ExtrasCatsValues ON (Extras_CatValues_Yr1s.YrID = ExtrasCatsValues.YrID) AND (Extras_CatValues_Yr1s.ID = ExtrasCatsValues.ID) SET ExtrasCatsValues.[Value] = [ExtrasCatsValues]![Value_Real]-(([Extras_CatValues_Yr1s]![Value_Real]-[Extras_CatValues_Yr2s]![Value_Real])/12*(SetCurrentMonthNo()-1));
Paul Cook-GilesSenior Application DeveloperCommented:
That's very interesting.  If you run the query from the Navigation Pane, do you get any popup confirmation messages?  
If you breakpoint on the DoCmd line, do you get  any popup confirmation messages after you hit F5 or F8?
Do you have Warnings turned off?
Are you familiar with  DoCmd.RunSQL? If so, try using that command instead of calling the query by name, and let's see what happens.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NicolaTMNSAuthor Commented:
Thanks again Paul and my apologies for the delay due to time difference between countries.

No pop up confirmation in Navigation Pane.

Docmd.RunSQL does indeed work!

Big Smile here and thank you.

NicolaTMNSAuthor Commented:
Job done. Happy.
Paul Cook-GilesSenior Application DeveloperCommented:
Excellent!  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.