Autofilling a field based on an entry in a separate field

[jmitchelldueck]

Hi,

I am creating a database tracking physicians and their contracts. I currently have two tables: PhysicianT and ContractsT, with corresponding forms to enter information in them.

I have an issue with the Contracts form; I want to be able to select a physician from a dropdown list (looked up from PhysicianT) and have Access autofill their Physician ID #.

PhysiciansT looks like this:

physicianID (AutoNum) name (Calculated)
1 Barker, Bob
2 Burgundy, Ron
3 Stark, Tony

Upon selecting Barker, Bob from my dropdown list, I want "1" to appear in the Physician ID # field in my Contracts form.

I am looking for a way to do this without VBA code please!



Thank you for your help.

[June7]

No code needed. Use a multi-column combobox. Review: http://datapigtechnologies.com/flash...combobox3.html

[jmitchelldueck]

No code needed. Use a multi-column combobox. Review: http://datapigtechnologies.com/flash...combobox3.html

How do I get the Physician ID # from my form back in to my table? I'm using the combo box for the Physician Name and a text box with control source = [combo box] as per the video you linked me to. Is the control source line in the property sheet the only way to link tables and forms together?

Thanks.

[June7]

The combobox should be bound to the field where you want to save the PhysicianID. Yes, the ControlSource property connects to field in the form's RecordSource. This will commit data to table.

Should not need the textbox. A ControlSource with = sign is a calculated value. Calculated values are not automatically saved to record. The textbox is only if you want to display other info from the combobox item.