Access 2007 - Form Combo Box

[losttrail]

Thanks, but no.

The only table I'm drawing from is Customer Contacts. There are many more fields in this table, but I only need these 5.

Right now the cboPhysician draws the 5 fields of data from qryPhysician. When I click on the arrow for the cboPhysician, I see all 5 columns of data. However, once I select a physician, then I only see the last name.



I did get some semblence of what I'm working towards by:

The boxes for Facility, City, State, I created as text boxes: txtFacility, txtCity, txtState.

In Design View, I gave each text box a 'Criteria' of:

=[cboPhysician].[Column](2) (for Facility)

=[cboPhysician].[Column](3) (for City)

=[cboPhysician].[Column](4) (for State)

This returns the proper Facility, City and State in each text box, except that it only selects the first record for each Facility. Even if there are 20 physicians for a facility and I select the 10, 15, 18, etc., it only displays the first record.

[RuralGuy]

What is the relationship between [Customer Contacts] and [Physicians]?

[losttrail]

Physicians is from two fields in the Customer Contact table; Last_Name and First_Name

[RuralGuy]

Post #17 asks if there is there only one Physician per contact or many?

[losttrail]

Many. Each facility can have multiple physicians.

[RuralGuy]

So why does your table only have fields for one Physician's name?

[losttrail]

Hopefully I can get a screen capture attached here.

Customer Contacts is the table I am drawing my data from. It has many columns but for the form frmScheduling, I only need data from 5 columns. As you can see, each Facility (Account Name) has multiple physicians.

frmScheduling is the form I am trying to populate.

frmScheduling-DV is the Design View of the form.

I hope this helps and thanks.

[RuralGuy]

So you have a separate record for each physician in a facility?

[losttrail]

Apparently. I have inherited all this existing DB and am sure that there is a more logical way to approach this.

Maybe the existing Customer Contacts table needs to be revamped into multiple tables?

It's been so long (1998) since I've done anything with Access, and even then it wasn't a lot.

[RuralGuy]

Normally a 1:many relationship is represented in Access with a MainForm/SubForm arrangement with the MainForm in single view mode and representing the 1 side of the relationship and the SubForm in Continuous Form mode representing the many side of the relationship. That requires at least two tables with the PrimaryKey of the 1 side table in the many side table as a ForeignKey field.

[losttrail]

At this point the Customer Contacts tables has no relationships.

[RuralGuy]

There is a 1:m relationship between a Facility and the Physicians.