Dropdown List values different from bound field values

[jharding08]

I have a dropdown that lists employees but only active employees (where inactive=FALSE). The dropdown is also bound to a table that stores the employeeID per record and has historical data (Inactive=TRUE or FALSE.)

I am seeing that when an employee is set to false, it doesnt show in the dropdown list, which is correct, but also doesnt show in the bound dropdown field. I dont have any criteria to filter it out in the form and the form recordset shows the value

Is there a property in the dropdown that controls this?

 

[theDBguy]

Is there a property in the dropdown that controls this?

There is, but it never worked, or I don't understand what it's supposed to do. There are a couple of workarounds for this issue.

 

[jharding08]

I'm guessing using the dropdown and a bound textbox and an afterupdate event ?

 

[theDBguy]

I'm guessing using the dropdown and a bound textbox and an afterupdate event ?

Would be one approach. Another is to not filter the dropdown but just sort it to move all inactive to the bottom.

 

[Pat Hartman]

The problem you are seeing is caused because in a continuous form, all visible records are instances of the SAME form and Access can keep only one set of properties for a form. By using a Where clause in the RowSource of the combo, you are essentially making the query different on each row.

The solution I use for this situation is the one suggested by theDBguy. I modify the sort of the RowSource query so that the inactive people drop to the bottom. BUT, I also add code in the BeforeUpdate event of the combo to prevent the user from selecting an inactive person.

The solution is different when you are working with cascading combos.

PS, I also modify the selection so that in the RowSource, I make the inactive flag display as "inactive" or null. Showing the active status is just noise and unnecessary. But showing "inactive" is a trigger to people to not even try to select that person.

Select PersonID, LastName & ", " & FirstName As FullName, IIf(InctiveFlg = True, "Inactive", Null) as ActiveStatus
From YourTable
Order by inactiveFlag, LastName & ", " & FirstName