Insert SQL statement using 2 tables and user input fields

tblOFInvDetail
   OFInvDetailID  Auto Key
   OfInvDetail  
   Company Code

tblOFInventory
   OFInventoryID  Auto Key
   OFInvTypeId    Not required
   OFInvDetailID   Numeric key from other table
   CompanyCode

The user wants to be able to Add a record to tblOFInventory but the record has to exist in tblOFInvDetail first.  My insert into tblOFInvDetail works but I need to then use the key created (in 1st tbl) as a field in tblOFInventory and Im not sure how to do that.  Im trying to insert into tblOFInventory the key from tblOFInvDetail where OFInvDetail = the users NewData (OFInvDetail) input.  


strNewSQL = "INSERT INTO tblOFInvDetail (OFInvDetail, CompanyCode) VALUES ('" & NewData & "','" & Me.txtHidCurrentCo & "')"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strNewSQL
        Response = acDataErrAdded
***The Above all works.  A record is inserted.  The following does not.
Error = Syntax error, missing operator in query expression.

        strNewSQL2 = "INSERT INTO tblOFInventory ( OFInvDetailID, CompanyCode )        SELECT tblOFInventory.OFInvDetailID FROM tblOFInventory INNER JOIN tblOFInvDetail ON tblOFInventory.OFInvDetailID = tblOFInvDetail.OFInvDetailID
VALUES ('" & NewData & "','" & Me.txtHidCurrentCo & "')"
        'DoCmd.SetWarnings False
        DoCmd.RunSQL strNewSQL2
        DoCmd.SetWarnings True
        Response = acDataErrAdded

Thanks in advance.
Start Free Trial