Query as Form RecordSource question

[WAstarita]

You feel like your becoming an expert at this stuff and then something
like this stumps the hell out of you....


I'm creating a database of service customers. I started the Customer
and customer contact section like this


Customers
- Addresses
- Contacts

Each Address had a CustomerID field linking it to a customer
Each Contact Record had a CustomerID and AddressID field linking it to
it.

That worked fine except
1. The complexity of the business logic layer to make sure when you
add a contact the address was one of the ones listed under the
CustomerID.
2. If I have different customers who share the same Bill To addresses
or Contacts, I would have to make the same addresses and contacts all
over again.


I'm trying to move to a Global Address / Contact model where instead of
having a specifc address linked to a specifc customer linked I have a
seperate address table that everyone can pull from, a seperate contacts
table everyone can pull from and an intermediate table to associate an
address with a contact or a contact with a customer. For example:

Addresses ------ ContactAddresses -------- Contacts
AddressID AddressID | ContactID ContactID

Create relationships and I'm done.

My real question is, if I wanted to setup a linked form that will add
an address to the addresses table and at the same time assign it to
contact in the ContactAddresses table, how would I do it? lets assume
for the example we are only dealing with the Primary keys to keep it
simple, I've tried something like

RecordSource = "Select Contacts.ContactID, ContactAddresses.RecordID,
ContactAddresses.AddressID , ContactAddresses.ContactId,
Addresses.AddressID FROM Contacts...... With various join
configurations.

Is there one Query I could build as the recordsource that would
automatically do the functionality I'm looking for without any back end
programming, assuming you knew the ContactID you were linking it to
(Maybe Recordsource = "Select..... WHere ContactID = "A recordID you
set at form open".

I hope I'm getting this across properly. I'm not trying to get anyone
to do this for me, just point me in the right direction, I think I'm
lacking in some SQL/Query theory. Or maybe it's not possible and I
have to use ADO or Subforms(Yikes).

Thanks in advance

 

[Michel Walsh]

Hi,


If you use a form, with the a relevant query as recordsource, like:


SELECT ca.*, c.*, a.*
FROM (ContactAddress AS ca INNER JOIN Contacts As c ON
ca.contactID=c.contactID)
INNER JOIN Addresses as a ON ca.address=a.address

and use combo box with NotInList event for CustomerID and ContactID, then
you can match existing contacts and existing addresses, or through the "not
in list" event handling, add the required contact, or address, before adding
a new record in contactAddress.

You don't need the query if you don't want to see contact information, or
address information, but in general, we do want see the details, not the
primary keys, so that kind of query can be useful to show the data, inside a
form.


Hoping it may help,
Vanderghast, Access MVP