Auto fill a form

[Joanne]

I have a form. On one control there is a drop down list
that selects 2 values from one table. Let's call
them "value1" and "value2". After I select "value1" I
want "value2" also from the same table to be updated.

How can I do this?

 

[Kevin Sprinkel]

Not sure what you mean by "updating" the second value on
selecting the first, but for the time being I'll assume
you mean to have it display on your form.

This is a frequent situation and raises several issues:

- the drop-down list actually "selects" only one value;
the value referred to in the Bound Column property of the
combo or list box. If you display, say, the CustomerID
and the Customer Name, e.g. in your combo box, and the
Bound Column is 1, then the customer ID of the row
selected is entered into the control and stored in the
field specified in the Control Source property.

- If you wish to *display* columns other than the Bound
Column for your user's convenience, use a call to the
Column property, e.g., to store the 2nd column of the
combo box named cbxCustomer in a textbox, set the textbox'
Control Source to:

=Me!cbxCustomer.Column(1)

'1' because Column the first column is zero, 2nd 1, etc.

- If you're trying to store this second value in another
field table, this is generally not recommended because
it's redundant and wastes disk space. Exceptions include
time-dependent values such as the cost of a product at the
time of an order.

HTH
Kevin Sprinkel

 

[Joanne]

After reading this it sound a little confusing. Maybe I
shoould have said this.
I want value2 to be filled automatically when I select
value1 from a drop down list. value1 and value2 are in the
same table.

 

[Joanne]

Thanks for your help Kevin.
Here is what this form does. I need to type in names that
I amrequesting travel for. I have a table that has
information on city/state and the closest airport code.
When I open the form, I add the name and select a select
the closest major city/state from the table. I want the
aiport code to be filled in after I select city and state.
This information is then exported to an excel spreadsheet
to be sent to our booking agent.
Thank again.

 

[Kevin Sprinkel]

OK. I think the best way to do this is to create a query
including the name and the city/state fields joined with
the table that contains the city/state and airport code by
the city/state field (or its primary key). Then export
the query to Excel.

To also display the airport code on the form as you enter
the city/state, use the Column property as I suggested.
For example, let's presume your Code table has three
fields--a numeric primary key, the City/State and the
Airport Code.

The Combo Box would have settings similar to the following:

Property Value
================== =====================================
Row Source Select ID, CityState, AirportCode From
AirportCodes
Bound Column 1
Column Widths 0";2";.5"

When you pick on the drop-down list, the City/State and
the AirportCode will display. When you select a row, the
ID will be stored in your table, but the 1st visible
column (the CityState) will display in the combo box.

To display the AirportCode in another control, refer to
its column number, in this case 2 since the numbering
begins with zero.

The control's Control Source property would be

=Me!YourComboBoxName.Column(2)

Does that clarify?

Kevin Sprinkel