Expert: Scottgem - 2/7/2005

Question
Scott:
I use a combo box to look up physician name and address to add to my patient file.  Each physician has a unique number that I use for relationships. However, when I index this number as a primary key the combo box no longer lists the physicians alphabetically (important because I have duplicate last names) Your thoughts?
Thanks
bob

Answer
First, the ONLY thing that should be in the patients table is the Physician ID. You can pull name and address using joins. This is also assuming that a patient will only have ONE physician relationship. If they can have more, then you need to use a join table.

The combo should have a RowSource like the following:

SELECT PhysicianID, PhysicianName FROM tblPhysicians ORDER BY PhysicianName;

You set indexes on the table level, but you can have an Order By clause in a query. Note, I left off the address. If you need the address to further identify a physician you can add it as a third column. In addition, the column widths property of the combo should be set so the first column is 0" (ex: 0";2"). This will surpress the display of the ID and display the name. Finally, if you also want to display the physician's address on the patient form, you can do so by adding the address column to the query and then adding an unbound textbox to your form and setting the data source to:

=cboPhysician.Column(2)

This will display the contents of the 3rd column (column count starts at 0) in the control assuming the combo is named cboPhysician.

HTH
Scott<>