[AccessD] Field linked to different tables

Hi Listers,

 

I am looking for some data structure advice.  I will use a simple example
but the real world application will be more complicated.

 

I have a Notes table.  The Notes table fields include WhoAbout,
LinkRecordID.

 

WhoAbout could be Client, Staff member, or Company. 

LinkRecordID is the ID from the Client, Staff member, or Company table
depending on what was selected in the WhoAbout field.

 

The records from the Notes table will be displayed in a continuous form
showing WhoAbout and the name from the corresponding table for the record
selected in LinkRecordID.

 

The Linkrecord field on the form is a combobox with a union of the records
from the three tables.  Data from all three tables are needed because it is
a continuous form and I don't want to change the recordsource for each
current row as this would show incorrect data in other rows.

 

Immediate problem: Even though I could put code in place to ensure that the
record selected is of the same type as the WhoAbout field the correct row
may not show if it has the same ID as another record from one of the other
tables. 

 

Question:  Has anyone else come across this problem and what might be the
solution?

 

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand