Can you make a form control that has a data source that is different from where the data is stored?

Question

Ok- not sure I did a great job of wording that question. It might be easier to share the specifics.:

The population we are tracking with the database moves frequently. We would like to track their new addresses, of course, but keep the original city from our first contact with the person while minimizing extra data entry steps.

It feels like there are two possible solutions:

Solution 1.
Add a "CityAtEntry" field to the underlying table.

Add a corresponding form field control to the intake data entry form controlled by the City entered in the regular data entry form, so no one has to enter a city twice.

But for that method to work, I'd need to have the source of the form field control one thing (City Data Entered text box) and have the data entered into the form field control save to an underlying table as a separate thing (CityAtEntry).

I don't think I can split the underlying source for a control like that.

Solution 2. Absolutely will not work- too much is required of the user:

But would require manually saving "CityAtEntry" on a form when the person has a new address.

Any ideas for a prettier solution?

Thanks in advance.

Answer 1

If you add a CityIDAtEntry column to the table as you suggest you can then automatically insert a value into it when you enter the first address for the person concerned by means of code along the following lines in the AfterUpdate event procedure of the CityID control:

If IsNull(Me.CityIDAtEntry) Then
    Me.CityIDAtEntry = Me.CityID
End If

The value in the CityIDAtEntry column will remain static when any amendments are made to the CityID control by virtue of it not being Null after the first execution of the above code.

Note that the values stored in these columns should be long integer number foreign keys referencing the autonumber CityID primary key of a separate Cities table in an enforced relationship. City names are unsuitable as keys as they can legitimately be duplicated. For an illustration of how to store address data in a set of correctly normalized tables take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the sections on 'one to many relationship types' and 'bringing the model together' illustrate a suitable model for addresses. In the section on 'entering data via a form/subforms' a contacts form illustrates how such data is entered, using a bound combo box for the CityID and unbound combo boxes for the region and country in which the city is located.