Need form to auto. fill field based on selected name

tcullen

Board Regular
Joined
Jun 15, 2005
Messages
79
I have a form with a drop down box of names from a table. This table has a column of names, and a column of phone numbers. I need to figure out how, when a person selects a given name from the drop down box on the form, to auto populate the field next to it with the corresponding phone number for that person based off of the table the drop down box pulls from. Any help? Thanks
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Redo your Combo box. Select the Name and the Phone# ... in that order (for this example). When setting the size of the combo box, make the size for the phone# 0 (size it using the mouse pointer).

In the On Lost Focus or On Change Event for the combo box, enter the following:

Me!MyPhone#Field = Me!MyCombobox.Column(1)

The way this works is: Your combo box contains two fields ... Name and Phone#... but Phone# is hidden. Each column within the combo box is numbered, starting with 0. So, when you select a name, you're selecting Column(0) of the combo box. Your code would then enter what is in Column(1) of the combo box into your phone# field on the form.



Me! ... is simply referring to the active form
MyPhone#Field... is the name given to the field you want the phone# displayed in.
MyComboBox.Column(1)... points to the hidden phone# related to the username selected in the combo box.
 

tcullen

Board Regular
Joined
Jun 15, 2005
Messages
79
I'm having trouble figuring out what to enter as the code in the OnLost Focus field.

Me!MyPhone#Field = Me!MyCombobox.Column(1)

Maybe if I give you the name of my form, and fields, you can tell me how to write it...

Form name is : JobList_Tb
Name of Combo Box : Combo32
Name of wher eI want Phone number to be (can this just be a text box?):Text34


Thanks in advance!
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
I think I would enter the code in the On Change Event.. just personal preference.

Open your form in Design mode.
Right Click on Combo32... select Properties to display the Properties window.
Select the Events tab.
Click the mouse pointer way to the right of the On Change field.... you should see an elipse (...) Click on this and select Code Builder.

You should now be in a VB code window with two lines of code already entered for you... your cursor should be in between these two lines (blank line).

Enter Me!Text34 = Me!Combo32.Column(1)

Click on File/Close and Return to Access.
Test your form to see if the Phone# is displayed when you select a Name from the Combo box.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,650
Messages
5,660,120
Members
418,550
Latest member
kp_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top