Compound foriegn key in lookup field?

[Sheco]

Suppose I have a business org structure in a table with a compound key made up of all three fields. The data might look like this:
[tt]
Division Branch Office
A 1 NY
A 2 TX
A 2 OK
A 2 NM
B 1 CA
B 1 NV
B 3 AZ
C 4 FL
[/tt]


I also have a second table that has all 3 of these fields plus a lot more fields. In this second table, those 3 fields are on the "many" side of a one-to-many relationship back to the org table.

Is there a way that I can do the lookup so that if a user is editing this second table and they choose "A" for the division field then the dropdown for the Branch field will only contain values 1 and 2 but not 3 and 4 since there are no Branch 3 or 4 in division A ?

I guess what I'm really asking here is if it is possible to reference values on the current new row in the SQL query used for your Row Source?

 

[Lilliabeth]

Hi Sheco,

What a great reason to do data entry in a form instead of a table. This would be moot.

 

[Sheco]

Yes I agree but unfortunately this is not an option because of silly businessmen.

The original estimate included forms for data entry but the client didn't want to pay so much so they negotiated behind my back to do it without the forms for a reduced price.

On one hand I could just say, "you get what you pay for" and not worry about relationships and referrential integrity... if they mess it up then they should have paid for the forms. But I don't want to do that. To the extent possible, I'd like to use Access's rules to deliver the "safest" result for the end user so I'm trying to set all of the default values, input masks, validation rules, and enforced relationships.

 

[Cherry2005]

This is very much like the problem I'm having.
Part # Machine # Scrap amount
a 1 x
a 2 y
a 3 x
b 1 y
b 2 x
c 4 z


Did you get an answer to your problem?
If so, would you share?