Different Listbox Dsplay Value vs Dropdown Value

[Filibuster]

I have a list box that is bound to a person's name field resulting from a query. When I click the dropdown feature, I get the list of all the names in the query. I'd like to limit the dropdown to a select list of names only but still have the control display whatever is currently stored in the name field. Is there a way to separate what the control displays from the values visible in the dropdown (e.g use a different query for each, or some other method)?

For context: the name in the database is the employee who developed a project. I'd like that name to display. The person may or may not be a current employee. For assignment (or reassignment) purposes, I want the dropdown to only show current employees.

 

[xenou]

Comboboxes seem to work that way easily - just set the row source to a query of active employees and row source type to table/query.

With listboxes it seems that you can't display a value that is not in the list box (listboxes work by index on the values in the list so the chosen item must be in the list). I could be wrong but that's what I find when I build a list box and adjust it's rowsource.

 

[Filibuster]

Thank you xenou for the response. My mistake - I am using a combo box. If I set the row source to active employees, the dropdown list will show only active employees. However, the display portion of the control will not show inactive employees (because the bound query is only grabbing active employees). which were previously loaded into the database.

 

[xenou]

if the form's data source is only for active employees then I'm not sure how you would have an inactive employee in that form's data source. I think the form's data source would need to include all the employees you want to display in the form.

 

[Filibuster]

Correct. This is the crux of my issue. The data source needs to include active and inactive employees. But, I would like to limit the drodown list to only active employees. Is there a method to do this?

 

[xenou]

You set the rowsource of the combobox separately from the data source of the form (i.e. what the form is bound to). So one can be active employees and the other all employees or vice versa.

 

[welshgasman]

You could have a checkbox/radio button for Active/Left employees and use the correct source depending on what was selected.
You could use a union query
Select Active
Union
Select Inactive
that way all the inactive would be out of the way at the bottom
A radio button could have Active/Inactive/All options?

HTH

 

[Filibuster]

I know just enough to be dangerous with Access. I did not design this database, so everything I do is a learning experience. Thanks for your patience and sticking with me.

As I've investigated this, here is some additiona info that will help:
The control source is based on a query at the form level (it returns projects and who is assigned to the project). This query will result in active and inactive employees.

Currently (and I want to change this), the row source query returns ALL employees from the employee table. The query returns an employee index, and employee initials. The control is bound to column 1, but the ColumnWidth property is set to Zero for column 1. We only want to display the employee initials.

Limit to List is set to YES. It is also worth noting that the query uses a table containing a Lookup field which generates the initials.


I changed the row source query to return only active employees. This resulted in the correct dropdown list. But, the control only displays project owners who are active employees. Control source records with inactive employees are displayed as blank.


So, I changed the Limit to List property to NO, thinking this might help. I get an error that saysing something to the effect that the property cannot be changed to the zero width of the bound column.


This error is now my focus (I think). Any way to work around it?