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

    Activating a subform with a value from a combo box

    I am currently making a database to store information about different contracts, each contract is with multiple associate companies.

    So in the main form, there is a subform that has a drop down list of our associate companies and a field to input the percentage of the contract they have taken on. However, one of our associate companies, Company X, is more of an overall name for many different companies. When Company X is chosen, I want a subform to show up that has a list of all the companies under Company X and a field for % of contract taken.

    My issue is that I do not know how to make a subform appear for when Company X is chosen. I have been self-teaching myself access, so if you could explain in layman's terms that would be great!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can make things appear or disappear by manipulating their visible property.

    So me.frmAssociatedcompanies.visible=false will hide a subform of the current form whose name is frmAssociatedCompanies.

    You need to test the value of the first combo box

    Code:
    If me.ComboCompany=1 then
        me.frmAssociatedcompanies.visible=true
    else
       me.frmAssociatedcompanies.visible=false
    
    end if
    This assumes that companies are actually identified by a number (Company Id) and that for company X this Id is 1.
    If you actually use the name as the Identifier, use this.

    Code:
    If me.ComboCompany="Company X" then
       me.frmAssociatedcompanies.visible=true
    else
       me.frmAssociatedcompanies.visible=false
    
    end if
    You need to put code in two places:


    • the After Update event of the combo where you choose the first company, and
    • the On Current Event of the form. This is so that the subform form is visible/hidden when looking at existing records.

    If you have difficulty implementing this, can you post a cut down version of your database, so we can provide more specific help.
    Regards
    John



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

    Couldn't get the code to work

    Hi, I have made a database with a similar structure to the one I'm having a problem with. It has a Contracts, AssociateCompanies, and CompanyXAffiliates table along with two junction tables. I started making the form to input new contracts, but I left it at that. I didn't want to upload my actual database because that has a lot of other tables that I'm not having any issues with.

    Once again, the problem is that when I choose Company X from the drop down list, I want a subform to come up with Company X affiliates.

    Thank you for helping!
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Access does not allow you to have a continuous subform within a continuous subform. You can put one in the header or footer of the subform, and it relates to just the current record of the first subform.

    In the attached ContactCompanies uses that approach and hides/shows the subform.

    ContractCompaniesV2 uses a different structure, and uses conditional formatting to enable/disable a second field, instead of using another subform.

    See what you think of the two options.
    Attached Files Attached Files
    Regards
    John



  5. #5
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thank You

    The conditional formatting way was very helpful! Thank you so much!

Posting Permissions

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