Look Up Data Type + IF Validation Rule

[ExcelMercy]

Hello,


I would like a Data Type "Look Up". Based on what is selected, I want the next field which is also a Look Up to offer certain options based on what is picked in the first Look Up field.



Example:

Field Name	Data Type
User_ID	AutoNumber
User_State	Look Up
User_City	Look Up

<tbody>
</tbody>

__________________

User_State

New York

Tennessee

Oregon

<tbody>
</tbody>

__________________

User_City Look Up:

Column1	Column2	Column3
Nashville	Salem	New York City
Memphis	Portland	Buffalo
Knoxville	Eugene	Rochester

<tbody>
</tbody>

Anyways, I need something that will make it so based on what option is picked in the User_State. This will determine which column is allowed to be pick from.




I know this isn't correct, but in terms of code this is kind of what I'm after.

= if(User_State = "New York", User_City = Column3, if(User_State = "Tennessee", User_City = Column1, if(User_State = "Oregon", User_City = Column2, "")))


I was thinking Validation area would be the area I should edit?


- I can do either custom entry or look up to a table, it doesn't matter to me.




Any help would be greatly appreciated!
Thank you

 

[Russell Hauf]

I would have a table that had both State and City in it. The State would be duplicated, of course, but for Salem, Portland, and Eugene, the State would be Oregon for each one. Then you can look up City based on State. Make sense?

 

[ExcelMercy]

Ok, so like:


Table: State

St_State

Tennessee

Oregon

New York

<tbody>
</tbody>

Table: State_City

StC_State	StC_City
Tennessee	Nashville
Tennessee	Memphis
Tennessee	Knoxville
Oregon	Salem
Oregon	Portland
Oregon	Eugene
New York	New York City
New York	Buffalo
New York	Rochester

<tbody>
</tbody>

Table: User Design View

Field Name	Data Type
User_ID	AutoNumber
User_State	Look Up (from table: State)
User_City	Look Up (from table: State_City)

<tbody>
</tbody>

How would I make it so User_City's options are based on what's in User_State's field?





Did you mean filling User_State & User_City solely from Table: State_City? How would I go about removing the duplicates? Plus how would I make User_City dependent on User_State's answer?

 

[Russell Hauf]

You could use GROUP BY or DISTINCT to remove duplicates:

SELECT DISTINCT StC_State FROM State_City;

Where exactly are you wanting to do this "Look up?" So to get only the right values for the selected City, you'd do something like this:

SELECT StC_City FROM StC_State WHERE StC_State = [your value from a...form(?) that you pass in];

The value could be something like Forms!frmChooseLocation!cboState.Value (I can't remember the exact syntax right now, but should be close)

There are also other ways of passing in the State value. I hope this helps.

 

[ExcelMercy]

Should I be trying to group them using a Query?

I was attempting to to this table-to-table. I want the "Look Up" directly in one of my tables, from another table.


I tried what you suggested, but I'm to much of a novice to get it working.

 

[Russell Hauf]

I'm sorry - I don't have MS Access available to me most days. If you create the separate tables and link them, that would be a good start.