Combo Box Select Only Items that are associated w/ company name

[sambo]

I have quite a complex set of relationships. I've got a table dedicated to Ship To Adresses. I would like to populate a combo box (cboContact) on a form with only those Contact Names that are associated with the company name that resides in the text box txtCompany.

The problem, I can't find a direct link between the Company and its list of contact names because [ContactNames] resides in a table that is 3 tables to the right in my relationships.

Here is a screenshot of my relationships...
I need to relate tblCompany to the "Ship To" table in a query. I am a little too far along in the game to be changing all my relationships, so I am hoping to solve this with a query.

Any help would be appreciated.

 

[Tiro Cupidus]

What happens if you build a query with tblCompany, tblUnits, tblRma, and Ship To tables and the Company and ContactName fields with a criteria for one company listed in the Company field? I figured it would work =)

 

[sambo]

That sounds cool, but you're going to have to spell it out more than that for me.
I have been staring at this for so long, I don't really even remember what I am looking at.

 

[Tiro Cupidus]

Build a query with these 4 tables:

tblCompany
tblUnits
tblRma
Ship To

Add these fields:

Company (from tblCompany)
ContactName (from Ship To)

Once you put some criteria on Company (ex: Like "Sony"), run the query. Seems like it should only return Contact Names for Sony.

 

[sambo]

I think I've got it, my only problem is now the requery event of the combo box. How can I make the combo box requery every new a different record is scrolled to?

There is no OnNewRecord Event in the form events collection, is there something similar?

 

[sambo]

You Rock Tiro,
Works like a charm. I found the OnCurrent event of the form and placed my combo requery in there.

Thanks...

 

[Tiro Cupidus]

Hey, cool! =)

Glad you got it to work.