Access move to SQL backend - Foreign key not added to related table

Pretty new to Access and working on an inherited database. I have move everything to a separate front end/back end, the back now in SQL Server.

One of the issues I'm struggling with is updating the foreign key in another table when creating a new record in the primary. The client table called active holds basic client information and the tracking table holds additional information for tracking data on that client.

The primary key in active is ClientID and it has a relationship with the column called Client ID in the Tracking table.

When creating a new record in active, the Client ID column does not get populated with the PK from the active table. If I try to make changes in the subform for the tracking info, I get an error 3164. If I manually add the Client ID number in the tracking table, then the subform works just fine.

The subform is using a query to pull it's information. The FK relationship is built both in SQL Server and the Access front end. "ID" is the primary key for the tracking table.

Any assistance on this would be greatly appreciated.

Well, when using either a sql back end, or even linked tables to a access back end, the relationships in the front ends does not really change anything. (it is always the back end that controls this, else what would occur if two front ends with different relationships were to open that database? Now who would be in charge?

and even with a pure access application, setting up relationships NEVER would then automatic setup the FK's for you when adding records in a sub form. How does the data engine know what the parent record is when adding a record?

Answer: it has no clue.

What this means? Well, if you add a child record with code, then you have to in code set that FK value.

And if you adding records in a sub form, and you want access to setup that FK for you?

Then you have to setup and TELL access to set and insert that FK value for you. You can EVEN do this if no relationships were defined in the database. So this process is really a manual process and setting on your part.

So, you need to open up the form in design mode (the one with the sub form). You then display the properties for the sub form control (not the sub form!!! - but the sub form control.

You will see this setting:

So the above link child and link master fields have to be setup. Without this setting EVEN when you have enforced and setup relationships in a database, you STILL have to set up the above link master/child settings.

So, Access does not automatic insert those FK's for you - you have to TELL access which ones to use. Now to be fair, if you have defined relatonships, then when you create and drop in a sub form to a main form? then access can set the above two fields (the PK of the main form, and the FK to be used in the sub form). But, that setting does not necessary need or even rely on any relationships you have - you MUST set the above up.

And this setting will and should work fine with SQL server. Access ALWAYS does a automatic save of the main form when you move focus to the sub form (Access does this, so the parent record exists and is saved BEFORE you start editing the sub form records).

And when you add a sub form record, then the link child/master settings are used to determine what PK value from the main form is moved + used in the sub form when adding new records.