Combo Box not showing old records

[Charlienews]

Hi,

I have a combo box that looks up a query that shows the current employees,
however, the box on older records no longer shows the names of older members
of staff although the data is in the control source. I don't really want to
link the combo box to the entire list of employees as it is very long but I
do need it to show those names where previously relevant.

Any help would be great.

Thanks in advance

Charlie

 

[Charlienews]

Hi Arvin,



SQL is:

SELECT [Active Employee].EmployeeID, [Active Employee].Employee FROM [Active
Employee];



Thanks



Charlotte

 

[John W. Vinson]

Hi Arvin,



SQL is:

SELECT [Active Employee].EmployeeID, [Active Employee].Employee FROM [Active
Employee];

So... I presume that the table or query named [Active Employee] contains the
names of <ahem> active employees, and the combo is showing exactly what's
there to show. Right?

Where are the names of no-longer-active employees stored? It sounds like
you're making contradictory requirements: you want to show inactive employees
but you don't want them included in the combo?

Please explain how your employee data is stored, and how you want this combo
box to work.

 

[Charlienews]

Hi John,

The 'Active Employee' query is fed from the 'Employee' Table via a tick box.

I would like the field to show any record that has been previously entered
but only show the active employees in the dropdown.

Thanks

Charlie

Hi Arvin,



SQL is:

SELECT [Active Employee].EmployeeID, [Active Employee].Employee FROM
[Active
Employee];

So... I presume that the table or query named [Active Employee] contains
the
names of <ahem> active employees, and the combo is showing exactly what's
there to show. Right?

Where are the names of no-longer-active employees stored? It sounds like
you're making contradictory requirements: you want to show inactive
employees
but you don't want them included in the combo?

Please explain how your employee data is stored, and how you want this
combo
box to work.

 

[John W. Vinson]

Hi John,

The 'Active Employee' query is fed from the 'Employee' Table via a tick box.

I would like the field to show any record that has been previously entered
but only show the active employees in the dropdown.

This can be a bit awkward: you want the employee name visible when the combo
is *not* dropped down, but not visible when the combo *is* dropped down. With
one control there's really no way to do this!

The trick is to use a small textbox carefully superimposed on the "text"
portion of the combo box. This textbox should have a control source such as

=DLookUp("[LastName] & ', ' & [FirstName]", "Employees", "[EmployeeID] = " &
Me!EmployeeID)

looking in the actual employees table, not the Active Employees query. The
textbox should have Enabled = No, Locked = Yes, Tab Stop = No, and (if need
be) you can use Format... Move to Front to put it in front of the combo. The
active employee list will become visible when the combo is dropped down, but
the DLookup name will be shown when it's not.

 

[David W. Fenton]

This can be a bit awkward: you want the employee name visible when
the combo is *not* dropped down, but not visible when the combo
*is* dropped down. With one control there's really no way to do
this!

I disagree.

The way I handle this is by using criteria in rowsource of the combo
box that is (IS ACTIVE or ID=CurrentFormID). That means you have to
requery the combo box in the OnCurrent of the form, and that you
have to check the ACTIVE value in the BeforeUpdate event and prevent
the user from choosing it. I prefer this to mucking about with
multiple controls.

 

[John W. Vinson]

I disagree.

The way I handle this is by using criteria in rowsource of the combo
box that is (IS ACTIVE or ID=CurrentFormID). That means you have to
requery the combo box in the OnCurrent of the form, and that you
have to check the ACTIVE value in the BeforeUpdate event and prevent
the user from choosing it. I prefer this to mucking about with
multiple controls.

Thanks, David - that sounds like another good option, I'll try it out. It may
well work better for some of my forms.

Wouldn't this have the disadvantage that the user would still *see* inactive
records, and perhaps get frustrated at being unable to select them? I wonder
if it would be possible instead to change the rowsource in the combo's
gotfocus event, so it shows all records when it does not have the focus, but
only active ones when it does?

 

[David W. Fenton]

Thanks, David - that sounds like another good option, I'll try it
out. It may well work better for some of my forms.

Wouldn't this have the disadvantage that the user would still
*see* inactive records, and perhaps get frustrated at being unable
to select them? I wonder if it would be possible instead to change
the rowsource in the combo's gotfocus event, so it shows all
records when it does not have the focus, but only active ones when
it does?

I'm not sure what issue you're talking about. It would show only
ACTIVE records EXCEPT for the value in the current record. Why would
you then choose the same value again? If you're editing the value,
it's surely because you want to change it, so you wouldn't choose
the one that's already there. And if you do, the BeforeUpdate will
kick in and tell you that you can't do that because it's INACTIVE.

I do generally include the ACTIVE column in the rowsource (using a
padded X as a replacement for a checkbox).