When working with plain 'ol Tables, how can you set the "Lookup" of a Field to return values based on the value of another Field in the same Record.
For example, I have the following three Tables like so:
Customer Table:
Code:
CustomerID|CustomerName
1|Rawb's Incredibly Expensive Imports
2|pbaldy's Second-rate Knockoffs
ShipTo Table:
Code:
CustomerID|ShipToID|Address1|Address2
1|1|100 Hoity Toity Ln|
2|1|123 Backalley St|Apt -1
Orders Table:
Code:
OrderID|OrderCustomerID|ShipToID
1|1|1
2|1|1
3|2|1
4|2|1
How do I get my Orders Table (when looking at the straight Table in Datasheet View) to only show the relevant ShipTo entries instead of all of them?
I've already tried entering the following into the Field's Lookup Row Source:
Code:
SELECT * FROM ShipTo WHERE [CustomerID]=" & [OrderCustomerID]
P.S.
I have an ENORMOUS amount of respect (and envy) for pbaldy. All usage of his name is done lightheartedly! >.>