How Do I Use a Combo Box to Update Two Form Fields at the Same Tim

Currrently I am using the same combo box select query in a form and varying
the bound colums to pull information out of one database to enter it into two
separate fields in another database. Is it possible to use a select query
for a form that will fill in the current field with one value and
automatically fill in the second field with its companion value. Example:
"part number 12345" and part name "gold widget". I would like to run the
select query on the part number and have the part number added to the part
number field and without running the query a second time having the part name
automatically added to the part name field. The part name field is the next
field on the form. Access 2003.

JBledsoe,

If you mean the form is based on a table which includes 2 fields where
there is data in another table, then it is almost certain that this is
not a good table design. There should be only one field in the second
table to identify the related data from the first table - this is one of
the principles of relational database design. If you have part number
12345, you will always know that this refers to gold widget by reference
to the first table, and it is invalid to redundantly re-store the part
name in the second table.

If, on the other hand, you want to enter the key data into the second
table via the form, and then have the related data from the first table
*displayed* on the form, this is another matter. There are a number of
approaches to this. Some of them are discussed in this article...
http://accesstips.datamanagementsolu...biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP

JBledsoe wrote:
Currrently I am using the same combo box select query in a form and varying
the bound colums to pull information out of one database to enter it into two
separate fields in another database. Is it possible to use a select query
for a form that will fill in the current field with one value and
automatically fill in the second field with its companion value. Example:
"part number 12345" and part name "gold widget". I would like to run the
select query on the part number and have the part number added to the part
number field and without running the query a second time having the part name
automatically added to the part name field. The part name field is the next
field on the form. Access 2003.

The support table lists many parts (records) by listing each part number in
one field and part name in the next field of the same record. I want to
combo list the records in this support table via a select query to enable me
to enter the record information (part number and part name) into two fields
of a second table (with the same names as the support table uses) at the same
time rather than having to do it separately for each field. I hope my
nomenclature is consistent here -- I know what I want to and hope I am
expressing it clearly. The end goal is to be able to have the buyer with
part numbers and part names all in the same record of the second table and to
reduce the part associated key strokes by one half.
--
JBledsoe


"Steve Schapel" wrote:

JBledsoe,

If you mean the form is based on a table which includes 2 fields where
there is data in another table, then it is almost certain that this is
not a good table design. There should be only one field in the second
table to identify the related data from the first table - this is one of
the principles of relational database design. If you have part number
12345, you will always know that this refers to gold widget by reference
to the first table, and it is invalid to redundantly re-store the part
name in the second table.

If, on the other hand, you want to enter the key data into the second
table via the form, and then have the related data from the first table
*displayed* on the form, this is another matter. There are a number of
approaches to this. Some of them are discussed in this article...
http://accesstips.datamanagementsolu...biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP

JBledsoe wrote:
Currrently I am using the same combo box select query in a form and varying
the bound colums to pull information out of one database to enter it into two
separate fields in another database. Is it possible to use a select query
for a form that will fill in the current field with one value and
automatically fill in the second field with its companion value. Example:
"part number 12345" and part name "gold widget". I would like to run the
select query on the part number and have the part number added to the part
number field and without running the query a second time having the part name
automatically added to the part name field. The part name field is the next
field on the form. Access 2003.

On Wed, 20 Dec 2006 12:14:00 -0800, JBledsoe
wrote:

Currrently I am using the same combo box select query in a form and varying
the bound colums to pull information out of one database to enter it into two
separate fields in another database. Is it possible to use a select query
for a form that will fill in the current field with one value and
automatically fill in the second field with its companion value. Example:
"part number 12345" and part name "gold widget". I would like to run the
select query on the part number and have the part number added to the part
number field and without running the query a second time having the part name
automatically added to the part name field. The part name field is the next
field on the form. Access 2003.

You should NOT be storing the part name redundantly in your second
table AT ALL.

Instead, set the Control Source of the second textbox to

=comboboxname.Column(1)

to see the second column (it's zero based) of the combo's rowsource
query.

John W. Vinson[MVP]

JBledsoe,

Yes, I think I understood what you wanted from your original question.
And thanks for the additional clarification.

The point I am trying to make is that what you are wanting to do, while
possible, is incorrect. The second table should not contain two fields
which are the same as the fields in the support table. This is an
invalid table design. The part name field should be removed from the
second table. It is unnecessary and inadvisable.

You can certainly have the data from both fields shown in your
combobox's drop-down list. This combobox will be bound to the part
number field in the second table. The goal then, is for the part name
to be automatically shown on the form, based on the selection made in
the combobox. As mentioned in the article, this can be done via either:
- including the support table in the query that the form is based on
- using a calculated field within the Row Source of the combobox, in
order for the combobox to display a concatenated value that includes the
part number and part name
- using a DLookup() function in the Control Source of an unbound
textbox on the form
- referencing the Column property of the combobox in the Control
Source of an unbound textbox on the form

Hope that makes it clearer.

--
Steve Schapel, Microsoft Access MVP

JBledsoe wrote:
The support table lists many parts (records) by listing each part number in
one field and part name in the next field of the same record. I want to
combo list the records in this support table via a select query to enable me
to enter the record information (part number and part name) into two fields
of a second table (with the same names as the support table uses) at the same
time rather than having to do it separately for each field. I hope my
nomenclature is consistent here -- I know what I want to and hope I am
expressing it clearly. The end goal is to be able to have the buyer with
part numbers and part names all in the same record of the second table and to
reduce the part associated key strokes by one half.