General combo box question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm wondering how others deal with the following situation: I have a query
driven combo box that pulls options from a table. The options table has an
autonumber field used as an index that ties to a master table, a description
text field and an "active" yes/no field. Over time some of the options become
obsolete. I put a field in the table as to whether this option is still
active and I only fill the combo boxes with those active options. In the
meantime, previous historical records that used the now obsolete option will
come up blank if I don't include that now inactive row in the combo box.

What I'd like is to be able to do is display an old option in the field in
an old record while not allowing the user being able to select (or even see
that it's a choice) for that obsolete option anymore in any other records.

Is there an approach for solving this issue? Or am I'm simply clueless?
 
What I'd like is to be able to do is display an old option in the field in
an old record while not allowing the user being able to select (or even see
that it's a choice) for that obsolete option anymore in any other records.

Is there an approach for solving this issue? Or am I'm simply clueless?
Click to expand...

It's a bit tricky (kludgy some might say) but it is doable.

Carefully superimpose a textbox over the text area of the combo box.
Either base the Form on a query joining the Options table, and set the
control source to the option field; or (less efficiently) set the
Control Source of the textbox to =DLookUp("OptionText", "Options",
"[OptionID] = " & [OptionID]) using your names of course.

In either case, set the Tab Stop and Enabled properties of the textbox
to No, Locked to Yes.

When the combo is dropped down it will pop out in front, displaying
just the active values; when it's not, it will be hidden behind the
textbox showing all the option values.

John W. Vinson[MVP]
 
I can see that working okay. Now I wonder if there's a method I could use in
datasheet view as I use that mode often in subforms...

John Vinson said:
What I'd like is to be able to do is display an old option in the field in
an old record while not allowing the user being able to select (or even see
that it's a choice) for that obsolete option anymore in any other records.

Is there an approach for solving this issue? Or am I'm simply clueless?
Click to expand...

It's a bit tricky (kludgy some might say) but it is doable.

Carefully superimpose a textbox over the text area of the combo box.
Either base the Form on a query joining the Options table, and set the
control source to the option field; or (less efficiently) set the
Control Source of the textbox to =DLookUp("OptionText", "Options",
"[OptionID] = " & [OptionID]) using your names of course.

In either case, set the Tab Stop and Enabled properties of the textbox
to No, Locked to Yes.

When the combo is dropped down it will pop out in front, displaying
just the active values; when it's not, it will be hidden behind the
textbox showing all the option values.

John W. Vinson[MVP]
Click to expand...
 
I can see that working okay. Now I wonder if there's a method I could use in
datasheet view as I use that mode often in subforms...
Click to expand...

MUCH harder, if possible at all... :-{(

I avoid datasheets: they provide both too little (for the developer)
and too much (for the user) flexibility. I've had users complain "the
subform isn't showing the <blah blah> field" - to find that they (or
another user) had shrunk it down to a pixel wide.

A Continuous Subform can be made to look very much like a Datasheet if
you like the appearance, and will allow the combo/textbox trick.

John W. Vinson[MVP]
 
Back
Top