CODEWITHSUNDEEP
ms-access
ragoertz
ragoertz

Reputation: 13

How to display combo box options that can't be selected

I have a combo box ([TaskID]) bound to a field [Selected Task ID]. The row source for [Task ID] comes from a table and includes [Task ID] and [Task Name], limited to entries where [Active] (another field in that table) =True. As [Task ID] is the value to be entered in the [Selected Task ID] field, it is set as the bound column. Since no one needs to see the value of [Task ID] (it's just an autonumber), however, the column count is set to 2 and the widths to 0";2". Today there are two tasks with the [Active] flag set to True, and I have different records where the [Selected Task ID] field is one or the other of those two tasks. On the form, it looks like:

            Task Name 
Record 1:    Task 1
Record 2:    Task 2

Tomorrow, Task 1 will no longer be active (ie, you can't assign time to it anymore). I still want to be able to see "Task 1" displayed for Record 1 (since its [Task ID] is stored in the [Selected Task ID] field), but I don't want to be able to select Task 1 anymore. When [Active] is set to False for Task 1, however, the display on the form field is blank:

            Task Name 
Record 1:
Record 2:    Task 2

(Remember that the combo box is used both to display and set the data for that field.) Looking at the data table itself, the Task ID is indeed stored properly, but the name associated with it isn't being displayed (presumably because it's excluded from the combo box options due to the [Active] flag being set to No).

How can I display the values for previously valid entries while only enabling the selection of currently valid entries?

Upvotes: 1

Views: 386

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

  1. You can create two comboboxes: one for data display without filtering inactive elements, the second - for data selection, with filtering. Place display control over selection one and add code to GotFocus event of display control:

    Me.cboSelect.SetFocus
Me.cboSelect.Dropdown

    Now valid and invalid entries will be always visible, but user won't be able to select invalid row from dropdown. Minus of this workaround is that value of invalid entry disappears if you click the field.

  2. You can add third column to Row Source query with Active indicator, sort the list by Active field first in order to move inactive elements to the end of list and add code to Before Update event of combobox, which doesn't allow user to select rows with inactive elements.

Upvotes: 1

Related Questions