2

I currently have three tables as shown below: enter image description here

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

But, it is giving me this error:

enter image description here

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.

Improve this question
1
  • It may also be helpful, if only for clarity's sake, to rename Townships.CountyName to Townships.CountyID.
    – C Perkins
    Commented Aug 1, 2017 at 21:39

1 Answer 1

Reset to default
0

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.

Improve this answer
4
  • Certificates.TownshipID is required. I am trying to eliminate unnecessary entry keystrokes, i.e. shouldn't be necessary to input the county if the township is entered. I had considered denormalizing the Certificate table and just having a standalone field for county in the Certificate table since it's a commonly used search criteria, but wanted to stick with proper normalization.
    – megruder79
    Commented Aug 1, 2017 at 21:42
  • The answer I shared should work as you have it, but in the case that TownshipID is required, then you shouldn't need the CertCounty field in the Certificates table. That actually is not normalized for that field since you're copying that fields value for every update. There are various other ways to include the County on the form and even make it searchable, all the while having it queried from the township relationship. That's a whole other topic, but perhaps keep it in mind.
    – C Perkins
    Commented Aug 1, 2017 at 21:57
  • I was mostly concerned that we rarely search by township and frequently use the county field. I was thinking about efficiency and do not really know how much searching by information two tables away would impact speed.
    – megruder79
    Commented Aug 2, 2017 at 21:38
  • Okay, so were you able to get the Before Change macro working?
    – C Perkins
    Commented Aug 2, 2017 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.