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

Hi Arvin,



SQL is:

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



Thanks



Charlotte

"Arvin Meyer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%b4Tn.69781$nz1.5861@hurricane...
>> 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
>>
>
>
>

On Sat, 19 Jun 2010 18:07:19 +0100, "Charlienews"
<(E-Mail Removed)> wrote:

>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 [MVP]

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
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Sat, 19 Jun 2010 18:07:19 +0100, "Charlienews"
> <(E-Mail Removed)> wrote:
>
>>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 [MVP]
>

On Mon, 21 Jun 2010 07:15:30 +0100, "Charlienews"
<(E-Mail Removed)> wrote:

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

John W. Vinson [MVP]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On Mon, 21 Jun 2010 07:15:30 +0100, "Charlienews"
><(E-Mail Removed)> wrote:
>
>>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!

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 22 Jun 2010 01:14:38 GMT, "David W. Fenton" <(E-Mail Removed)>
wrote:

>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?
--

John W. Vinson [MVP]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On 22 Jun 2010 01:14:38 GMT, "David W. Fenton"
> <(E-Mail Removed)> wrote:
>
>>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?

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/