Sometimes you need to insert a new record and then open a form with the new record displayed. You can only do so if you know the Primary Key value of the new record. I ran into an issue with AddNew in ADODB and using a SQL Server backend, the recordset will not return the last primary key inserted into the table. (The primary key is autogenerated by SQL Server). I came up with the following solution to share with you here.

Using Multilple SQL Statements with a Recordset
The solution is displayed below, it uses two SQL Statements, the first to insert the record and the second to fetch the last Identity value inserted.
    Dim strSQL As String
    Dim rs As adodb.Recordset
    Dim rs_Value As adodb.Recordset
   
    If IsNull(Me.JobID) Then
        Exit Sub
    End If
   
    strSQL = "INSERT INTO tblEvents(JobID,EventStatusID) Values(" & Me.JobID & ", " & conEventStatusNone & "); SELECT SCOPE_IDENTITY() as NewEventID"
    If con.State = adStateClosed Then
        OpenMyConnection 'A custom routine I use to open a connection, insert your own connection code here
    End If
    Set rs = con.Execute(strSQL)
    Set rs_Value = rs.NextRecordset
    With rs_Value
        DoCmd.OpenForm "frmEventItems", , , , , , !NewEventID
    End With

    Me.frmEvents_sub.Requery

   On Error GoTo 0
   Set rs = Nothing
   Set rs_Value= Nothing

You can of course accomplish the same thing with a stored procedure, which is the preferred method, but if you are not authorized to create the SP on the server this solution will come in handy.