Many to Many relations on forms (Access 2000)

Topic

New Reply

Hi,

I need help creating data entry forms. I’m new to Access, but not to VB or VBA with Word and Excel.

I created tables with Many to Many relationships, for instance Contact person to address. Person may have more than one address (home, business), more that one person can live/work at single address.

Contacts Table
ID (primary key), Name, personal info….

Contacts_Address Table
Contact_ID, Address_ID, (the two IDs are the primary key), role at address

Address Table
ID (primary key), Phone, address, email….

For data entry:
1) Need to create contact and address and have the Contacts_Address link table updated as well.
2) Need to create contact and link to existing address (by getting address with a different contact, e.g., parent or spouse).
3) Need to add new address to existing contact.
4) Need to link existing contact with existing address.

I am having no luck getting my tables and forms to link and synch. I’ve experimented with forms, sub-forms and event procedures. Unfortunately the sample Contact.mdb doesn’t have my problem and I can’t find answers in the help.

I’m sure this is do-able and it’s just my lack of experience.

Can anyone help? Advice where to get info? Sample where this is done?

TIA,

Steve Silberman

Reply | Quote

Replies

Steve: Think of Many-to-Many relationships as two, one-to-many relationships. Then create forms and subforms to handle each, i.e., Main form # 1 would be based on Contacts table, subform would be based on a query of Address table and ContactsAddress table. Main form # 2 would be based on Address table and the subform would be based on a query of Contacts and ContactsAddress table. Check out the Student and Classes database wizard in Access 97 to see a good example of how this would work. HTH.

Reply | Quote

For a simpler approach using one main form and one subform check out this thread. There is also an attachment with a sample of a many-to-many relationship with main form and subform. Just replace courses with contacts and criteria with addresses and you should be able to fill in the rest from there.

Reply | Quote