Populating one foreign key based on entry of another foreign key in Access 2016 I currently have three tables as shown below:


The user enters Certificates via a form. I want CertCounty to populate based on selection of TownshipID. I've attempted the following Data Macro:


But, it is giving me this error:



I'm pretty sure my expression for the Value is incorrect as it was a stab in the dark and I had little confidence it was correct. I'm also open to other solutions, i.e. VBA, just not sure what to do as I'm still learning how to work with databases and, in particular, Access.
Just because [TownshipID] is populated (i.e. not null) and because there is a relationship defined between the two tables, these facts do not mean that the table [Townships] is open and positioned at any particular record.  You must specifically direct Access to open the table and look for the related record.  You do that in the Before Change Data Macro by using the LookupRecord action.

Look Up a Record In: [Townships]
  Where Condition: Townships.ID = Certificates.TownshipID


Within that action's group is where you place the SetField action.



Other comments:

Data Macros are best for validating data.  They also have their place in keeping tables in sync as you are doing, but these conditions should be rare, especially if your tables are properly normalized.

It appears that Certificates.TownshipID is optional and can be null.  I assume that means the county can be specified independently for the certificate when there is no township.  Correct?  If so, your technique is probably justified.  Otherwise, there would be no need to also store the county with the certificate since it could always be retrieved via the Township relationship.

Since you already have a form where Certificate data is entered, you could also place relevant code in the form's module, for example in the TownshipID_AfterUpdate() event handler (or similar method) to retrieve and update the county information.