combo box with home and work add.. new records in table for each

[hjgwright]

Hi.
I have an Address table with Addrs_ID as the primary key, and an Address Type table which contains two records: Home Address and Work Address.

There is a Personnel contact form which has a subform for the address. The Address table has a field called 'Type' and uses the lookup to find the Address Type from the Address Type table.

The problem is that the user can only enter one address. I.E if I add a home address, it becomes the work address. If I change the work address, it changes the home address.

I think this is happening because there is in fact only one Addrs_ID. What I want to do is configure the combo box (Work or Home address) to create a new Address and Addrs_ID for eg Work if Home has already been entered.

I don't know how to do this (new to Access) and would love some help....

Thanks,
HWright.

 

[Pat Hartman]

You have the relationship defined backward. One contact has "many" addresses or at least two. Therefore the contactID goes into the address table rather than the way you have it.

The key to the address table should be made up of two fields -
AddrType and contactID. This will prevent more than one home address or work address for any contact.