[AccessD] Field linked to different tables

Thanks Gustav,  I was coming to the same conclusion.  Appreciate your reply.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, 11 August 2014 7:59 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com