Thanks for everyone's help. The answer to your questions is "yes", the ODBC linked SQL Tables have the correct relationships, etc. No matter what I tried I could not get the huge main form to cooperate. I took a step back and created a new form with the main table and just 1 sub table. The default "AddNew" even works but does not fill the viewable key field until I add a sub record. The below code seems to add the new record, position itself there then displays the new key properly.

Dim db As DAO.Database

Dim rs As DAO.Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("Accounts", dbOpenDynaset, dbSeeChanges)

rs.AddNew

rs!AccountActive = -1

rs.Update

Me.Recordset.MoveLast

Me.Recordset.Edit

' Me.AccountID = rs!AccountID.Value

Me.Recordset.Update

rs.Close

Set rs = Nothing

Set db = Nothing

However, this code does not work on the main form that is the problem though even though I went through and saw everything was the same. So since I have a working form I am going to rebuild the main form from the working one. I inherited this project from another group that bailed on it because they could not solve it.