Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2010
    Posts
    5

    Unanswered: Help with Combo Boxes

    Please Help

    I am new to MS Access 2007.

    I have been through numerous tutorials - not none tells me exactly how to accomplish this.

    I have 3 Tables

    Table 1:
    Contact ID (PK)
    Contact Name

    Table 2:
    Company ID (PK)
    Company Name

    Table 3:
    Order ID (PK)
    Orders
    Company ID
    Contact ID

    I have set up One-To-Many Relationships from Tables 1 and 2 TO Table 3

    What I am trying to accomplish:

    I want to make a Form with 2 Drop Down Menus: one for the Company and One for the Contacts

    I want the Drop-Down Menu for the Contacts to to be Filtered by Only Those who work for the Company That has already been selected.

    Please let me know how to do this.

    Many Thanks - kessel_m

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    If you want to do this, you have to have a FOREIGN KEY in th "Contact" table, and a Relationships between the "Company" table and the "Contact" table.

  3. #3
    Join Date
    Dec 2010
    Posts
    5
    I do have a One-to-Many relationship set up between the Company and Contact Table.

    What should I do next? The closest example I was able to find online had everything in one table - so I could not follow it exactly.

    Any help is appreciated!

  4. #4
    Join Date
    Dec 2010
    Posts
    5

    side bersever

    I do have a One-to-Many relationship set up between the Company and Contact Table.

    What should I do next? The closest example I was able to find online had everything in one table - so I could not follow it exactly.

    Any help is appreciated!

  5. #5
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Via which fields you have got a Relationships between "Company" and "Contact" tables ???
    (Table1 and Table2).

  6. #6
    Join Date
    Dec 2010
    Posts
    5
    In my Company Table I have 2 fields

    tblCompany
    1. companyID (PK)
    2. companyName



    In Contact Table I have 2 fields

    tblContact
    1. fullName
    2. companyID


    I have a 1-to-Many Relationship between the companyID in both Tables.

    What do you think?

  7. #7
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    What you need are called "Cascading combo boxes"

    Basically: for your company combo be sure to include the company id

    then when SomeCompany is selected from that combobox use that company id to filter your contacts combo. I generally use the AfterUpdate event of the primary combo box.

    It'll look something like this:

    Code:
    Private Sub Form_Load()
    
    	cbo_Company.RowSource = "SELECT CompanyID, Company_Name " _
    		& "FROM tbl_Company " _
    		& "ORDER BY Company_Name;"
    
    End Sub
    
    Private Sub cbo_Company_AfterUpdate()
    
    	If Not isNull(cbo_Company) Then
    		cbo_Contact.RowSource = "SELECT Contact_ID, Contact_Name " _
    			& "FROM tbl_Contact WHERE Company_ID = " &clng(cbo_Company.Column(0)) _
    			& " ORDER BY Contact_Name;"
    	End If
    
    End Sub
    You'll need to use your table/field names, but, the code above should get you in the right direction. As usual I'm assuming your ID field is a Long Integer (autonumber). If it's not you'll need to modify the cbo_Contact rowsource string to format the ID properly.

    Also, you'll notice the space between the leading quotes and ORDER in the contact rowsource...very important, otherwise Access will concatenate your ID with the word ORDER...which doesn't work...no matter how many times I attempt it<g>

    Sam, hth
    Good, fast, cheap...Pick 2.

  8. #8
    Join Date
    Dec 2010
    Posts
    5
    It worked!
    Thank You Soo Much!
    You Rock!

  9. #9
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    LoL no problem, glad to help.

    Sam, at least somebody's happy on a LetsHaveEveryThingGoToesUpAtOnceDay...err I mean Monday<g>
    Good, fast, cheap...Pick 2.

Tags for this Thread

Posting Permissions

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