Access 2007 - Form Combo Box

[losttrail]

New here, please be gentle. I haven't used Access since 1998, but now have to jump in to help my wife.



Access 2007

Have a DB with multiple tables; one table is Customer Contacts. Multiple companies with multple employees at each company.

We are setting up a scheduling form and I have a Combo Box for "Facility" that grabs the company name, no problem.

I have another Combo Box for the employee.

What I am hoping to do is:

Once the Facility is selected, the Employee Combo Box will then list only the employees associated with that company.

I have spent several hours trying query's, lookup columns, etc., but can't get this figured out.

Thanks for any help.

[RuralGuy]

That is what we call Cascading ComboBoxes. Here's a start: http://www.fontstuff.com/access/acctut10.htm

[losttrail]

Thanks. Hope much hasn't changed since Access 2000.

Never have done any Access programming.

[RuralGuy]

The interface is completely different since 2007. There are pleanty of helpers on this site to get you through the tough spots. Just post a specific issue.

[losttrail]

I see you're at 8,300' in the Colorado Rockies.

We're only at 7,400' in the Colorado Rockies.

We must be neighbors. Maybe you're in Woodland Park?

[RuralGuy]

Nope! Outside of Pagosa Springs.

[losttrail]

Ahh. Monument here. Just north of Colorado Springs.

[RuralGuy]

I'm a West Slope boy.

[losttrail]

Cool. I'm originally a Montana boy. Bitterroot Valley.

[RuralGuy]

Transplant from California 20 years ago myself.

[RuralGuy]

Love Whitefish MT.

[losttrail]

Ok, I can't get anything to work on the cascading comb boxes.

I have created a query that feeds a list box with data from 5 columns in a table: Last Name, First Name, Account Name, City, State.

From this I need to have another box (combo?) filled in with the appropriate Account Name once the proper selection is made.

I have need of more of these scenarios, but I feel like if I can get the first one to work, then the others will be easier.

HELP!

table: Customer Contacts
query: qryAccountName
list box: AccountName

BTW: I'm not a SQL programmer at all. I haven't done any programming for 20+ years and that was COBOL, RPG II, OS/JCL, Assembler.

[RuralGuy]

List Boxes work differently than ComboBoxes. I do not really understand what you want yet.

[losttrail]

Sorry to be so confusing. This thing is a DB in evolution. I'll try to clear things up.

I have a form (frmScheduling) that gathers data from a table (Customer Contacts). Throughout the form there are fields that need to display data. As much as possible, we are looking for the ability to have fields 'auto-populate', based upon a selection made in a previous field.

So....

In the Physician box, I it to display Last_Name, First_Name, Account_Name, Mailing_City, Mailing_State.

Once the appropriate physician is selected, I need other boxes to auto-populate based on that selection, to fill out Facility, City, State.

To this point, I can get the Physician box to display the 5 columns of data, either by using a List Box or Combo Box. I gather the data using qryPhysician for the 5 columns of data displayed here.

However, if I use the List Box, I continually see however many rows of data I set to display in Format.

If I use the Combo Box, I see all 5 columns of data when I pick the selection arrow, but once I make the selection, all I see is the last name of the physician.

Lastly, I cannot figure out how to get the data selected from the Physician list or combo to populate the other boxes.

Again, there are going to be other places that I will need to do the same or similar actions, and I think that once I understand this I can use the basic format to work those other areas.

I just can't get this first one.

Hope this helps.

[RuralGuy]

Assuming your Physician table has a PrimatyKey and the RecordSource of your form is a query that joins the Physician table with another table then the ComboBox wizard can do all of this work for you. All of the fields of the Physician table and this other table can be displayed on your form as if they were in the same table.