list boxes

[cgmckenzie]

Could I please get some help, I have a drop down box with Customers listed. I have maybe 20 customers. The record source for the drop down list is a table with fields customers, address, city, state, phone and etc, The problem I have is I would like for when I select the particular customer in the form drop down box, the fields associated with the customer thier address, phone, city, state, and so forth automatically fill in the appropriate fields in the form.

 

[skwilliams]

For example, I have a table called Table1 and information in the fields txtName, txtAddress, and txtPhone. I also have a form with a combo box called cboName to select the name and need to autofill the address and phone number fields called Address and Phone . In the row source of the combo box I'd type:

SELECT txtName, txtAddress, txtPhone FROM Table1 ORDER BY txtName;

Set the number of columns to 3.

Go to the AfterUpdate--Select Event Procedure.

Within the procedure type the following:

Address=cboName.Column(1)
Phone=cboName.Column(2)

Now when you select the Name from cboName the Address and Phone fields will autofill from the corresponding table.

Let me know if you need any clarification.

 

[Autoeng]

See post http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=35378

 

[cgmckenzie]

skywilliams thanks for your help but it shows the address and state in the combo box with the customer I would like the city, state, and phone fields to automatically fill in thier appropriate field spots on the form something like this,

Customer Customer is the drop down box which is
Address a field on the form address,city
City State state, and phone are also feilds
Phone

When I pick the customer I want the address city and state fields to automatically fill in, maybe this is a clearer.

 

[skwilliams]

The additional fields do actually get pulled into the combo box as well, just in separate columns.

In your combo box properties, set your column width to the same viewable area as your customer field. The other fields will still get pulled just not displayed.

For example, if your combo box viewable area (without the dropdown button) is 2.0", then set the column width to 2.0". This will display just your customer field.

The SQL procedure pulls the corresponding data (address, city, state, phone) from the table based on the customer you select.

The VB code in the AfterUpdate procedure places the related data in their proper fields.

If this doesn't answer your question, feel free to post your DB.