Automating an Update Query Using DoCmd.RunSQL

This tip shows us how to create and execute an Update Query using the VBA DoCmd.RunSQL Statement.  We shall use the example of a Products table, and our objective is to update the CostPerUnit field of a particular record matching a set criteria.  To do this the user will open an unbound form to select a product from a combo box, and enter a new updated price in the textbox.  

Above: Unbound Form to select a product from a Combo Box list, and enter its new price.

The code which creates and executes the SQL Statement will run when the Update command button is clicked.  Information entered by the user is referenced by the code (as Me!ctlProduct and Me!txtNewCost) and integrated into the SQL statement.

Above:  This code runs when the users click the Update command button.

The varSQL variable contains the SQL from the Update Query which we are going to run.  This can be built using the Access Query Design Grid, and switching to SQL view to copy the statement.  The two form controls with our user input are built into this variable, and become part of the Statement. The next line uses the DoCmd.RunSQL statement to the run the SQL contained in our string variable.  This will then act like a standard Update Query: the relevant product is found, and the CostPerUnit field is updated to the new price specified by the user.