LookUp Values That Depend on Previous Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my table, I have one column that contains a list of values to choose from.
In the next column, I want another list of values. But the values depend on
the value chosen in the first column. For example:

Column1: choose A, B, or C
Column2: if A, then choose 1, 2, or 3 if B, then choose 4, 5, or 6.

How would I set this up in the Design View?
 
PLEASE SEARCH BEFORE POSTING!!!!

VERY COMMON QUESTION

Look for "cascading combo". You will probably find three or four posts from
today alone.
 
In my table, I have one column that contains a list of values to choose from.
In the next column, I want another list of values. But the values depend on
the value chosen in the first column. For example:

Column1: choose A, B, or C
Column2: if A, then choose 1, 2, or 3 if B, then choose 4, 5, or 6.

How would I set this up in the Design View?
Click to expand...

First off... DON'T use table datasheets for data entry. They are of
very limited capability, and in particular, cannot achieve this.

Secondly... get rid of ALL your Lookup fields. They do more harm than
good. See http://www.mvps.org/access/lookupfields.htm for a critique.

Finally, use a Form (let's call it frmMyForm) to enter your data,
rather than a table datasheet. Put a combo box on it based on Column1;
say it's called cboColumn1. Create a Query

SELECT [Column2] FROM sometable WHERE [Column1] =
[Forms]![frmMyForm]![cboColumn1] ORDER BY [Column2];

Base a second combo box, cboColumn2, on this query. You'll then need
one single line of VBA code. Open the Form in design view; view the
Properties of cboColumn1; on the Data tab find the AfterUpdate
property and click the ... icon. Choose "Code Builder" and edit the
code to

Private Sub cboColumn1_AfterUpdate()
Me!cboColumn2.Requery
End Sub

John W. Vinson[MVP]
 
Thanks for the info. I did search before posting, but it would never occur to
me to look up "cascading combo".
 
Back
Top