98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Running an Update Query via Access VBA
Running an Update Query via Access VBA
Resolved · High Priority · Version 2016
Tue 7 Aug 2018, 08:15Delegate Manny said...
Manny has attended:
Access Intermediate course
Access Advanced course
Running an Update Query via Access VBA
Hello All,
I have 2 questions. I have a update query that updates my main table from a temporary table. This works fine when i run this via clicking the button in access objects on the left hand side.
My issue is 2 fold.
1) i have attempted to run this via vba using the below code but i am having no luck in executing this. Please could you check if i am running this correctly.
2) after this update query is run, i wish the subform and the new form to re run its calculations using the "refresh" function and it seems to not take this into account.
Dim rst As DAO.Recordset
Dim qdef As DAO.QueryDef
Dim dbs As DAO.Database
Dim frm As Form_PriceByFundF
Dim subfrm As Form_PriceByFundSubFormF
Set frm = Forms!PriceByFundF
Set subfrm = Forms!PriceByFundF.PricingF.Form
Set dbs = CurrentDb
dbs.Execute "Update24AMPriceFromChallengeQ"
frm.Refresh
frm.Repaint
subfrm.Refresh
subfrm.Repaint
Thanks, MANNY
Tue 7 Aug 2018, 13:10Trainer Jens said...
RE: Running an Update Query via Access VBA
Hi Manny,
Thank you for the forum question.
I would not use DAO to do what you want. Use SQL. You can update and do the calculations in one line.
The line below is just an example. The line will add 10% to all price in the main table if the price is greater than 100.
DoCmd.RunSQL "Update "maintable" set price=price*1.1 Where price>100"
Your object variables are declared wrong in the memory.
Dim frm As Form_PriceByFundF
Dim subfrm As Form_PriceByFundSubFormF
It must be:
Dim frm As Form
Dim subfrm As SubForm
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 7 Aug 2018, 15:02Delegate Manny said...
RE: Running an Update Query via Access VBA
Ah i got it Jens...
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
DoCmd.OpenQuery "Update24AMPriceFromChallengeQ"
It only will run the updated information ocne the form has closed :)
last question, how do i refresh the data in the subform to bring in the new data (whilst the form is open). I tried the below but this does not seem to work either
Dim frm As Form
Dim subfrm As subform
Set frm = Forms!PriceByFundF
Set subfrm = Forms!PriceByFundF.PricingF.Form
frm.Refresh
frm.Repaint
subfrm.Refresh
subfrm.Repaint
Tue 7 Aug 2018, 19:03Trainer Jens said...
RE: Running an Update Query via Access VBA
Hi Manny,
Try to replace your code (below)
Dim frm As Form
Dim subfrm As subform
Set frm = Forms!PriceByFundF
Set subfrm = Forms!PriceByFundF.PricingF.Form
frm.Refresh
frm.Repaint
subfrm.Refresh
subfrm.Repaint
With:
Forms!PriceByFundF![PricingF].Requery
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 8 Aug 2018, 08:07Delegate Manny said...
RE: Running an Update Query via Access VBA
HI Jens,
I got there in the end! TaDa. Thanks for your help :)
If [Comments].Value = "" Then
MsgBox ("Please Enter Relevant Comments for Challenged Price")
Exit Sub
Else
End If
If MsgBox("Please Confirm You Wish To Commit your Challenge", vbYesNo, "Confirmation Required") = vbYes Then
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
DoCmd.OpenQuery "Update24AMPriceFromChallengeQ"
Dim frm As Form_PriceByFundF
Set frm = Forms!PriceByFundF
frm.Refresh
Else: Exit Sub
End If
Wed 8 Aug 2018, 09:21Trainer Jens said...
RE: Running an Update Query via Access VBA
Well done Manny,
Access VBA can be a challenge.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 14 Nov 2019, 06:16edwards142 (guest) said...
Running an Update Query via Access VBA
Hey guys,
Use the VBA DAO Database Execute command, rather than DoCmd.RunSQL and DoCmd.OpenQuery, to run action queries without warning messages and avoid changing the Access SetWarnings status.
I can share you a post which contain complete details regarding how to run vba code in Access under various circumstances.
http://www.accessrepairnrecovery.com/blog/run-vba-code-in-access
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Related tablesWhen you have related tables such as Customers and their Orders, the Customer table is the Primary table. |
