Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    St-Félicien, Québec
    Posts
    6

    Unanswered: many to many rel'nship in subforms

    I am relatively new to access, though I have some programming experience.
    I was wondering if there is any simple way to tie together a form and subform that use a junction table to relate their information without the user having to input anything.

    Here's the situation:

    Tables - primare key(s)
    tblRepFile - FileID (AutoNumber)
    tblAddresses - AddressID (AutoNumber)
    juncTblFileAddr - jFileID & jAddressID

    Forms - record source
    frmPane2(main form in this post) - tblRepFile
    sbfrmPane2Addresses - qryFileAddress (see below)

    Query
    sources are: tblRepFile > juncTblFileAddr < tblAddresses
    fields are: jFileID & jAddressID


    So sbfrmPane2Addresses is a subform in frmPane2. Somehow I need to assign the information from FileID to jFileID without the user's input.

    Just to be clear, the above are only the main fields involved, I have a rather complicated relationship structure that requires a whole bunch of junction tables. I'm not just adding many to many relationships for the sake of it (or out of ignorance).

    I have not yet entered any significant data so I'm up for any changes in order to get this to work. The important thing for me is end-user ease-of-use (hence this whole automation mess).

    Any help and/or suggestions would be greatly appreciated,
    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    22
    In the properties sheet of the subform control on the main form, you need to assign the Link Master Fields property to FileID and the Link Child Fields property to jFilesID.

    Jack

  3. #3
    Join Date
    Mar 2004
    Location
    St-Félicien, Québec
    Posts
    6
    Originally posted by jackb22
    In the properties sheet of the subform control on the main form, you need to assign the Link Master Fields property to FileID and the Link Child Fields property to jFilesID.

    Jack
    That was my first course of action and for some reason it doesn't seem to work. When I try to add new information it tells me that my key is blank (jFilesID being one of a two-part key).

    Thanks anyway. Does anyone else have any suggestions? If you need more info just ask.

  4. #4
    Join Date
    Feb 2004
    Posts
    22
    jfilesID should not be a composite key. It should be a single foreign key to link with filesID.

    Jack

  5. #5
    Join Date
    Mar 2004
    Location
    St-Félicien, Québec
    Posts
    6
    Originally posted by jackb22
    jfilesID should not be a composite key. It should be a single foreign key to link with filesID.

    Jack
    I am confused now. As far as I understood it, due to the nature of many to many relationships, one needs a juntion table. So I have a junction table. I also was led to understand that for a junction table to work properly, the primary keys from both tables involved had to combine to become the primary key for the junction table. So two foreign keys form the junction table's primary key. Was that just a coincidence in the many examples that I found? Or was there merely a mis-understanding in my previous explanantions?

    I attached a quick screen capture of the way relationships are set up in this database.
    Before I changed the names of the tables so they would be easier to uderstand.
    tblRepFile is acctually ficheReprese... in the capture
    FileID is IDFiche
    tblAddresses is addresses
    AddressID IDaddresse
    juncTblFileAddr is juncTbleFiche...
    jFileID is IDFiche
    jAddressID is IDaddresse

    if you think that having the same field names in different tables could still cause a problem I can change those.

    Thanks for your suggestions so far
    Attached Thumbnails Attached Thumbnails screencapture.jpg  

  6. #6
    Join Date
    Feb 2004
    Posts
    22
    What do you have as the Master and Child values for the subform control? Neither of these should be the composite primary key of the junction table.

  7. #7
    Join Date
    Mar 2004
    Location
    St-Félicien, Québec
    Posts
    6
    Originally posted by jackb22
    What do you have as the Master and Child values for the subform control? Neither of these should be the composite primary key of the junction table.
    the main form is pane2sbfrm and uses ficheReprese... as record source
    the sub-form is pane2adressesbfrm and uses qryFicheAddr as source

    qryFicheAddr uses IDFiche and IDaddresse from juncTblFiche...
    all three tables, involved in the rlationshiip are shown in the query design window

    that said, IDFiche in ficheReprese... is the master field in the link and IDFiche from qryFicheAddr (inherited from juncTblFiche...) in the child field

    I don't see how else I would manage to link these tables, am I missing something major here? Do you know of any good demonstrations of the use of many-to-many relationships in forms? I am interested in learning how to get this to work (and not take so much of your time)

    thanks for your ongoing patience

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: many to many rel'nship in subforms

    Originally posted by tranquilithé
    I am relatively new to access, though I have some programming experience.
    I was wondering if there is any simple way to tie together a form and subform that use a junction table to relate their information without the user having to input anything.

    Here's the situation:

    Tables - primare key(s)
    tblRepFile - FileID (AutoNumber)
    tblAddresses - AddressID (AutoNumber)
    juncTblFileAddr - jFileID & jAddressID

    Forms - record source
    frmPane2(main form in this post) - tblRepFile
    sbfrmPane2Addresses - qryFileAddress (see below)

    Query
    sources are: tblRepFile > juncTblFileAddr < tblAddresses
    fields are: jFileID & jAddressID


    So sbfrmPane2Addresses is a subform in frmPane2. Somehow I need to assign the information from FileID to jFileID without the user's input.

    Just to be clear, the above are only the main fields involved, I have a rather complicated relationship structure that requires a whole bunch of junction tables. I'm not just adding many to many relationships for the sake of it (or out of ignorance).

    I have not yet entered any significant data so I'm up for any changes in order to get this to work. The important thing for me is end-user ease-of-use (hence this whole automation mess).

    Any help and/or suggestions would be greatly appreciated,
    Thanks
    It sounds like you have the right idea, and I think your perception of the many to many relationships is on track.

    With forms that I have designed, I typically link the main form with the subform using the two ID's such as you have done. The subform then picks up one part of the composite key that is needed automatically. The other part of the key is usually a combo box used to look up the value from the other linking table, in this case I believe it would be the Address table.

    Are you currently having problems with the link being picked up or with the entry of the second field that is required?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  9. #9
    Join Date
    Mar 2004
    Location
    St-Félicien, Québec
    Posts
    6
    I thought I had posted a reply before (hence why it's taken me a while to respond).

    My situation is that while I have my main form linked to my sub form, the value isn't getting assigned so that one part of my primary key is missing, and the record cannot be saved. I haven't actively worked on the database for a while now but I am going to spend a chunk of time on it today and hopefully I will find the glitch. any solutions are still welcome. If anyone wants to see the database itself I can archive it (zip or rar) since I don't have any real data in it yet.
    thanks to all

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by tranquilithé
    I thought I had posted a reply before (hence why it's taken me a while to respond).

    My situation is that while I have my main form linked to my sub form, the value isn't getting assigned so that one part of my primary key is missing, and the record cannot be saved. I haven't actively worked on the database for a while now but I am going to spend a chunk of time on it today and hopefully I will find the glitch. any solutions are still welcome. If anyone wants to see the database itself I can archive it (zip or rar) since I don't have any real data in it yet.
    thanks to all
    I say zip it and post it. Just have enough data to test.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

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