My first attempt was using a value list that used the Value from CompanyID and showed the value form the CompNameDiv. This did not work, as the record reverts back to showing the CompanyID.
Contact::CompanyID = Company::CompanyID
appears to be the relationship that you already have and should be all that you need for this to work.
It sounds like you formatted Contact::CompanyID with a drop down list where CompanyID and CompNameDiv from Company are the first and second fields of your value list and you selected the option to hide the ID field. That correct?
If so, when you exit the Contact::CompanyID field, you will see the ID number as that is the value that was actually entered into the field. But there are two basic ways you can show the company name and division.
1) Simply place the CompanyNameDiv field from companies on your layout and it will display this data once you select a company from the drop down list. You can even set field behavior on this field to prevent access when in browse mode, give it an opaque fill color and place it on top of the drip down list field. When you click on this field, the drop down list pops to the front and deploys, when you select a value, the list disappears back behind the CompanyNameDiv field--which now displays the company name and division of the company you just selected. BTW you can add any fields from Company that you want to your contact layout and they will display data from the company you select from the value list.
2) Use a pop up menu instead of a drop down list. Pop up's behave a bit differently, but if you select the "hide first field" option, the second field is displayed in the field after you exit the field.
Oh yes, and CompNameDiv should be defined as:
Company Name & " " & Division Name
Company Name + Division Name
Worked exactly like I needed it too. Have lots of reading to do on the Cartesian X.
The laying over of an edit box on top of the drop down is definately added to the toolbox. Love stupidly simple solutions.
Phil is right, you don't have to use the "x" . You just need the self join. Didn't mean to miss lead anyone, just got in a hurry making example.
Just being picky about details here, but a "self join" is a relationship that links a table to itself by using a second occurrence of the same data source table.
We don't have that type of "join" here and such a join is not needed in order to get this to work.