Synchronize Form Fields

[Guest]

I've been struggling with how to automatically fill in a form field based on
a selected elsewhere on the form. However, the information comes from an
external table not connected to the form.

Here's what I have:
- A table, with a list of vehicles and their respective capacity.
- A table/form that will be used to enter data, such as what vehicle an
employee used that day.

I would like it if, when the user entered what vehicle they used that day on
the form, if another field on the form was automatically filled with the
capacity of the vehicle they chose. Thanks, I appreciate any help you can
give.

 

[Steve Schapel]

Evan,

There are several commonly used approaches to this....

1. Probably the easiest is to base your form on a query that includes
both tables, joined on the vehicle field from both. That way, you have
the capacity field from the Vehicles table available in the query, and
hence can be directly represented on the form. When the user enters the
vehicle, the capacity will automatically be shown. In this case,
though, you might want to set the Locked property of the Capacity
control on the form to Yes, as this is not a correct place to allow this
to be edited.

2. Put an unbound textbox on the form, and in its Control Source
property put the equivalent of...
=DLookup("[Capacity]","Vehicles","[Vehicle]='" & [Vehicle] & "'")

3. If you are using a combobox on the form to enter the Vehicle, and
the Row Source of this combobox is the Vehicles table, make it a
multi-column combobox, so the Row Source includes the Capacity column,
and then you can put an unbound textbox on the form, and set its Control
Source to the equivalent of...
=[Vehicle].[Column](2)
(the colimn numbering starts from 0, so the (2) in my example would
imply the 3rd column in the combobox's Row Source.

 

[Guest]

Thanks much.

Evan,

There are several commonly used approaches to this....

1. Probably the easiest is to base your form on a query that includes
both tables, joined on the vehicle field from both. That way, you have
the capacity field from the Vehicles table available in the query, and
hence can be directly represented on the form. When the user enters the
vehicle, the capacity will automatically be shown. In this case,
though, you might want to set the Locked property of the Capacity
control on the form to Yes, as this is not a correct place to allow this
to be edited.

2. Put an unbound textbox on the form, and in its Control Source
property put the equivalent of...
=DLookup("[Capacity]","Vehicles","[Vehicle]='" & [Vehicle] & "'")

3. If you are using a combobox on the form to enter the Vehicle, and
the Row Source of this combobox is the Vehicles table, make it a
multi-column combobox, so the Row Source includes the Capacity column,
and then you can put an unbound textbox on the form, and set its Control
Source to the equivalent of...
=[Vehicle].[Column](2)
(the colimn numbering starts from 0, so the (2) in my example would
imply the 3rd column in the combobox's Row Source.

--
Steve Schapel, Microsoft Access MVP

I've been struggling with how to automatically fill in a form field based on
a selected elsewhere on the form. However, the information comes from an
external table not connected to the form.

Here's what I have:
- A table, with a list of vehicles and their respective capacity.
- A table/form that will be used to enter data, such as what vehicle an
employee used that day.

I would like it if, when the user entered what vehicle they used that day on
the form, if another field on the form was automatically filled with the
capacity of the vehicle they chose. Thanks, I appreciate any help you can
give.