[AccessD] A little help, please

Your problem here is that you are trying to display data about two completely different entities. 
Faculty / staff is not Vendors.  This kind of problem always rears its head when you try to handle 
two entities in a common manner.

I think given what you are trying to do you need to create a payee table (which is probably what you 
are in the process of doing).  This table will have all of the common data that needs to be present 
in order to process a payment.  Things like name, tax id, address etc.  If that data is required to 
issue a check, then it has to be present in the source table, whether t1 or t2.

Now... pull all of that data into the combo as hidden fields.  When you select an entity in the 
combo have the code copy the hidden fields into the payee table fields.  You might (probably do) 
want to also copy the PKID in the source table into a ExternalPK field and have another field which 
is a number representing the source - 1 for T1, 2 for T2.

This basically makes a COPY of the relevant table into the Payee table, complete with the PK back in 
the source table in case you need to get back there.

In other words, abandon the idea of storing the PK of the foreign tables t1/t2 as the "bound field" 
of the combo.  That just won't work.  It MIGHT (kinda sorta) work if you used the foreign PK and the 
"source" field as a 'multifield FK', but it would probably be ugly and Access combos cannot be 
"bound" to two fields simultaneously.

Alternately, you could append the external table PKID with a dash and a "source number", IOW 1324-1 
(PKID 1324 from t1) or 5376-2 (PKID 5376 from t2) and use that STRING as the FK in the payroll 
table.  This does work, and as long as the number of source tables is not going to grow...  You 
would need to go to both of the source queries for the union, and create a new PK column where you 
create this string.

This method has the advantage of "not storing the data twice", along with eliminating all of the 
update stuff you would have to do to stay in sync with the external tables.  Let the source tables 
continue to be the place where the data is kept.  Of course now all of your queries everywhere has 
to recover the data from different places.

Ugly no matter which way you go.

John W. Colby
www.ColbyConsulting.com


Martin, Donna M wrote:
> Perhaps my entire approach is wrong.  Here's what I am trying to do:
> I am trying to create a list of payees from two tables:  1) Faculty/staff info is added by HR personnel to database1.T1; Vendors and external people would be entered into database2.T2.  Only T2 would ever be updated from this source.  And yes...it is a concern that both have autonumber pk's, which I have no idea how to resolve.  FYI...a new record would require more than just the name of the vendor (e.g., phone #, etc).
> 
> I am using Access2007.
> 
> Thanks!  Donna