How can I get a Combo box to display values not in it's Row Source Query?

[Razor68]

I've got a DB with lots of legacy options for fields that are linked via ID to other tables. For exampled, field "ProjectID" in the "Test Requests" table linked to table "Projects" via field "ID" in the Projects table. These source tables have another field, boolean "Active", that is used to prevent people from selecting legacy values for new records. The headache I'm running into is that I want people to see the Project Name for old Projects that can no longer be picked in the Combo Box, but if I set the Row Source of the Combo to require that "Active" be TRUE, then the Combo Box is blank when they go to these records.

Right now I'm working around this limitation by having an unbound Text Box control placed on top of the Combo Box with code handling refreshing it from the Projects table as people switch between records, plus more code handing what happens when people click on this text box, etc., which is all very clumsy IMHO. Fortunately our datasheet forms and continuous records forms all have these fields locked against being changed so I don't have to filter on the Active field in them, but I could see that changing.

Is there a better way of doing this? What I really need is a Combo Box with "Row Source" separated into "Display Source" and "Selection Source" options, so I can display everything while limiting the selection. Does such a thing exist?

Thanks,
Erik

[ranman256]

you dont. Thats what the query is for.

[Bulzie]

Not sure if I understand whole issue but can you have your combo box show the project name and the Active field, then in the AfterUpdate event on that combo box, check if that project selected is Active Me.SelectProject.column(1) = "Active" and if so then go to update form or if not, then maybe display message saying cannot be updated.

[Gicu]

What you need to do is have two queries for the row source of the combo. The first one is to include all records (your "Display Source")and that is the one saved in the row source property of the combo box control. The second one only shows your "active" records (your "Selection Source") and you use the Enter event of the combo box to switch to it when someone wants to edit it: Me.cboMyCombo.RowSource= "qrySelectSource".
Cheers,

[Micron]

You could also use an option frame that defaults to one or the other when form opens. To see the other list, choose the other option and reset the combo rowsource property. The options might be more intuitive to a user.

[Razor68]

Thanks for the suggestions!

I think Gicu hit the nail on the head for the easiest alternative to what I'm doing now. The only headache will be if they stay on one of these fields while cycling through records, but then I could check for control focus and maybe set the focus elsewhere. Not ideal but it should be sufficient, and I suspect most users go from field to field using the mouse if they're looking at older records anyhow, so it won't be too annoying if I reset the focus to a particular field on a record change.

Bulzie's suggestion to show everything then reject changes that aren't Active might be a bit annoying as more and more projects age out (over 80% have already as this database goes back over two decades), unless I sort by the Active field followed by the Project Name, so at least they're not scrolling down past numerous dead projects.

Micron's idea would probably annoy my users unfortunately - they'll see the Combo Box as blank while searching through records unless they remember to switch over to the "Show All" Option. Plus, I'll still need to have code verify they didn't pick an invalid option while set to "Show All".

Overall I'd like the users to get the functionality as transparently as possible, so switching the row source seems like the best of the options offered so far, though I'll have to try it and see. It should work great for any form that's not Continuous or in Datasheet view, though I was really hoping that someone knew of an option that would extend to all form views. Ah well - you can't have everything!

Thanks,
Erik

[Gicu]

You can reset it to all (display) in the OnCurrent event of the form if you are worried about cycling through records.

[Micron]

they'll see the Combo Box as blank while searching through records unless they remember to switch over to the "Show All" Option. Plus, I'll still need to have code verify they didn't pick an invalid option while set to "Show All".

No, I think I said it would default to one or the other. Which one depends on which one you'd determine to be the default.

[ArviLaanemets]

I have used combination of combo and unbound text box like described by you for this. Only the unbound text box contains a DLookup formula to display e.g. project name. The text box covers whole combo except activation arrow at right, and is frontmost one. I don't remember, was refreshing textbox from AfterUpate event needed or not, but this will be only piece of coding possibly needed.

You also can use form's OnCurrent event e.g. to disable combo when project is inactive - so you don't reassign the record of some old project to new project accidentally.