Recordset.AddNew Method VBA Access

Sep 05, 2015 by azurous in Access

The Recordset.AddNew method is a function used for adding records to a recordset.

–

Syntax:

Call objRecordset.AddNew([FieldList], [Values])

objRecordset:

A Recordset object.

FieldList:

A 1 dimensional array of the variant type. This array must contain the Field names.

Values:

A 1 dimensional array of the variant type. This array must contain the field values of the new record.

–

Example:

Consider the following table:

As you can see the table has the following fields:

MyField1
MyField2
MyField3
MyField4

The following code will add a new record to the table:

Sub Example1() Dim objRecordset As ADODB.Recordset Set objRecordset = New ADODB.Recordset Dim arrFieldList(0 To 3) As Variant Dim arrFieldValues(0 To 3) As Variant 'initated recordset obejct objRecordset.ActiveConnection = CurrentProject.Connection Call objRecordset.Open("MyTable1", , , adLockBatchOptimistic) 'set field array arrFieldList(0) = "MyField1" arrFieldList(1) = "MyField2" arrFieldList(2) = "MyField3" arrFieldList(3) = "MyField4" 'set value array arrFieldValues(0) = 10000 arrFieldValues(1) = 1100000 arrFieldValues(2) = "ranomd text" arrFieldValues(3) = "more random text" 'add new record Call objRecordset.AddNew(arrFieldList, arrFieldValues) 'update the recordset object objRecordset.UpdateBatch End Sub

Result:

Note: If you are working directly with an Access table, the values displayed in the table will not update automatically. One method would be to press “F5” to update the values being displayed in the table. For more information on this issue please see the link below:

VBA Access Table Not Updating

You can download the file and code used in this article from the link below:

AddNew.Zip