Info Disappears from query-bound Combo Box

[dgunner71]

Hello all -

I have a combo box (cboEmployee) on a form for service visits. The combo box record source is a query and only displays active employees. The control is bound to the employee number, but displays the employee last name. (i.e. column widths 0";1") The Limit to List property is set to Yes (and I get a message that it must stay set to yes unless I change the column widths).

The user is then able to select an employee from the combo box. However, when an employee is no longer active, (no longer shows up in the query), he/she also disappears from past service visits he/she may have completed. If I change the column widths to 1";1", the employee number will show up - but it seems there is no way to still display the Employee Name.

Is there a way to still display the employee names if they have been removed from the query? (i.e. I want to view all, but still restrict users to only select active employees.)

Thanks in advance for any insight.

[jimatqsi]

No, because you have bound a data item in the record set to the combo box that you are using to search/recall employees. I have found this to be a bad idea, so I don't do it any longer.

The best thing is to make your combo box unbound to the form's record set. After an employee is selected in the combo box, go run a little bit of code to advance the record set to the selected employee. I suppose you can then display a message if the selected employee is not active or whatever keeps him/her from being in the record set.

Jim

[dgunner71]

Just as an update to this problem, I did find the issue.

The control source of the combobox was based on an SQL statement that required the status to be active.

ControlSource = SELECT [ID], [txtValue], [ynActive] FROM tbl WHERE [ynActive] = -1;

Bound Column = 1
Column Count= 3
Column Widths = 0;1.25;.5

My problem was I stored value 5 (i.e. [ID]=5) in a record and later marked value 5 not active ([ynActive] = 0). The stored value of the combobox was still 5, however the combobox itself appeared blank because the SQL statement was preventing the value from appearing.

I had to add an Or statement to the SQL clause to specify either the value is active OR the value matches the [ID] of the form.

Gunner