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

Rachel Miller 21 Reputation points
2021-09-11T13:18:51.453+00:00

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.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
841 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ken Sheridan 2,751 Reputation points
    2021-09-11T16:48:02.543+00:00

    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.


0 additional answers

Sort by: Most helpful