Custom row source for combo box in continuous form in Access

Question

I have searched around, and it seems that this is a limitation in MS Access, so I'm wondering what creative solutions other have found to this puzzle.

If you have a continuous form and you want a field to be a combo box of options that are specific to that row, Access fails to deliver; the combo box row source is only queried once at the beginning of the form, and thus show the wrong options for the rest of the form.

The next step we all try, of course, is to use the onCurrent event to requery the combo box, which does in fact limit the options to the given row. However, at this point, Access goes nuts, and requeries all of the combo boxes, for every row, and the result is often that of disappearing and reappearing options in other rows, depending on whether they have chosen an option that is valid for the current record's row source.

The only solution I have found is to just list all options available, all the time. Any creative answers out there?

Edit Also, I should note that the reason for the combo box is to have a query as a lookup table, the real value needs to be hidden and stored, while the human readable version is displayed... multiple columns in the combo box row source. Thus, changing limit to list doesn't help, because id's that are not in the current row source query won't have a matching human readable part.

In this particular case, continuous forms make a lot of sense, so please don't tell me it's the wrong solution. I'm asking for any creative answers.

Answer 1

I also hate Access, but you must play with the cards you are dealt. Continuous forms are a wonderful thing in Access, until you run into any sort of complexity as is commonly the case, like in this instance.

Here is what I would do when faced with this situation (and I have implemented similar workarounds before):

Place an UNBOUND combobox on the form. Then place a BOUND textBox for the field you want to edit.

Make sure the combobox is hidden behind (NOT invisible, just hidden) behind the textBox.

In the OnCurrent event fill the listBox with the necessary data. Go ahead and "Limit to list" it too.

In the OnEnter or OnClick event of the textBox give the combobox focus. This will bring the combobox to the forefront. When focus leaves the combobox it will hide itself once more.

In the AfterUpdate event of the combobox set the value of the textbox equal to the value of the combobox.

Depending on your situation there may be some other details to work out, but that should more or less accomplish your goal without adding too much complexity.