AddNew Method

Applies To

Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Creates a new record for an updatable Recordset object.

Syntax

recordset.AddNew

The recordset placeholder is an object variable that represents an updatable Recordset object to which you want to add a new record.

Remarks

Use the AddNew method to create and add a new record in the Recordset object named by recordset. This method sets the fields to default values, and if no default values are specified, it sets the fields to Null (the default values specified for a table-type Recordset).

After you modify the new record, use the Update method to save the changes and add the record to the Recordset. No changes occur in the database until you use the Update method.

Caution    If you issue an AddNew and then perform any operation that moves to another record, but without using Update, your changes are lost without warning. In addition, if you close the Recordset or end the procedure that declares the Recordset or its Database object, the new record is discarded without warning.

Note   When you use AddNew in a Microsoft Jet workspace and the database engine has to create a new page to hold the current record, page locking is pessimistic. If the new record fits in an existing page, page locking is optimistic.

If you haven't moved to the last record of your Recordset, records added to base tables by other processes may be included if they are positioned beyond the current record. If you add a record to your own Recordset, however, the record is visible in the Recordset and included in the underlying table where it becomes visible to any new Recordset objects.

The position of the new record depends on the type of Recordset:

  • In a dynaset-type Recordset object, records are inserted at the end of the Recordset, regardless of any sorting or ordering rules that were in effect when the Recordset was opened.
  • In a table-type Recordset object whose Index property has been set, records are returned in their proper place in the sort order. If you haven't set the Index property, new records are returned at the end of the Recordset.
The record that was current before you used AddNew remains current. If you want to make the new record current, you can set the Bookmark property to the bookmark identified by the LastModified property setting.

Note To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet workspace, or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.

See Also

Bookmark property, CancelUpdate method, Delete method, EditMode property, Index object, LastModified property, Move method, MoveFirst, MoveLast, MoveNext, MovePrevious methods, Seek method, Update method.

Specifics (Microsoft Access)

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

Example

This example uses the AddNew method to create a new record with the specified name. The AddName function is required for this procedure to run.

Sub AddNewX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strFirstName As String
    Dim strLastName As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)

    ' Get data from the user.
    strFirstName = Trim(InputBox("Enter first name:"))
    strLastName = Trim(InputBox("Enter last name:"))

    ' Proceed only if the user actually entered something
    ' for both the first and last names.
    If strFirstName <> "" and strLastName <> "" Then

        ' Call the function that adds the record.
        AddName rstEmployees, strFirstName, strLastName

        ' Show the newly added data.
        With rstEmployees
            Debug.Print "New record: " & !FirstName & _
                " " & !LastName
            ' Delete new record because this is a demonstration.
            .Delete
        End With

    Else
        Debug.Print _
            "You must input a string for first and last name!"
    End If

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Function AddName(rstTemp As Recordset,
    strFirst As String, strLast As String)

    ' Adds a new record to a Recordset using the data passed
    ' by the calling procedure. The new record is then made
    ' the current record.
    With rstTemp
        .AddNew
        !FirstName = strFirst
        !LastName = strLast
        .Update
        .Bookmark = .LastModified
    End With

End Function
Example (Microsoft Access)

The following example creates a new record in an Employees table and saves the changes:

Sub AddNewRecord(ByVal rst As Recordset, _
    strLast As String, strFirst As String)
    
    With rst
        .AddNew                                ' Add new record.
        !LastName = strLast                ' Add data.
        !FirstName = strFirst
        .Update                                ' Save changes.
    End With
End Sub