Combobox dilemma

[Saylindara]

I made the mistake in a post I made earlier today in saying that something
was working fine - no sooner said than I encountered a problem.

I have a main form Event and a subform which lists all the employees who
have participated in that event and the date etc. The employees are selected
via a combobox. I only want the combobox to show current employees so I
created a query to do that. Unfortunately, the employees already listed on
the subform disappeared when they disappeared off the combobox. How can I
keep a history of all employees past and present who participated in an event
but have the combobox show only current employees?

 

[John Spencer]

That would require TWO controls on a continuous form.

You can stack the controls on top of each other.

The simplest method is to change your subform's source so that it includes the
employee name (Not always possible to do if you need to have an updatable source).

Add a textbox (name txtShowName) and assign the employee name to the control's
source.
Place the textbox over the combobox. (You may have to use the format bring to
front to make sure the textbox is on top of the combobox.

Add code in the textbox's GotFocus event to set the focus to the combobox
Me.NameOfCombobox.SetFocus
That will automatically move the focus off the textbox.
ALso set the text box's (txtShowName) tab stop property to NO. That will
prevent you from getting into a situation where you tab out of the combobox
and get sent to the textbox which automatically sends you back to the combobox.

Post back if your subform's record source becomes not updatable then you can
use two comboboxes stacked on top of each other. One combobox with ALL
employees (cboxShowName) and the other combobox with just the current name.
Make sure you link both comboboxes to the same field.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Wayne-I-M]

Hi

Create a new query showing only those current employees and base your combo
box on this. You could use the date they left the company (use a criteria
IsNull in the date left company column) or some other method to filter the
query.

 

[Saylindara]

I had exactly that query. And as soon as I put in an employment finish date
the name disappeared from the list of courses they had completed as well as
the combo box. I need to keep their history intact after they leave but the
combo box to only show current employees for future entries.

 

[Saylindara]

I shall have a drop of whisky in my tea and then study this carefully.

 

[Douglas J. Steele]

If you share a wee dram, I had a sample of what John's talking about in my
February, 2006 "Access Answers" column in Pinnacle Publication's "Smart
Access". See http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Saylindara]

Thank you, I'll look that up

If you share a wee dram, I had a sample of what John's talking about in my
February, 2006 "Access Answers" column in Pinnacle Publication's "Smart
Access". See http://www.accessmvp.com/DJSteele/SmartAccess.html