Combo Box populating 2 text boxes automatically

[John Torres]

I have a tblSupplierInformation which consist of SupplierID, ContactName, PhoneNumber among other name fields.

I’m creating a combo box from the tblSupplierInformation to a form “Purchase Order Entry”. I want to create 2 text box to be populated with ContactName, PhoneNumber from that table when a supplier name is selected. How do I do that?

Access2000
Vista

Thanks,
John

[Stewart Ross]

I have a tblSupplierInformation which consist of SupplierID, ContactName, PhoneNumber among other name fields.

I’m creating a combo box from the tblSupplierInformation to a form “Purchase Order Entry”. I want to create 2 text box to be populated with ContactName, PhoneNumber from that table when a supplier name is selected. How do I do that?

Access2000
Vista

Thanks,
John

Hi John. Include in your combo's recordsource query the supplier ID, the supplier name, the contact name, and contact phone number. Your combo is presumably bound to the supplier ID field in your purchase order table.

With a small amount of VBA code in the After Update event of your combo control you can set the values of the contact name and phone number controls when the user makes a selection from the list, like this:

Me![ContactName] = Me![name of your combo].Column(2)
Me![PhoneNumber] = Me![name of your combo].Column(3)

Combo columns are numbered from 0. The first column is column(0), the second column (1) and so on. If you order the combo columns differently, or alter the number of fields you are displaying, you will need to change the column references accordingly.

Hope this helps.

-Stewart

[mgstlucia]

I am trying to do the same type of thing. Do I need to change Me! to a form name or query name?

[Stewart Ross]

I am trying to do the same type of thing. Do I need to change Me! to a form name or query name?

Hi. The 'Me!' part is Access shorthand in a form or report's code module for referring to the associated form or report without explicitly saying so. If you need to refer to controls on a different form or report, either to copy from them or to them, then you need to be explicit about the reference using one of several ways to refer to controls, such as

forms!formname!controlname 
forms("formname").controls("controlname") 
 

etc. Of course, you will need to make sure that the form you are referring to is open at the time.

The syntax for referring to a control on a subform is different:

me!subformname.form!controlname
forms!formname!subformname.form!controlname 

-Stewart

[mgstlucia]

Thanks. I got it to work.

[John Torres]

Mine too!!! THANK YOU VERY MUCH.

Thanks. I got it to work.