HOW TO UPDATE A RECORD IN A MICROSOFT ACCESS TABLE USING VBA / UPDATING A RECORD IN AN ACCESS TABLE BASED ON USER INPUT

APPLIES TO:  ACCESS 2007, 2010 (UNTESTED IN EARLIER VERSIONS)

The following tutorial will show you how to update a record in an MS Access table when a user clicks a button using VBA and SQL.

For this tutorial you will need to create the following:

• A table called “tblAccessories”, with the following fields:
  • A field called “accessoryId” (the field data type should be number)
  • A field called “accessoryName” (the field data type should be text)
• A record in tblAccessories with the field “accessoryId” set as 1

Note: Data types are very important when performing this type of operation so always ensure that you are working with the correct data type that you are trying to update.

• A form called “frmAddAccessory”
• A field on the form called “textbox1”
• A command button on the form called “btnAdd”

This example will demonstrate how to update a record in a table based on user input in an access text box on a form.

1. On the form you have created as per the above instructions, select the command button called “btnAdd”

2. In the tools section of the “Design” tab click on the item called “Property Sheet” and select the “Event ” tab

3. On the “On Click” property click on the little black down arrow / triangle and select “Event Procedure”

4. Click on the three dots next to the little black down arrow / triangle - this will open up the VBA window and you should see the following:

Private Sub btnAdd_Click()

End Sub

5. On a new line directly between the two lines above add the following 

DoCmd.RunSQL "Update tblAccessories SET accessoryName = " & Me!textBox1 & "  WHERE accessoryId = 1"

Save your changes and test your form / report