• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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?

Thanks,
Nic
0
NicolaTMNS
Asked:
NicolaTMNS
  • 3
  • 3
1 Solution
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Nicola, could you post the code that's executing the query (and the query's sql), please?
0
 
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.

Thanks,
Nic


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_cmdRunAdjustments_Click:
    Exit Sub

Err_cmdRunAdjustments_Click:
    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));
0
 
Paul Cook-GilesAccess/VBA/SQL Database 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.

Nic
0
 
NicolaTMNSAuthor Commented:
Job done. Happy.
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Excellent!  :)
0
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now