Removing choices from combobox query but stilling showing for older entries

I have a form, frmMain that lists staff names in a combobox from a query
into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and
ShowInViews. The RowSource Query for the combobox is:

SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE
(((lkpStaff.ShowInViews)=True));

The combobox ControlSource is the field Staff in tblMain which is populated
from frmMain.

By unchecking ShowInViews for staff that are no longer employed I can remove
them from the combo's dropdown. However, of course, it also removes those
staff names from older entries. I want to have my cake and eat it too,
i.e., to remove the staff person as a choice going forward, while still
retaining them in older records.

I hope that makes sense and thanks in advance.

Doug


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

It sound like you should be able to have your cake and eat it.
However, you haven't made clear what you mean by "older entries".

If you have another form or report using the same SELECT query,
all you need to do is drop the WHERE clause. If you have a
different SELECT query, omit ShowInViews altogether.

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"Doug Glancy" wrote in message
...
|I have a form, frmMain that lists staff names in a combobox from a query
| into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff
and
| ShowInViews. The RowSource Query for the combobox is:
|
| SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE
| (((lkpStaff.ShowInViews)=True));
|
| The combobox ControlSource is the field Staff in tblMain which is
populated
| from frmMain.
|
| By unchecking ShowInViews for staff that are no longer employed I can
remove
| them from the combo's dropdown. However, of course, it also removes
those
| staff names from older entries. I want to have my cake and eat it too,
| i.e., to remove the staff person as a choice going forward, while still
| retaining them in older records.
|
| I hope that makes sense and thanks in advance.
|
| Doug
|
|
| __________ Information from ESET NOD32 Antivirus, version of virus
signature database 4852 (20100209) __________
|
| The message was checked by ESET NOD32 Antivirus.
|
| http://www.eset.com
|
|
|

Len,

Len,

Thanks for your time. I was afraid it wouldn't be clear.

It's all the same combobox in the same form. Basically, I want to not show
a name as a choice now, while still displaying that name for previous
records. This is just on the form. The underlying data, as you suggest,
is fine. I just want the database users to still see the name "John" for a
record they entered on the form last year when John was an employee, but not
be able to pick "John" as a choice today.

Doug

".Len B" wrote in message
...
It sound like you should be able to have your cake and eat it.
However, you haven't made clear what you mean by "older entries".

If you have another form or report using the same SELECT query,
all you need to do is drop the WHERE clause. If you have a
different SELECT query, omit ShowInViews altogether.

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"Doug Glancy" wrote in message
...
|I have a form, frmMain that lists staff names in a combobox from a query
| into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff
and
| ShowInViews. The RowSource Query for the combobox is:
|
| SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE
| (((lkpStaff.ShowInViews)=True));
|
| The combobox ControlSource is the field Staff in tblMain which is
populated
| from frmMain.
|
| By unchecking ShowInViews for staff that are no longer employed I can
remove
| them from the combo's dropdown. However, of course, it also removes
those
| staff names from older entries. I want to have my cake and eat it too,
| i.e., to remove the staff person as a choice going forward, while still
| retaining them in older records.
|
| I hope that makes sense and thanks in advance.
|
| Doug
|
|
| __________ Information from ESET NOD32 Antivirus, version of virus
signature database 4852 (20100209) __________
|
| The message was checked by ESET NOD32 Antivirus.
|
| http://www.eset.com
|
|
|




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4852 (20100209) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Hi Doug,
Clearly, if John isn't in the list he cannot be displayed by the combo.

Perhaps you could create a textbox the same location, size and shape as
the existing combo and set it to use DLookUp to display John (or anyone
in lkpStaff). You could use the OnCurrent event to make either the combo
or the txtbox visible depending on ShowInViews.

Maybe you would prefer to allow the combo box to show all employees and
to write code for its AfterUpdate event to advise the user if a former
employee is chosen and undo the choice.

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"Doug Glancy" wrote in message
...
| Len,
|
| Len,
|
| Thanks for your time. I was afraid it wouldn't be clear.
|
| It's all the same combobox in the same form. Basically, I want to not
show
| a name as a choice now, while still displaying that name for previous
| records. This is just on the form. The underlying data, as you
suggest,
| is fine. I just want the database users to still see the name "John"
for a
| record they entered on the form last year when John was an employee,
but not
| be able to pick "John" as a choice today.
|
| Doug
|
| ".Len B" wrote in message
| ...
| It sound like you should be able to have your cake and eat it.
| However, you haven't made clear what you mean by "older entries".
|
| If you have another form or report using the same SELECT query,
| all you need to do is drop the WHERE clause. If you have a
| different SELECT query, omit ShowInViews altogether.
|
| --
| Len
| __________________________________________________ ____
| remove nothing for valid email address.

As you found, if you remove the item from the combo's RowSource, and the
bound field is hidden, the combo has no value to display so shows as blank.

One approach is to leave the inactive people in the RowSource, but sort them
to the bottom. Typically the RowSource would be something like this:

SELECT StaffID,
Staff.Surname & ", " + Staff.FirstName & IIf([Inactive], " (inactive)",
Null) AS FullName
FROM Staff
ORDER BY Inactive DESC, Surname, FirstName;

If that's not possible, you could use a query as the source for your form,
adding the Staff table to the query so you have the FirstName and Surname
fields available. Now you can place a text box on top of the combo (a bit
narrower so the combo's drop-down still appears at the right), and the text
box can show the staff name (even if it's not in the combo's RowSource.)

In the text box's GotFocus event, SetFocus to the combo. It still goes blank
when the combo gets focus, but at least it shows for all all other rows
(even in a continuous form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Doug Glancy" wrote in message
...
I have a form, frmMain that lists staff names in a combobox from a query
into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and
ShowInViews. The RowSource Query for the combobox is:

SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE
(((lkpStaff.ShowInViews)=True));

The combobox ControlSource is the field Staff in tblMain which is
populated from frmMain.

By unchecking ShowInViews for staff that are no longer employed I can
remove them from the combo's dropdown. However, of course, it also
removes those staff names from older entries. I want to have my cake and
eat it too, i.e., to remove the staff person as a choice going forward,
while still retaining them in older records.

I hope that makes sense and thanks in advance.

Doug

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4852 (20100209) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Allen and Len,

Thanks to both of you. I will take a look at using a textbox.

Doug

"Allen Browne" wrote in message
...
As you found, if you remove the item from the combo's RowSource, and the
bound field is hidden, the combo has no value to display so shows as
blank.

One approach is to leave the inactive people in the RowSource, but sort
them to the bottom. Typically the RowSource would be something like this:

SELECT StaffID,
Staff.Surname & ", " + Staff.FirstName & IIf([Inactive], " (inactive)",
Null) AS FullName
FROM Staff
ORDER BY Inactive DESC, Surname, FirstName;

If that's not possible, you could use a query as the source for your form,
adding the Staff table to the query so you have the FirstName and Surname
fields available. Now you can place a text box on top of the combo (a bit
narrower so the combo's drop-down still appears at the right), and the
text box can show the staff name (even if it's not in the combo's
RowSource.)

In the text box's GotFocus event, SetFocus to the combo. It still goes
blank when the combo gets focus, but at least it shows for all all other
rows (even in a continuous form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Doug Glancy" wrote in message
...
I have a form, frmMain that lists staff names in a combobox from a query
into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff
and ShowInViews. The RowSource Query for the combobox is:

SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE
(((lkpStaff.ShowInViews)=True));

The combobox ControlSource is the field Staff in tblMain which is
populated from frmMain.

By unchecking ShowInViews for staff that are no longer employed I can
remove them from the combo's dropdown. However, of course, it also
removes those staff names from older entries. I want to have my cake and
eat it too, i.e., to remove the staff person as a choice going forward,
while still retaining them in older records.

I hope that makes sense and thanks in advance.

Doug

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4852 (20100209) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4854 (20100210) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4854 (20100210) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com