Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Jerusalem, Israel
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Many to Many relations on forms (Access 2000)

    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

  2. #2
    New Lounger
    Join Date
    Apr 2002
    Location
    Columbus, Ohio, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Many to Many relations on forms (Access 2000)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Many to Many relations on forms (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •