Recordset.AddNew Method VBA 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:
Table with data

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:
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:

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

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *