Help with Combo Boxes

kessel_m · #1 (**permalink**) 12-15-10, 02:17

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

MStef-ZG · #2 (**permalink**) 12-15-10, 02:50

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.

kessel_m · #3 (**permalink**) 12-15-10, 10:40

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!

kessel_m · #4 (**permalink**) 12-15-10, 10:40

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!

MStef-ZG · #5 (**permalink**) 12-16-10, 02:40

Via which fields you have got a Relationships between "Company" and "Contact" tables ???
(Table1 and Table2).

kessel_m · #6 (**permalink**) 12-28-10, 11:07

In my Company Table I have 2 fields

tblCompany

companyID (PK)
companyName

In Contact Table I have 2 fields

tblContact

fullName
companyID

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

What do you think?

SCrandall · #7 (**permalink**) 12-28-10, 15:18

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