Run a lookup with unique criteria based off each individual record

[King Kreglo]

Hi there! I am having an issue with my lookup function. :banghead:

I want the lookup to be performed with criteria based off of the same row that record being selected is on.

I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.

Here’s an idea of what I am working with. You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:

|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR

The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9 |
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |

This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|

The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |

Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
Thanks in advance to whomever the MS Access prodigy is who can figure this one out, I appreciate it!:banghead:


P.S. Here is a copy of the SQL statement I’m using right now.

SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
:banghead:

 

[King Kreglo]

Also, I'm pretty new to this site! Any first-timer pointers you want to give me would be appreciated

 

[King Kreglo]

Thank's so much for the response Pat! This works great, a little slow but it gets the job done!

 

[King Kreglo]

Here's what my final statement ended up looking like to whoever is interested

UPDATE [DSL Carrier CLLI's] INNER JOIN [Customer NPA-NXX's - ADSL - Test] ON [DSL Carrier CLLI's].DSLCarrier = [Customer NPA-NXX's - ADSL - Test].[LowCap(L)].Value SET [Customer NPA-NXX's - ADSL - Test].[LowCap Carrier] = [DSL Carrier CLLI's].[DSLCarrier] WHERE ((([Customer NPA-NXX's - ADSL - Test].[LowCap Carrier])=[forms]![frmCustomer NPA-NXX's - ADSL - Test]![CLLI]));

 

[King Kreglo]

Yeah I noticed my mistake right after I sent that reply!
Update's nearly instantly right now. Thank you