combo box craziness

[mikeinohio]

Hello, i have a form called employess which have the following fields:

PhysicianFullName, PhysicianIM, PhysicianAdd,PhysicianCity, PhysicianState,
PhysicianZip

I also have a table calle Physicians with the fields:

PhysicianFName,PhysicianLName, PhysicianTitle, PhysicianIM,
PhysicianAdd,PhysicianCity, PhysicianState, PhysicianZip

How can I create a combo box using the tblPhysicians tableto populate the
fields on the employees form?

 

[Allen Browne]

Why is this data duplicated across two tables?

Could you use just one table of people? Then add a check box to indicate if
the person is a physician. You can now create a combo that selects everyone,
or a combo that selects just the physicians.

If you need to track multiple roles for these people, create a little table
listing the various roles, i.e. one record for Physician, another record for
staff member, another for patient, etc. Now create a third table with
fields:
PersonID relates to the main table of people
RoleID relates to the little table of roles.
Now one person can have as many roles as you need.
(This is instead of the IsPhysician check box.)

 

[mikeinohio]

good point, the physicians are contracted by the company and every employye
is assigned at least a primary and a secondary. what i was trying to do is
since i already had a table that has 150 physician records in it, i just
wanted to be able to use a combo to pick assign one and then display thier
info, but not necessarily store it in the employees table, hope that cleared
up some things

 

[Klatuu]

You need a unique primary key for the physcians table you can use as a
foreign key in the employee table to relate the physician to the employee.
In this case, I would suggest adding an Autonumber field to the physician
table named PhysicianID.
Now add a field to the employee table with the same name as a Numeric Long
Integer data type.

On your employee form, create a subform control with the Source Object
property being a form that will contain the physician data. Use the
PhysicianID from each table in the Link Master and Link Child properties of
the subform control so the physician associated with the employee will show
in the form.

You will also want to add a combo box bound to the PhysicianID field to your
employee form to allow you to select a physician. Use the combo's After
Update event to requery the subform control so when you select a physcian, it
will populate with that physician's data.