Dropdown List values different from bound field values (1 Viewer)

jharding08

Member
Local time
Today, 04:42
Joined
Feb 16, 2021
Messages
55
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

I’m here to help
Staff member
Local time
Today, 04:42
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 04:42
Joined
Feb 16, 2021
Messages
55
I'm guessing using the dropdown and a bound textbox and an afterupdate event ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:42
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 19, 2002
Messages
42,981
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
 

Users who are viewing this thread

Top Bottom