limiting options when creating a list

[Guest]

This is hard to explain but I'm trying to reference a 2 column table in my
form. The first column is a business unit name and the second is a small list
of names associated with that unit.
I'm trying to create a form where the user selects a business unit in one
field, and in another he has to select 1 name that is a subset of the names
related to that unit. For example, if he goes into the form and selects
business unit C, he has to also select a name from a list of names only
associated with that unit.

Can you help me figure out how to design this? I'm guessing I'd have to
write an expression somewhere that says "if you choose business unit A in
field 1, then your options in field 2 are x, y, and z."

 

[Wayne Morgan]

To set this up properly you need two tables set up in a one-to-many
relationship. The second table will contain the values you want displayed in
the second combo box or list box. For the Row Source of the second box, you
use a query (either a saved query or a SQL statement) that refers to the
value of the first box in the WHERE clause. This limits the possible
results. In the AfterUpdate event of the first box, requery the second box
(Me.cbo2ndCombo.Requery).

Example SQL:
SELECT Field2 FROM Table2 WHERE Field1= [cbo1stCombo] ORDER BY Field2
or, if the query is a saved query:
SELECT Field2 FROM Table2 WHERE Field1= [Forms]![frmFormName]![cbo1stCombo]
ORDER BY Field2

Field1 of the second table would be the field that links to the first table.
While this can be done with a single table, you are having to continuously
repeat the data (with no typing errors) in the first column for each sub
item in the 2nd column. If that is a small value, initially this may be no
problem. However, if the value in the first column changes later (i.e. the
business unit changes its name) you'll have to change it in each record
(this could be done using an Update Query). If you use the two table
approach, you only have to change it in the first table and the liked field
(usually an ID field) would maintain the link between the two and the new
name would automatically be applied to all of the sub records.