Combobox ControlSource restricted by RecordSource query 1

[dbuehmann]

Inside a continuous form, I have a combobox whose ControlSource is an id but displays a name (as it should). The RecordSource is set to a query that returns an id and name. The BoundColumn is the id column.

However, I want the RecordSource query to return a different list of id:name pairs depending on another field in that record (e.g. list only the employees that are in a department selected earlier in that row). Easy, right? - just add that condition to the query.

Here's the problem. The combobox list gets filled correctly for each row. However, the textbox portion of the comboboxes only display correctly for the currently selected record (and any other records that have the same department selected). Apparently, when Access does the name:id lookup for the textbox part of the combobox, it applies the condition for the currently selected record (e.g. I can only see employees in the textbox if they are in the same department as the department selected in current record).

Is there any way to get the ControlSource id:name lookup to work independently of the RecordSource query? I'd prefer to keep the field bound to avoid manually managing list updates.

thx!

 

[930driver]

Generally, any formatting applied to a row in a continuous forms form applies to every row. This includes row sources for combo boxes. I have sometimes been able to get around this by adding the name to the recordsource of the form and populating a text box with that. I put the text box over the combo such that it covers all but the dropdown arrow. It then looks like a combo but will display appropriately.

If you set the text box's properties as follows;
Enabled: No
Locked: Yes
Tab Stop: No
then for the current row the text box will drop behind the combo and your combo will look and work correctly.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"