Need to create a VBA to run a query in MS Access?
If so, I’ll show you the steps to add a VBA to run a query when clicking a button.
In general, here is the VBA syntax 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 the “max_sales” query:
Step 1: Add MS Access Form
First, open MS Access, and then add an Access Form. You can add an Access Form by going to the CREATE tab, and then pressing on the Form Design icon located on the MS Access Ribbon:
Step 2: Place a Button
Place a button on the Form itself. You can do that by clicking on the ‘button icon’ on the Access Ribbon. Then, place that button on the Form itself:
After you placed the button on the Form, 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.
Step 3: Select the Event Procedure
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:
Step 4: Open the VBA Screen
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:
Step 5: Write the VBA to Run the Query
Write the VBA syntax to run your query upon a click (here, the query’s name is “max_sales”). The syntax should be as follows:
Private Sub Command0_Click() DoCmd.OpenQuery "max_sales" End Sub
This is how the VBA would look like:
Step 6: View the Results
Once you’re done writing the VBA syntax, save your work. Then, go back to your Access Form and switch to the Form View.
To switch to the Form View, press on the View icon under the DESIGN tab. Then, select Form View from the drop-down list:
Finally, press on the button that is located on your Access Form to run the “max_sales” query upon a click:
You can now run your query at any given time by simply pressing on the button.