Adding record to multiple tables

[Garrett!]

I am using Access 2007 on my front-end and SQL Server 2014 on the back-end. I have a table of Car Dealers and a table of contacts at the dealerships. These tables are SQL tables. The user can select a dealer and then see everyone that works at that dealership. When they look at this there is a field called Email. This is a hyperlink that they can click on to open Outlook and send an email. The table called DealerEmails is an Access table. My table layout is:

Dbo_Dealers
------------------
ID (PK)
DealerName
DealerAddress
DealerCity
DealerState
DealerZip
DealerPhone
ModifiedBy
ModifiedDate

Dbo_DealerContact
---------------------------
ID (PK)
LastName
FirstName
Postion
DealerID (FK)
ModifiedBy
ModifiedDate



DealerEmails
-----------------
ID (PK)
DealerContactID (FK)
Email
ModifiedBy
ModifiedDate

Now I'm trying to write the code to add a new contact. My code works but I need to obtain the AutoNumber from when I add a new record to the table dbo_DealerContact. My code is:

Option Compare Database
Option Explicit

Dim adoDealerContacts As New ADODB.Recordset
Dim daoDealerEmails As DAO.Recordset

Private Sub cmdSave_Click()

Dim intDealerID As Integer
Dim intDealerContactID As Integer

    'Obtain the ID (primary key) of the Dealer
    intDealerID = cboDealerName.Column(0)
    
    'Add a new record into the DealerContacts table
    With adoDealerContacts
        .AddNew
        .Fields("FirstName").Value = txtFirstName
        .Fields("LastName").Value = txtLastName
        .Fields("Position").Value = txtPosition
        .Fields("DealerID").Value = intDealerID
        .Fields("PhoneNumber").Value = txtPhoneNumber
        .Fields("FaxNumber").Value = txtFaxNumber
        .Fields("Notes").Value = txtNotes
        .Fields("ModifiedBy").Value = fOSUserName()
        .Fields("ModifiedDate").Value = Now()
        .Update
        'Obtain the ID (primary key) of Dealer Contact
        intDealerContactID = .Fields("ID").Value
    End With
    
    'Add new record into the DealerEmails table
    With adoDealerEmails
        .AddNew
        .Fields("DealerContactID").Value = intDealerContactID
        .Fields("Email") = txtEmail
        .Fields("ModifiedBy").Value = fOSUserName()
        .Fields("ModifiedDate").Value = Now()
        .Update
    End With
    
End Sub

Private Sub Form_Close()

    'Close ADO connections
    adoDealerContacts.Close
    Set adoDealerContacts = Nothing
    
    daoDealerEmails.Close
    Set adoDealerEmails = Nothing
    
End Sub

Private Sub Form_Load()

Dim strSQL As String

    'Create connection for the Dealer Contact Recordset
    strSQL = "SELECT * FROM DealerContact"
    adoDealerContacts.Open strSQL, Form_frmMainScreen.connDB, adOpenDynamic, adLockOptimistic
    
    'Create connection for the Dealer Email Recordset
    strSQL = "SELECT * FROM [DealerEmails]"
    Set daoDealerEmails = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    

End Sub

I tried to add Me.Dirty=False, but this still returned a value of 0 into my variable intDealerContactID.
I also tried moving intDealerContactID = .Fields("ID").Value outside of the With block.

I'm aware that there is a command in SQL @@Identity. But I'm unsure how to use it in this context.

Is there a way to get the primary key from dbo_DealerContacts so I can insert that into my Emails table?

Thanks you for any help.

 

[spikepl]

Try

       .AddNew
        intDealerContactID = .Fields("ID").Value
        .Fields("FirstName").Value = txtFirstName
        ...

UPDATE
Why mix of ADO/DAO?

 

[Garrett!]

When I use ADO for the DealerEmail table I get a run-time error saying: Invalid object name 'DealerEmails'. I assume this is because of the code on another form where I load the connection:

Option Compare Database
Option Explicit

'ADODB connection to tblBrand
Public adoDealerBrands As New ADODB.Recordset

'Connection to the database
Public connDB As New ADODB.Connection
Private Sub Form_Load()

    'Open the connection to the SQL database LGS_Util
    connDB.Open "Provider=SQLOLEDB;Server=LookData1;Database=LGS_Util;Trusted_Connection=Yes;"


End Sub

I thought it was because I am connecting to the SQL database and DealerEmail is an Access table. I could very well be doing something wrong.