Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2005
    Posts
    12

    Question Unanswered: Subforms using junction table

    Yet another post of my confusion

    I have a many to many relationship. I am making a database for contacts and the 'groups' they are in. Basically, I want one table of contacts and I want to be able to to call up a group which will list everyone within it so I can mail merge a letter or email to them. (I've not actually gotten that far yet though).

    The problem is, it's a many to many relationship. I've put in a junction table, and I've taken a screencap of the result.

    What I am now trying to do is create a form to input the contact details, and to say which groups that person belongs to, if any. I also want to be able to use this form to edit details.

    Does that make sense?

    So far, I'm having no luck. I've been trying to create a subform, but the best I can get is a list of IDs and no description... and when I try to select one, it tells me I need a contact to match (something along those lines)

    Any ideas? Please?
    Attached Thumbnails Attached Thumbnails relationships.gif  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Webgirly

    Assuming you want the form to be contact-centric then...
    Parent form record source = contacts and all the details you want to enter.
    Sub form would be based on your junction table. It would be linked by the ContactsID parent and child fields (check the subform properties).
    Pop a combo box in the subform. Make the source for this the Groups table. Include the group name and the groupID fields. The groupID field is the one you will be using. Set the control source to the GroupID field of the junction table.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    12
    hahaaaaaa!!! OMG you're a GENIUS!!! I have been told combo boxes, and subforms and I've tried both, I never realised I needed a combo box within a subform!

    It's actually Working! Thank you, thank you, thank you!

  4. #4
    Join Date
    Dec 2005
    Posts
    7
    Hi,

    I have the same problem.
    I am a newbie in access, but I am not getting it.

    1) I create the form contacts.
    2) I add a subform using the "subform/subreport" button in the tool bar
    3) I use the junction table and add all fields

    What to do next?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by webgirly
    hahaaaaaa!!! OMG you're a GENIUS!!! I have been told combo boxes, and subforms and I've tried both, I never realised I needed a combo box within a subform!

    It's actually Working! Thank you, thank you, thank you!
    My pleasure.

    Quote Originally Posted by bluesand4
    Hi,

    I have the same problem.
    I am a newbie in access, but I am not getting it.

    1) I create the form contacts.
    2) I add a subform using the "subform/subreport" button in the tool bar
    3) I use the junction table and add all fields

    What to do next?
    Hi

    You need to create your two forms independently. You have the parent form. Then create another form based on the junction table (with the combo box too). Open your contact form in design view and insert the junction form as a sub form (you can just drag and drop if you like). All you need to do then is link the child and master fields in the properties of the subform control. This is just a rehashing of my earlier first post

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2005
    Posts
    7
    Thanks pootle flump

    every thing is working

  7. #7
    Join Date
    Dec 2005
    Posts
    17
    along the same lines, i have a slightly more complex setup with similar logistical problems. i do plan on doing my own study and research (i have 2 books coming in the mail), but figured i would pose a question here as well...

    i am developing a database to catalog advertising claims about specific products. i've attached my relationships chart to illustrate. i think i've created my join tables correctly and defined the relationships properly. each claim may be applicable in one or more regions and in one or more advertising mediums. each region and medium can be associate with many claims and each claim can be associated with many regions and mediums.

    what i want the main form for data entry and viewing/sorting/filtering to contain is some basic information about the advertising claim, and also a listbox allowing for multiple selection of regions and mediums. i'm assuming the listboxes are going to have to be in subforms linked to the regions and mediums lists. i've not been successful at getting this to work, but i'm still working on it...

    if i were to simplify things and use a lookup field as i'm doing with the products and claim type, i wouldn't be able to associate each claim with more than one region, correct? another solution i've thought of to simplify is to make a yes/no field for each region and claim and have checkboxes for each of them. that seems like a rather lo-tech solution and it will eat up a lot of screen-room on my forms...

    any other suggestions as to how to proceed? if not, no worries--just typing it out is helping the brainstorming process and making things clearer...
    Attached Thumbnails Attached Thumbnails relationships-12-19-05.gif  

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Andy

    Your requirement doesn't sound any different to webgirly - the same solution should suffice.
    If you want to use list boxes for data entry then you don't need to pop them in a subform - they would be unbound. You would have to select the relevent regions and then programmatically loop through the selected entries and insert them into the table. It is an attractive way to do things but somewhat more complex to set up

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2005
    Posts
    12
    I have a similar-ish problem again, I have tried to figure it out myself, I promise

    I'm trying now to create an 'opposite' form, so to speak.

    I want a form I can select the groups from, and have it show me a list of all the contacts that belong to that group. I don't want it editable though, or to change the table at all, just to display it all.

    I have tried to do it in the same way you told me for the contacts form, but the problems I am having with this form are:

    a) the results are editable, in this case, that's definately not something I can leave.
    b) the combo box will only display the first field's contents, and that's all. I'm not sure why.

    It would be lovely if I could pick the group I want to view from a drop down box, and then have the people in that group be displayed in a box below, but I'm not sure if that's possible.

    Can you help? Please?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by webgirly
    the problems I am having with this form are:

    a) the results are editable, in this case, that's definately not something I can leave.
    b) the combo box will only display the first field's contents, and that's all. I'm not sure why.
    a) Set the Allow Edits and Allow Additions properties of the form to false
    b) No reason why it should - remember that a combo box allows you to enter data into a field based on a list of possible data. As such, it cannot display more than one datum. You can have it display more fields in the list when it is dropped down though.
    You should alter your SQL Statement to put the field whose data you want to show to the user in the combo first. remember to change the Bound column property if necessary.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Dec 2005
    Posts
    12
    Thanks for that!

    My only problem is that I kinda need a couple of fields displayed... The idea being that when someone wants to see the members of a group they can see their contact detail and everything else etc.

    From this I might want to extend it to having buttons for mail merging and sending emails to the members of each group.... I don't know if that makes any difference at all?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by webgirly
    My only problem is that I kinda need a couple of fields displayed... The idea being that when someone wants to see the members of a group they can see their contact detail and everything else etc.
    I guess I misunderstood - I thought the combo was to filter for a group and then you would display the details of the group members in a subform\ listbox?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2005
    Posts
    12
    No, sorry, I meant, the way I tried was the reverse of the solution you gave to my last problem...

    I made a form, based on the groups table, then inserted a subform, based on the junction table, and inserted a combo box into that based on the contacts table. I had hoped I could tinker with it until it did exactly what I wanted, but I just dont have the skill.

    Ideally, what I'd like is a form with a drop down box that a user could select a group from, and have the full list of contacts in the group displayed somewhere else on the form showing the contacts details.

    I don't think I'm explaining this very well, sorry.

  14. #14
    Join Date
    Dec 2005
    Posts
    12
    Sorry, does anyone know how I can do this? I'm not sure if I explained very clearly.

    I have three tables, one containing contact details, one containing groups and a junction table to resolve the many to many relationship between the previous two. I have a form, in which I can enter/recall contact details which includes a subform that I can add/edit the groups that contact belongs in.

    What I need now is something of the reverse.

    I want a form, with preferrably, a drop down box allowing me to select each of the groups, one by one. When I select a group from this drop down box I want a box below to show all of the contacts that are a member of that group. I don't want the box to be editable, and I want it to show a number of different fields.

    (If it makes any difference, afterward, I plan to maybe insert tick boxes, say for instance email, postal, so that I can show those wanting to be contacted by email, and these wanting to be contacted by post, so from that filtered list, I can create a label mail merge, or a mass email)

    Is this at all possible or am I over-complicating matters? This is essentially the reason for the database mind you, so I am hoping it's a possibility...

    Thanks!

  15. #15
    Join Date
    Dec 2005
    Posts
    12
    Quote Originally Posted by pootle flump
    I guess I misunderstood - I thought the combo was to filter for a group and then you would display the details of the group members in a subform\ listbox?
    Sorry PootleF, I just re-read this, and I must have misunderstood what you meant the first time I replied. Yes, this is exactly what I want, I just need the results to show more than one field at a time... I'll need the postal and email addresses at least you see.

    Sorry

Posting Permissions

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