HOW TO ADD A NEW RECORD TO A MICROSOFT ACCESS TABLE USING VBA / ADDING A RECORD TO AN ACCESS TABLE BASED ON USER INPUT
APPLIES TO: ACCESS 2007, 2010 (UNTESTED IN EARLIER VERSIONS)
The following tutorial will show you how to add a new record to an 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 a field called “accessoryName” (the field data type should be text.)
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 collect user input in an access text box on a form and save it to a table.
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()
5. On a new line directly between the two lines above add the following
DoCmd.RunSQL “INSERT INTO tblAccessories (accessoryName) ” _
& “VALUES(” _
& “‘” & Me!textbox1 & “‘) “
Save your changes and test your form / report
Note: This method is only used for adding a new record to / row to a table, I will cover updating an existing record in a future tutorial or email me for help.