Often times you’ll come across situations where you need to create a VBA to run a query in MS Access. For example, you may have a button on an Access Form that can be used to run an existing query, once you press on that button.
To accomplish the above task, you can write a simple VBA to run your desired query in MS Access.
Here is the VBA syntax structure that you can use to run a query in MS Access:
DoCmd.OpenQuery "Query Name"
Please note that the “Query Name” should NOT be inserted within brackets “”
In the next section, I’m going to show you the steps that you can use in order to create a VBA to run a query using a click of a button.
Steps to create a VBA to run a query in MS Access
Let’s suppose that you already have an Access database, and that the Access database contains a query called “max_sales”
Here are the steps that you’ll need to follow in order to create a VBA to run your “max_sales” query:
(1) First, open MS Access, and then add an Access Form. You can add an Access From by going to the CREATE tab, and then pressing on the Form Design icon located on the MS Access Ribbon at the top
(2) Place a button on the Form itself (you can do that by clicking on the ‘button icon’ under the Controls section which is located on the Access Ribbon. Then, place that button on the Form itself).
Once you place the button on the Form itself, you’ll notice that the Command Button Wizard would appear on your screen. Press Cancel as the Command Button Wizard will not be needed for our purposes.
(3) If the Property Sheet is not already populated at the right-hand-side of your screen, then press F4 to get that screen (i.e., press F4 while the button is selected).
On the Property Sheet select the ‘[Event Procedure]’ associated with the ‘On Click’ option
(4) To open the screen where you can write your VBA for your button, press on the 3 dots ‘…’ corresponding to the ‘On Click’ option under the Property Sheet
(5) Write the VBA syntax to run your query (here it is the “max_sales” query) upon a click. The syntax should look as follows:
Private Sub Command0_Click() DoCmd.OpenQuery "max_sales" End Sub
We are almost done… To view your results, you’ll need to follow the final 2 steps below.
View your results
(1) Once you are done writing the VBA syntax, save your work. Then, go back to your Access From and switch to the Form View.
To switch to the Form View, press on the View icon located on the Access Ribbon under the DESIGN tab. Then, select Form View from the drop-down list
(2) Finally, press on the button that is located on your Access Form to run the “max_sales” query upon a click!
Mission Accomplished! You can now run your desired query at any given time by simply pressing on the button you just created within the MS Access Form.