Modifiable data as relashionship key!

[joebwan]

I know that using modifiable data as the relationship key between tabls is a no-no. Here is what I want...

Table A with one or more ID fields that relate to table B. In story-problem-speak, let's say table A is an event and table B are participant names and each record in table A can have, say, three particpants.

So far so good, but the trick is to build a layout with three layout-fields that each display a specific piece of data from the related table B, but not the ID number. I want the related piece of data instead.

It would be nice to have the layout use a drop-down listbox to select entries from table B and when selected, it would update the appropriate ID field in table A to reflect the data chosen from the drop-down listbox.

I could use some help to point me in the right direction here.

[Vaughan]

Have you tried lookups?

[joebwan]

I suppose a field that I filled with a lookup value would work if it had an attached value lookup drop-down listbox attribute AND just at the moment the value was chosen a script would run to update the ID value. But is there a way to run a script when a field changes value??? I didn't see how to do that.

[Vaughan]

"I know that using modifiable data as the relationship key between tabls is a no-no."

Now you know why. Once the relationship key changes you can no longer perform a re-lookup to update the keys!

[joebwan]

If a script could automatically be called when a layout shows a new record and again when a field shown in the layout changes, then I think it could work. The lookup field would be global and set only for purposes of displaying the looked-up value rather than the underlying table linking ID. So the user sees a dropdown value list that looks something like...

Bob

Carol

Ted

Alice

Then when one is picked, the PersonID field gets updated with the correct value. The problem, this solution requires triggering a script to run at certain points that I don't know how to trigger stripts at.

Does everyone else who build data entry layouts with related tables force the operator to manually enter the KeyID field numbers?