Different Listbox Dsplay Value vs Dropdown Value

Filibuster

New Member
Joined
Oct 17, 2013
Messages
7
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top