In a nutshell: Using ODBC to connect to Sql Server 2008 R2. These are my slimmed down tables: contacts and address. I realize my problem may be a query problem and not a subform problem, but I'm a beginner.



Contacts
ContactID PK int Identity (in Sql)
LastName
FirstName

Addresses
AddressID PK int Identity
ContactID FK
Address

For my contacts who already have addresses, there is no problem. I go to the address subform and enter/save data. The problem is with contacts without existing addresses. My thought was that I could enter an address and the AddressID would autoincrement, because that's what would happen in Sql. It's an identity field. As soon as I start typing though, the ContactID that I carried from the master form disappears as does the "New" in my AddressId.

In order to edit, I have to add all fields to the query behind the subform. When I type in an address, it will say, "Cannot add null to ContactID". Why is it trying to add a new contact record? I just want it to add a new Address record. Also, why doesn't AddressID autoincrement?

My goal is to avoid VBA, because I'm giving this to a group who knows even less about Access than I do, and they will have to maintain it. Can I fire off a new record in the Address table with a macro?

Thanks for any help.