By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,606 Members | 1,796 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,606 IT Pros & Developers. It's quick & easy.

Combo Box I want to show Old Values but not when updating

P: 68
In my database I have a form that allows the user to view and update data. I use combo boxes to standardize entry of the data. The issue that I have found with combo boxes is that when the data goes out of date, i.e. the Employee leaves, the user can no longer see the employee on the form. This has to do with the list values being limited to Active Employees.

What I want to do is show all of the employees on the form but only allow the user to select the active employees when they are updating the record.

How do you get around this issue?
Jun 12 '15 #1

✓ answered by jforbes

I think this behavior only happens when using a key column and an additional display (human readable) column, and maybe Limit to List turned on. If you were to save the Employees name into the Record on the Form, it would display inactive Employees, but only allow you to select active Employees.

That would probably be a pretty serious change for you, so you might want to include the currently selected Employee in the RowSource for the ComboBox. It's not as clean, but it would be prettier than what you have. To do this, include something like this as the RowSource of the ComboBox:
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee.ID, Employee.FullName, Employee.Active
  2. FROM Employee
  3. WHERE Employee.Active=True 
  4. OR Employee.ID=[Forms]![TheCurrentForm]![ComboBoxName]
Then you'll need to add a Requery in the OnCurrent Event of the Form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.ComboBoxName.Requery
  3. End Sub

Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
You could use the control's BeforeUpdate event to check if the employee is active. If they are not, then cancel the event, undo the change and show a message. Here is an example from the MSDN website: ComboBox.BeforeUpdate Event.

To help the users know which ones are active, you could add another column to your combobox that displays this information. It would only show when the box is expanded, but it would help.
Jun 12 '15 #2

P: 68
Thanks Seth, I solved this by adding a text box that contains the Employee's name from the lookup table as a part of my base query, then making the combo box shrink to show only the drop down arrow part of the box and limited the combo box list to active employees. By putting combo box next to the text box the user is able to see them as if they were one field. When they open the Combo box list they are able to select an active employee and the text box updates automatically.

It feels messy to me but it does what I needed. I am open to feedback on this method of solving the problem of seeing the employee currently selected, who may no longer be an employee, and allowing selection of only active employees.
Jun 12 '15 #3

jforbes
Expert 100+
P: 1,107
I think this behavior only happens when using a key column and an additional display (human readable) column, and maybe Limit to List turned on. If you were to save the Employees name into the Record on the Form, it would display inactive Employees, but only allow you to select active Employees.

That would probably be a pretty serious change for you, so you might want to include the currently selected Employee in the RowSource for the ComboBox. It's not as clean, but it would be prettier than what you have. To do this, include something like this as the RowSource of the ComboBox:
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee.ID, Employee.FullName, Employee.Active
  2. FROM Employee
  3. WHERE Employee.Active=True 
  4. OR Employee.ID=[Forms]![TheCurrentForm]![ComboBoxName]
Then you'll need to add a Requery in the OnCurrent Event of the Form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.ComboBoxName.Requery
  3. End Sub
Jun 13 '15 #4

NeoPa
Expert Mod 15k+
P: 31,769
I have a very similar situation. Seth's approach works fine for me.
Jun 13 '15 #5

jforbes
Expert 100+
P: 1,107
Oh, yes. I didn't mean to say that Seth's approach wouldn't work. It's a great approach, especially if you want to allow your users to select inactive employees and warn that they are doing so.

I just wanted to offer it up as an option, because there are times when only an active employee should be selected. For me, limiting the list to only active employees is preferable to listening to users ask me, "If we are not supposed to select inactive employees, then why are they in the list?"
Jun 15 '15 #6

NeoPa
Expert Mod 15k+
P: 31,769
JForbes:
"If we are not supposed to select inactive employees, then why are they in the list?"
My answer to that one is the simple truth - Because the list covers existing records as well as valid selections for new or updated ones.

Having said that, your suggestion is perfectly valid. It's a tidy solution if twittering users are a problem (and they can be). There's always room for more solutions.
Jun 15 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.