IIF SELECT in Row Source

I sent a question about this earlier but since it has not shown up, I am 
sending a shorter version.  I have a field in my database that is a combo
box 
and the SELECT statement in the row source looks up a value in another
table. 
 Is it possible for the row source to contain a statement that will cause
the 
lookup to look in another table based on a certain value in another field?

Your help is greatly appreciated,
ridgerunner