I thought I’d cover the subject of creating/adding/inserting new records into a table using VBA.

As per pretty much anything with Access, there are a number of way to do this and I thought I’d cover three in this post

• Recordset .AddNew
• Form New Record
• Running an INSERT Query

For the samples below, I will simply try to create a new record in a table named ‘t_Clients’ and assign values of ‘Daniel’ to a field named ‘FirstName’ and ‘1999-11-22’ to a date field entitled ‘DOB’.

Recordset .AddNew

I think the most common approach we see is simply opening a recordset of the data source (table) and then through DAO adding a new record.  The basic code would be something like:

Private Sub cmd_AddRec_Click()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset

    If Me.Dirty = True Then Me.Dirty = False 'Save any unsaved data
    Set db = CurrentDb
    Set rs = db.OpenRecordset("t_Clients")
    With rs
        .AddNew
        ![FirstName] = "Daniel"
        ![DOB] = #11/22/1999#
        .Update
    End With
    'If you are doing this through a form which is bound to t_Clients,
    'then don't forget you need to requery it to see this addition
    'Next 2 lines are optional depending on the above
    Me.Requery
    DoCmd.GoToRecord , , acLast

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Form New Record

Another approach, which is ideal (well can only be employed) when you have a form directly bound to the data source (table) is to simply automate the form to navigate to a new record.  The code for this would be something like:

Private Sub cmd_AddRec_Click()
    On Error GoTo Error_Handler

    DoCmd.GoToRecord , , acNewRec
    Me.[FirstName] = "Daniel"
    Me.[DOB] = #11/22/1999#
    'Note: No requery is required in this instance!

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Running an INSERT Query

Finally, as you can create INSERT queries and run them, we can easily do the same in VBA.  The code for this would be something like:

Private Sub cmd_AddRec_Click()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim sSQL                  As String

    Set db = CurrentDb
    sSQL = "INSERT INTO t_Clients (FirstName, DOB) " & vbCrLf & _
           "VALUES('Daniel', #11/22/1999#)"
    db.Execute sSQL, dbFailOnError

    'If you wanted to you can at this point easily validate that the above
    'query actually worked by doing something like
    If db.RecordsAffected = 0 Then
        MsgBox "No New records were created by the above query?"
    End If

    'If you are doing this through a form which is bound to t_Clients,
    'then don't forget you need to requery it to see this addition
    'Next 2 lines are optional depending on the above
    Me.Requery
    DoCmd.GoToRecord , , acLast

Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

As you can see, there are several approaches that can be used to create new records in a table using VBA in Access.  Which one to employ depends on the situation.  In a general sense, if you already have a form open and bound to the table you wish to add a new record to, then the Form approach is typically best, otherwise, the choice is yours!