[AccessD] Field linked to different tables

Hi David

You'll need an WhoAboutID to distinguish the normal Ids from the three tables.

For example, instead of a union of the three tables, create three queries like:

Select 1 As WhoAboutID, * From tblClient
Select 2 As WhoAboutID, * From tblStaff
Select 3 As WhoAboutID, * From tblCompany

then union these, and either use the compound id of WhoAboutID and ID or create a single text id:

[ID] & ";" & [WhoAboutID] As UnionID

for your combobox. When selected, you can use Split to split the UnionID into WhoAboutID and ID.

/gustav


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af David Emerson
Sendt: 11. august 2014 05:24
Til: AccessD
Emne: [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