This is a question that frequently comes up in the forums:

How can I get the Primary Key value of an insertion performed through VBA?

The issue here depends on the insertion method adopted.
 

INSERT INTO / DLookUp

Typically, a lot of times the simplest insertion approach is to perform a INSERT INTO query

Currentdb.Execute "INSERT INTO Companies ( Company ) VALUES('Some Company Name');", dbFailOnError

In such a case, there is no direct way to retrieve the table’s PK value for the inserted row of data.  That said, one could always resort to performing a DLookUp() on the table, something like

lPkValue = DLookup("CompanyId", "Companies", "[Company]='Some Company Name'")

 

OpenRecordset

The better approach though is to use the OpenRecordset approach to inserting records.  The benefit is that you can perform both actions in a single action, rather than 2 as with INSERT INTO …

The basic concept is illustrated below

'Assuming Table named "Companies"
'   Field "CompanyId" AutoNumber PK
'   Field "Company" Short Text
Sub AddNewEntryDemo()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim lPkValue              As Long

    Set db = CurrentDb
    sSQL = "SELECT * FROM Companies WHERE 1=0;"
    Set rs = db.OpenRecordset(sSQL)
    With rs
        .AddNew
        ![Company] = "Some Company Name" 'Append any data you want to specific fields
        lPkValue = ![CompanyId] 'Retrieve the PK value for the new record
        .Update
    End With
    
    'Lets confirm we captured the new entries PK value and compare to the table value
    Debug.Print lPkValue, DLookup("CompanyId", "Companies", "[Company]='Some Company Name'")

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 occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: AddNewEntryDemo" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

As you can see, you use the .AddNew to start creating a new recordset at which point the PK field value can be accessed and read back to a variable.
 

@@IDENTITY

Another approach that can be used with the INSERT INTO approach which is to use @@IDENTITY

Sub AddNewEntrySQLServerDemo()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim sSQL                  As String
    Dim lPkValue              As Long

    Set db = CurrentDb
    sSQL = "INSERT INTO Companies (Company) VALUES('Some Company Name');"
    db.Execute sSQL, dbFailOnError
    lPkValue = db.OpenRecordset("SELECT @@IDENTITY")(0)

    'Lets confirm we captured the new entries PK value and compare to the table value
    Debug.Print lPkValue, DLookup("CompanyId", "Companies", "[Company]='Some Company Name'")

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 occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: AddNewEntrySQLServerDemo" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

 

SharePoint

There’s been discussion in the comments below regarding SharePoint. I don’t use it, so I’ve never developed against it. Based on the comments it would seem the solution provided by GianPaolo Santonicola works with SharePoint, so I’m adding it here for reference in the hope it helps others.

Set db = CurrentDb
sSQL = “SELECT * FROM Companies WHERE 1=0;”
Set rs = db.OpenRecordset(sSQL)
With rs
    .AddNew
    ![Company] = "Some Company Name" 'Append any data you want to specific fields
    .Update
    .Bookmark = .LastModified
    lPkValue = ![CompanyId] 'Retrieve the PK value for the new record
End With

 

Conclusion

As you can see, it isn’t hard to grab the PK value for a newly inserted record in a table.

One word of caution however, I have seen numerous times cases where developers perform an insert through a query

Currentdb.Execute "INSERT INTO Companies ( Company ) VALUES('Some Company Name');", dbFailOnError

and then use DMax() to retrieve the greatest PK value, assuming it will be the last entry.

lPkValue = DMax("CompanyId", "Companies")

This is dangerous and ill-advised as it can happen that another user performs an insertion at the same time and you end up retrieving the wrong PK value.  This is why using DLookUp() is more reliable, but must be done using enough criteria to guarantee uniqueness of the retrieved data to ensure you get the proper PK value returned. Dlookup() requires an extra call, which is a little less performant.

For me, after years of doing this, I typically use the OpenRecordset approach and avoid DLookUp as much as possible.