Hi Larry.
I'm trying to have a combo box show a descriptive value while returning a foreign key based on a conditional set of records from the lookup table.To make this easier, you need to set the Column's Sparse and Bound properties to .F. Make sure the combo's style is set to 2-Dropdown list. Then, set up the column's controlSource like this:
I may have the field names and table name of the lookup table wrong, but you get the idea. You set up a conditional ControlSource that looks up the key in the lookup table and returns the descriptive text.
( IIF( SEEK( devaeres_v.rat_id, 'devarate', 'rat_id' ), devaeres_v.rat_title, '' ) )
And set the combo's ControlSource to the foreign key field.
At this point you are probably saying to yourself “Hang on, there! Setting up the column’s ControlSource like that has the side effect of making the column ReadOnly!” and you would, in fact, be correct. However, you can still drop the list and make changes. The reason for this is that the ReadOnly attribute applies only to the portion of a control that accepts text. Since a drop down list does not accept text, it cannot be made read only (any more than a command button could be made read only). This behavior is documented and is by design.
Now all you gave to do is Requery() the combo in its GotFocus()
HTH