Form Drop-down list

Is there a way to have a drop-down list update several fields at once? For
example, I've got a drop down list with 3 fields: category code, G/L acct
nbr, and Acct Unit and I would like to update all 3 fields in my record.
However, there is only one "bound" field. Is there any way around this? Of
course, I could just store category code in my main table and do a table
lookup when I want the other two fields. For simplicities sake, I would
prefer to have the drop-down list just update the 3 fields in the main table.

What happens if you go to a record and change the category code? Do you then
also need to change the other two fields? Also, what happens if Acct Unit
changes for a particular category? Do you need to go find and update every
record that uses this category? If the answers to these questions are yes
then you need to put the fields into the Category table and use a join to
this table whenever you need to see them.

You can easily add the fields to the combo's rowsource and just display the
values on the form (in read/only controls).

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Julie Mascatello wrote:
Is there a way to have a drop-down list update several fields at
once? For example, I've got a drop down list with 3 fields:
category code, G/L acct nbr, and Acct Unit and I would like to update
all 3 fields in my record. However, there is only one "bound" field.
Is there any way around this? Of course, I could just store category
code in my main table and do a table lookup when I want the other two
fields. For simplicities sake, I would prefer to have the drop-down
list just update the 3 fields in the main table.

If I understand your question right, then just follow these simple steps.

In Design View, ensure the Toolbox Wizard is shaded (ON)
Choose the drop down icon off the toolbox. Drag it onto your form. When the
wizard pops up, choose the 3rd option, the one that says find a record on my
form....

Choose which field to be the field to choose from. Only include one of them.
Then, just finish up the wizard as normal. Hope that helps

"Julie Mascatello" wrote:

Is there a way to have a drop-down list update several fields at once? For
example, I've got a drop down list with 3 fields: category code, G/L acct
nbr, and Acct Unit and I would like to update all 3 fields in my record.
However, there is only one "bound" field. Is there any way around this? Of
course, I could just store category code in my main table and do a table
lookup when I want the other two fields. For simplicities sake, I would
prefer to have the drop-down list just update the 3 fields in the main table.

Julie,

To add to Sandra's response, this issue is part and parcel of the general
topic "Database Normalization".

Generally, the *only* field to duplicate from a 2nd table is the 2nd table's
Primary Key, called a Foreign Key in the first table. This foreign key
*unlocks* and gives *Access* to all of the fields of the 2nd table through a
query which links the tables by the common field.

The only exception I can think of are time-based fields. For example, an
OrderEntryDetails table needs a a ProductID field (a foreign key to the
Products table), and also a UnitPrice field to store the product's UnitPrice
on the date of the order.

Normalization is IMO the most important topic in relational database design.
Do it well, and the application is a breeze to maintain. The following
links cover this topic.

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genacc...abasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default...lurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en...ml/ch04DDP.asp

Hope that helps.
Sprinks

"Julie Mascatello" wrote:

Is there a way to have a drop-down list update several fields at once? For
example, I've got a drop down list with 3 fields: category code, G/L acct
nbr, and Acct Unit and I would like to update all 3 fields in my record.
However, there is only one "bound" field. Is there any way around this? Of
course, I could just store category code in my main table and do a table
lookup when I want the other two fields. For simplicities sake, I would
prefer to have the drop-down list just update the 3 fields in the main table.