Displaying Additional Columns in Access Form

[Roxanne]

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 display (in 2 additional boxes on the form) the
word "current" and the date "1/1/2006" any time someone picks customer ABC
from the drop down list for customer when entering a trouble ticket?

The technicians do not know the maintenance status and expiration of the
customers and I need those two columns from the Customer table to populate
into seperate boxes under the drop down list for Customers. This way, any
time they enter a trouble ticket and selecting a customer, those two boxes
will populate the maintenance status of the customer so the tech knows.

I had one reply before (listed below) and when I followed those coding
instructions, it showed all 3 columns (company name, maint, expiration) all
in the drop down list for the Customer, but when you selected a certain
customer, the only thing visible was the company name.

I just want to get the maintenance and expiration to populate into 2 other
boxes when a certain company is selected from the drop down menu. Is this
possible and if so, how do I accomplish it?

ORIGINAL ANSWER:
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]

 

[Wayne Morgan]

There are a couple of ways. The easiest is probably to add the other two
fields as additional columns to the combo box. Set the width of those two
fields to zero so that they don't show in the combo box. Add two textboxes
to the form and set their Control Sources to point to these other two
columns.

Example:
=cboMyCombo.Column(2)

This would give you the value in the third column of the combo box. The
index number is zero based, so 2 is the third column, 3 is the fourth, etc.

 

[Roxanne]

Wayne,

Right now I have the following on the Form:

My combo box has a name of 'Customers' and shows the following for the Row
Source:
SELECT Customers.CustomersID, Customers.CompanyName,
Customers.MaintenanceStatus, Customers.Expiration FROM Customers ORDER BY
CompanyName;

I have made the column count 4 and when I put in widths of 0";1.975;1.5";1.5
- the combo box itself will show the customer selected but I can see the
other two columns when going to select a customer.

Now, taking the widths back down to 0';1.975";0";0", I return to just seeing
the customer name in the drop down list (so all is ok at this point).

I then added one text box and attempted to set the Control Source to column
2 as you described below, but I get an error that the expression is not
right. When looking at the form in "view" the text box shows "Name?" and it
doesn't populate the maintenance status from the customers table.

A couple questions I have are
(1)I am using Access 2003 - is the name of my combo box "Customers" - any
time I input cbo like you show below nothing works.
(2)Is there something else I need to be setting in the properties of the
text box so that the field will populate once a certain customer is picked in
my drop down list? (3) Is there a way to just get all 3 columns to show up
on the form from the combo box itself? When I adjust out the widths of the
other 2 columns in the properties and make the actual box as wide as needed
to show all the columns, only the customer name appears in the box (even
though when you select the drop down arrow, you can scroll over and see the
other columns and their values for each customer)?

Thanks,
Roxnne

 

[Wayne Morgan]

1) The name is of the combo box is on the Other tab of the Properties sheet
when you have the form open in design mode and the combo box selected. It
should also be what shows in the drop down of the Properties sheet. You will
need to change the name I used to the correct name for your combo box. If
the combo box and the field it is bound to (Control Source of the combo box)
have the same name, you may have a problem. If so, change the name of the
combo box by putting cbo in front of it. You will also need to change the
name of any procedures in the form's code module that may use the old name
and in any queries or other forms or reports that may have referred to the
combo box.

2) As long as the textbox and combo box are on the same form, the textbox
will update automatically as the combo box changes. If it is a parent
form/subform setup and one is on the parent and the other on the subform
then something else will need to be done.

3) Yes, you could change the Row Source of the combo box to have it
concatenate the desired fields together into one field. They will then show
up in the combo box itself after a selection has been made.

Example:
SELECT Customers.CustomersID, Customers.CompanyName & ", " &
Customers.MaintenanceStatus & ", " & Customers.Expiration FROM Customers
ORDER BY CompanyName;

If you do this, the combo box will only be a 2 column box.