Pull down list box fills text box when selected

[Mark]

Hi,

On a form, I have a pull-down combo box with control
source: HospitalName, row source type: table/query, row
source: tableHospital, bounnd column: 1. I have a text
box with control source: HospitalPhone. Both,
HospitalName and HospitalPhone, are from the same table
(tableHospital).

How can I make it so that when the user selects a hospital
name from the pull down box... the hospital phone text box
is filled with the corresponding phone number of that
hospital name? And importantly, when the phone number is
filled... have the ability to edit the number if need be
and not be read-only field.

Thanks a bunch!!!!!

 

[John Vinson]

Hi,

On a form, I have a pull-down combo box with control
source: HospitalName, row source type: table/query, row
source: tableHospital, bounnd column: 1. I have a text
box with control source: HospitalPhone. Both,
HospitalName and HospitalPhone, are from the same table
(tableHospital).

How can I make it so that when the user selects a hospital
name from the pull down box... the hospital phone text box
is filled with the corresponding phone number of that
hospital name? And importantly, when the phone number is
filled... have the ability to edit the number if need be
and not be read-only field.

If you want to be able to store the phone number in your current
table, and have it different from the phone number in the hospital
table, you'll need a bit of VBA code to "push" the value into the
Phone textbox.

Let's say your combo is named cboHospital; that the phone number is
the fourth field in the row source; and that you have a bound textbox
txtPhone on the form. The code would be

Private Sub cboHospital_AfterUpdate()
If IsNull Me!txtPhone Then ' don't stomp on existing data
Me!txtPhone = cboHospital.Column(3) ' it's zero based
End If
End Sub

Leave out the If/End If lines if you *do* want to overwrite the phone
whenever you select a hospital from the combo box.

 

[Mark]

John, I just wanted to say thanks for the quick response
and great help!!!!!!!! Have a good one!