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

 

[Arvin Meyer]

Charlie,

The most likely reason that they don't show up is that they are being
excluded in the RowSource. If you can't figure out where, try posting the
RowSource SQL.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley

 

[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.

 

[Jon Lewis]

You can't do this with a combo of more than one column (e.g. as in your case
the (presumably) hidden EmployeeID bound column and the displayed Employee
one). A single column combo will actually give the behaviour you want but
typically this is not a feasible solution as you can't store something like
the Employee as there might be more than one of the same name.

So you can try something like a separate text box box positioned over the
text box portion of the combo. The combo would be unbound and it's
AfterUpdate event would update the value of the text box. This approach is
a bit messy though so way is to constuct the RowSource of your combo to show
Active items first then inactive ones. There's various ways to do this,
perhaps by having an additional displayed column with say 'x' for inactive
items. You can sort the RowSouce by this active flag to give all the active
items at the top of the list.

HTH

Jon

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).