Combobox: Limit Rowsource but not what's displayed in the box

[RobRoy]

I have a table, say Employees, as my Rowsource, a Combobox stores their UniqueID into a job table.
Over time employees leave so a Boolean field in the Employees table is used to flag them as Current.
The intent here is so employees who no longer work at the plant cannot be selected by mistake.
That part, no problem. The Rowsource query's WHERE clause handles that.

The problem is going back to old jobs the employee's name doesn't display in the Combobox and I need it to.
I've tried fudging the Rowsource query using a different Events to change the Rowsource query and while it works


from a user perspective it's all quite unreliable. The Combobox is in a continuous forms subform, in fact there's two per row
in the subform and both need to work this way. With just one when I tested it, my solution seemed workable but with two
it becomes a mess.

[rpeare]

I think what I'd do is something a little sneakier have two controls on top of one another, one for all employees, one for just active employees, then just play with the visible property based on either the age of the job or if you have a 'closed' indicator (job completion date for example) base the visible property on that. I'm not sure it would work too well on a continuous form (I haven't done testing) but it might give you the result you want.

[RobRoy]

I think what I'd do is something a little sneakier have two controls on top of one another, one for all employees, one for just active employees, then just play with the visible property based on either the age of the job or if you have a 'closed' indicator (job completion date for example) base the visible property on that. I'm not sure it would work too well on a continuous form (I haven't done testing) but it might give you the result you want.

That idea did occur to me too however I think I would have run into much the same problem , how to trigger switching the Visible property.

After a sleep I realised my whole approach is flawed, even if it could be made to work going back to an old record it would not be possible to assign an employee that's left to the job as the Valid flag can be only true or false. What I've done is replace the Valid flag with ValidUntil of type Date. Then the query that populates the Combo Box has a WHERE JobDate < ValidUntil clause and that works very well.

The only downside is I have to force a refresh as the user scrolls through the records using OnCurrent which I never like doing as the backend database can be on a remote server.

[rpeare]

There's another option too.

If you create your form as an unbound form. Unbound forms can't be continuous so you'd have to be able to have some sort of search function but to me it's far, far easier to control what is displayed on the form with unbound controls, though it does require a lot more coding to make it work. If you're attached to the continuous though you're right you're going to have a very hard time making sure it updates correctly (if it's even possible) as you're mousewheeling through the records.

[RobRoy]

There's another option too.

If you create your form as an unbound form. Unbound forms can't be continuous so you'd have to be able to have some sort of search function but to me it's far, far easier to control what is displayed on the form with unbound controls, though it does require a lot more coding to make it work. If you're attached to the continuous though you're right you're going to have a very hard time making sure it updates correctly (if it's even possible) as you're mousewheeling through the records.

My searching did find that option however as you say that's a lot of work and it's now working quite well even on an old 386 running Win2K with Access 2000.
The main data entry / edit form now has around 10 tabs and many of those have subforms with multiple combo boxes per record. Some of the combo boxes are pulling values from tables with 50K records. The main table behind the overall form at some users sites has over 150,000 records in it and over a WAN. The wonder isn't how well it works, it's that it works at all