LookUp Values That Depend on Previous Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simula67 - 18 Jan 2005 22:17 GMT

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?

Signature

"Nirvana is here. It's our minds that are elsewhere."

Reply to this Message

Rick B - 18 Jan 2005 22:30 GMT

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
[quoted text clipped - 4 lines]
>
> How would I set this up in the Design View?

Reply to this Message

Simula67 - 21 Jan 2005 21:09 GMT

Thanks for the info. I did search before posting, but it would never occur to
me to look up "cascading combo".

> PLEASE SEARCH BEFORE POSTING!!!!
>
[quoted text clipped - 13 lines]
> >
> > How would I set this up in the Design View?

Reply to this Message

John Vinson - 19 Jan 2005 02:00 GMT

>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
[quoted text clipped - 4 lines]
>
>How would I set this up in the Design View?

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]

Reply to this Message