How-To: Add a New Record

Consider the following table:

One of the most routine operations performed on a database consists of creating records. You can do this in the SQL, the Microsoft Access Object Library, and DAO. If using a Recordset object in a database that uses either the Microsoft Access Object library or DAO, to create a new record

1. Call the AddNew() method of your Recordset object
2. Identify each column whose value you know by passing its name as the index to the Fields property, and assign the desired (and appropriate) value to it. It is important to keep in mind that there would not be any checking on required fields or the types of values until the code runs. This means that you should be aware of the types of values you are dealing with and you should know what columns are required
3. After assigning the values, call the Update() method of the Recordset object.

Here is an example using DAO to create a new record in a table named Videos:

Private Sub cmdAddVideo_Click()
   Dim dbVideoCollection As DAO.Database
   Dim rstVideos As DAO.Recordset

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

   rstVideos.AddNew
   rstVideos("Title").Value = "Cape Fear"
   rstVideos("Director").Value = "Martin Scorsese"
   rstVideos("CopyrightYear").Value = 1991
   rstVideos("Length").Value = "2 Hours 8 Mins"
   rstVideos("Rating").Value = "R"
   rstVideos.Update
End Sub