Showing more than one column on a Form from a combo box

[Roxanne]

My original question asked how to show other columns in a combo box. What I
really need to know is: Is there a way to get more than one column from a
table to show up on a form?

I have a form based off of a trouble ticket table. In the form, I have a
combo box that is currently allowing a person to select the customer from a
drop down list of customer names. The list to this box is coming from
another table within the database named "customers" and the customers table
is linked to the trouble ticket table by customer ID. The customers table
also contains maintenance status and expiration date columns. What I want to
know is if there is a way for the maintenance status and expiration date to
show up on the form when a certain customer is selected. For example - if
customer ABC is selected from the drop down list of customer and their
maintenance status is current and expiration date is 1/1/2006, is there a way
to get the maintenance status and expiration date to show up on the form any
time someone picks customer ABC in the drop down list for customer?

Thanks for anyone who can help with this. The text below is what my
original question was and I asked if there was a way for the additional two
columns to show up without having to manually type the info in for each
customer.

John,

I have the maintenance status and expiration fields in the customer table -
I didn't put them in the trouble ticket table.

Good... just checking to be sure!

In the customer table, I have these two fields set as text boxes - should
they be set as something else? Also, you have "include the two fields in the
RowSource query for the customer combo box" and "use the columnwidths
property to set the width of these columns to zero." What does this mean
exactly? I don't have a combo box in my Customer table.

I'd recommend NEVER using combo boxes in Tables. I'm talking about a
combo box on the Form. See below...

Also, I understand adding two text boxes to my form, but don't understand
the (n) subscript of the field. I'm rather confused to the "combo's query"
you are referring to. Am I writing a query within the form?

The Row Source property of the combo box on the Form should be a Query
selecting the customer information. I'm suggesting that that query
include the two fields which you want to see. For example, the
RowSource property of the combo could be

SELECT CustomerID, CustomerName, Status, ExpirationDate
FROM Customers
ORDER BY CustomerName;

The Combo's ColumnCount property would be 4 to include all fields; its
ColumnWidths property would be

0;1.5;0;0

to display the customer name and hide all the other fields. The Bound
Column would be 1 so that the CustomerID is bound to your trouble
ticket's CustomerID field.

A Combo Box has a "Column" property which lets you extract information
other than the bound column's value from the combo. If your combo is
named cboCustomer, then simply look at the Control Source property of
the textbox on the form in which you wish to display the status, and
type in

=cboCustomer.Column(2)

This will automatically display the third (zero based, remember) field
from the combo - the status, using the example above.

John W. Vinson[MVP]

 

[SAm]

roxanne,

the answer given to you seems to be the correct one, however it may be too
advanced for you.

do this:

create the form, on the form have one control that you call "EmployeeName"
covert this to a combo box (right click on the control and select "change to"
and select combo box). then in the properties you need to select where you
want to these employee names to come from. you do this in Data | Row Source.
you can click on the right side to get the query builder. you need to select
more than one column. the first column should contain the employee name, the
next one should contain the employee ID.

you now have a pull down menu that will allow the user the select employee
names. but you want to hide the employee ID from showing on the combo box.
the solution is, go to the properties of the combo box and under the format
tab you will see column widths. if you type ;0 then you will have the first
column showing the default width, and the second column will not be showing
since you set it to 0. you can also set like 2;0 this means you will have two
inches for the first column, the best would be to keep it at the default.

you can now reference the column(1) that john was talking about.

good luck,

sam

 

[Joan Wild]

John has given you a method to display the other fields in separate
textboxes
Create a textbox and set it's Control Source to
=cboCustomer.Column(2)
and another to
=cboCustomer.Column(3)
That will display the Status and ExpirationDate in the textboxes.

However there is another option. change the RowSource of your combo to

SELECT CustomerID, CustomerName & " " & Status & " " & ExpirationDate
FROM Customers
ORDER BY CustomerName;


Then change the properties of the combobox to
Column Count - 2
Column Widths - 0";3" (adjust to suit)
Bound Column - 1