Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Cascading Combo Boxes if on Different Forms

    My database is housing a set of treaties. On the main Treaty Form, there is a continous subform to show what affiliate companies are participating in it. Each affiliate company has contacts, so since I can't put a continous subform within another continous subform, I opted for a pop-up form.

    So after someone chooses a company, picks how much of the treaty they took on, they can click the add contacts button for a form to pop up where they can enter in as many contacts as they want.

    What I want to happen is that when the pop up form comes up, the only contacts listed are those associated with that particular affiliate company. So if I choose Company XYZ and then click "Add Contacts", the only options I have are those contacts who are with Company XYZ.

    I also don't want the user to be able to pick the same contact more than once --

    Is it possible to base combo boxes off one another if they are in different forms? I have found examples online, but they are all in the same form.

    I've attached a sample DB with what the structure of mine is like.
    Thanks in advance for any help!
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have a look at the attachment. I needed to make some changes to your tables and to the relationships.

    I have used a unique index to prevent the same contact being added twice. This is not the most elegant solution as you only get told you can't after you try. If the new table structure and relationships are correct I can show you a better option.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi John,

    Sorry for the delayed response but I do not have access to Access on the weekend and have not been able to look at your response till today.

    The new relationship model you set up makes sense, I just added another relationship between the Affiliate and the Affilate Contacts.

    I think the issue that I'm having is how to reference a control object in a different form. My actual database has tables/forms/controls named differently than the one I uploaded, so I'm confused about the RowSource on the control named contactID on the Junction-ContactTreaty Form.
    The where clause is
    WHERE (((AffiliateContacts.AffiliateID)=[Forms]![Treaty]![Junction].[Form]![ComboAffiliate]))
    but I don't exactly get what its referring to after the = sign.

    I changed the relationships in my actual database to match to the one you edited but can't seem to get it to work, and I'm pretty sure the issue I'm having is in that WHERE clause.

    As for the more elegant solution on not allowing the same contact to be added twice, I'd love to know what idea you have because I'm trying to make the database as efficient as possible and not just toss it together.

    Thanks!

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by tinghting View Post
    Hi John,

    I think the issue that I'm having is how to reference a control object in a different form. My actual database has tables/forms/controls named differently than the one I uploaded, so I'm confused about the RowSource on the control named contactID on the Junction-ContactTreaty Form.
    The where clause is
    WHERE (((AffiliateContacts.AffiliateID)=[Forms]![Treaty]![Junction].[Form]![ComboAffiliate]))
    but I don't exactly get what its referring to after the = sign.
    [Forms] refers to the collection of currently open forms.
    [Treaty] is the name of the form being referred to.

    But the control we are interested in is actually on a subform, and subforms are displayed within a Subform Control.
    [Junction] is the name of the subform control.
    [Form] says we are interested in the form within that subform control.
    [ComboAffiliate] is the name of the control.

    The way to write such statements (unless you have a lot of experience at writing them) is to use the Expression Builder.
    ON the Criteria Line of the query, right click and choose Build, then navigate to what you are looking for.
    Expressionbuilder7.gif
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Restricting Selection so a Contact cannot be chosen twice

    Here is another example, where the choice of Contacts is restricted so a contact is not offered if it has already been used.
    The query behind the combo is more complicated because it needs to use a subquery.
    Also if a user clicks the combo next to a name that has previously been entered, you should see the name already present.

    A side effect of doing this is that the names disappear from combo boxes when you move to a different record. So the form has a text box sitting in front of the combo. The text box displays the name for previously entered people, and the combo is just used for entering new names.

    I have also move the popup form back to the Treaty form, so the two subforms sit side by side as linked subforms.
    Attached Files Attached Files
    Regards
    John



  6. #6
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you John, after your explanation I was able to name things properly in my database and the correct contacts are showing up!

    However, in my database, when I add contacts it says that "You cannot add or cha nge a record because a related record is required in table."

    I'm not sure why this is as my relationship set up is the same as in the sample database.

  7. #7
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi John,

    I am able to get the appropiate contacts to show up, however, it doesn't save into into my Junction table, and everytime I open the pop up form again there are no contacts available there to see. I think the issue is that data from the subform isn't going into the pop-up form but I am not sure how to pass this information along.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When a contact record is added via the popup form you need the AffiliateTreaty (or AffiliateTreatyID) value entered for the record. I did that by having a greyed copy of the field (normally I would hide it) with a default value.
    With forms and subforms, you can do that via the LinkMaster and LinkChild properites of the subform.
    Regards
    John



Posting Permissions

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