Using different fields to define and set relationships

[danek]

Hi everybody!

New around here. I have a database which has a couple of tables, and records that tie in to other tables based on a name field relationship. Problem is, if you change the name on a record (to correct a typo or for whatever other reason) it breaks the relationship with all the records in other tables using that name.

To try and work around this problem, I gave each record a unique serial number that automatically gets generated when the record is created. This number does not appear on any layout, so it can't be changed accidentally. Currently this fix makes things less user-friendly, because to assign a record to a related record in another table, one must know and enter this number.

What is a good way to use a different, human-readable field to select a record in another table, and set the value of a hidden field to define a relationship to this record?

[Dr. Evil]

In your value list, check

"Also display values from second field"

and choose the name field. Also, check

"Show values only from second field"

Then put the related name field next to or on top of your ss#field.

Then user will only see names, the ss# works in the background.

Let me know if you have any questions.

[danek]

That worked like a charm! Thanks a million!