Can a combo box display more data than what's in drop down list?

[Eko]

I have a form for entering service records, and on it I have a combo box with employees' names. The RowSource for that combo box is as follows:

SELECT [tblPersonnel].[EmployeeID], Trim([FirstName] & " " & [LastName]) AS Expr1 FROM tblPersonnel WHERE ((([tblPersonnel].[Status])="Current"));

My "problem" is that when I go back to old records which have names of former employees (their status in tblPersonnel used to be "current", but now is "former"), that combo box is empty (obviously because RowSource is narrowed down to display only current employees). Is there a way to make this combo box show only current employees' names in the drop down list, but still display names of former employees when viewing (old) existing records?

This may sound like a weird request but it actually makes sense. I want to be able only to select current employees when entering new data, but still need to be able to see it if an existing record in that combo box contains name of a former employee (that record would have been created before, when that employee was "current" but has become "former" since then).

Thanks in advance...
Hank

 

[jsv2002]

Why not have another control on the form that shows who the record was created by that way your combo is restricted to current list but you can still display historic data?

 

[evanscamman]

Here's an idea:

2 Combo Boxes - 1 hidden, 1 visible. When you enter the visible one, it unhides the 2nd one and switches focus transparently.

Evan

 

[speakers_86]

I like it evan.

 

[namliam]

SELECT [tblPersonnel].[EmployeeID], Trim([FirstName] & " " & [LastName]) AS Expr1 FROM tblPersonnel WHERE ((([tblPersonnel].[Status])="Current"));

My "problem" is that when I go back to old records which have names of former employees (their status in tblPersonnel used to be "current", but now is "former"),

How about playing with the SQL? Looking like:
Where Status = Current
OR [yourform displays old record, which needs former employee's]

Another way (if you have this date) might be to play with dates they leave....
I.e. If someone leaves Jan 1st, on any (old) project lasting beyond Jan 1st he is displayed while any project finished prior to Jan 1st he isnt shown.

 

[Eko]

Thank you guys very much for your suggestions.

I'm most attracted to the idea of modifying SQL somehow, but not sure how exactly to fix it. Namliam, what exactly did you suggest to put in place of [yourform displays old record, which needs former employee's]? If I add "Where Status = Current OR Former" than it'll just display both current and former employees' names in the drop down list, which is not what I want.

And, I don't use dates when status changes (I could add it, though), but I think it would have to work the same way like it would with "status" data, if I knew how to construct it.

I also thought of creating an event procedure that would dynamically change the SQL string, but none of the existing events work. I wish there was an "OnDropDown" Event. That would solve the whole issue.

I might have to go with a hidden combo box.

Thanks again

How about playing with the SQL? Looking like:
Where Status = Current
OR [yourform displays old record, which needs former employee's]

Another way (if you have this date) might be to play with dates they leave....
I.e. If someone leaves Jan 1st, on any (old) project lasting beyond Jan 1st he is displayed while any project finished prior to Jan 1st he isnt shown.

 

[namliam]

What about the "On focus" or "on clicked" events??

As for the "[yourform displays old record, which needs former employee's]", I dont exactly know... How would you be able to tell it is an older project that needs the old employees that have already left the company??

Only you can answer this question, not me...

The point is you dont want to see employee's from 5 years ago on a project that is only a month old... I think working with dates is the best way to go about this...