Combo Box on a tabular form, filtered by another field on that same record / row

This is a general question - Whenever a bound combo box in a tabular form  that is filtered on another field in that row, there I behavior that makes the data on the form difficult to use. My standard approach to this situation is to requery the combo box every time the filtered field changes. The problem is that when you look at the entire form, this causes the combo box to only display when you're on the current record. The other records show a null value, until that row receives the focus or fields are edited. I've tried using On Enter, Current, Before Update, and other procedures to query differently, but the result is always the same. So if a user is reviewing the form, they can only see that dependent combo box value when they put the focus on that record - and the others remain null. I'm thinking there has got to be a way to help this behave differently, but I suppose it's also just a limitation. Any help would be great. Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's the way a bound datasheet form works. All bound controls are really the same control, and Access manages the way they're displayed internally. There's no way to make the form behave differently when in the datasheet view.

One fix for this is to use a Continuous form, which can be configured to look almost exactly like a datasheet form, but behave much like a singular form. This would allow Access to manage each record independently, and each record would then show the combos properly "cascaded".
Dale FyeOwner, Developing Solutions LLCCommented:
I'm not sure I understand your question or what you are describing.  I agree with Scott that there are some features associated with datasheets and continuous forms which make them less than ideal for editing data.  I frequently use a separate "details" form to use for editing data originally viewed in a datasheet or continuous form.

But in the attached database (Office 2007), I have two forms, one datasheet, one continuous, and neither of them seems to react the way you are describing.
Helen FeddemaCommented:
You refer to a tabular form.  What form type is it (Single Form, Datasheet, Continuous, Pivot Table)?  That makes a difference to how some controls work.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Here is a database I created to show the problem and offer a solution.  The solution only works for continuous view.  It will not work for datasheet view.
maknitCEOAuthor Commented:
Thanks everyone for the comments - to clarify, this is a form, in form view (not layout view). The form is continuous, and tabular. So when a context sensitive (or filtered) field in the combo box references (or filters by) another field in the same row, then it's only the  value in the selected record that is used to display the underlying value in the combo box. I think this happens because Access requeries the combo box for (only) that selected record, but the reference to that field is applied to ALL of the rows on the tabular form (selected or not). So the combo box only correctly displays a value in the current row. The other non selected rows are using the selected row field for different rows, so the combo box (which is bound btw) gets confused and doesn't display anything. I've actually seen this behavior in Acces for almost a decade, but have a hard time believing that this is intended functionality. I usually just drop the field filter / criteria in the underlying row source to work around the problem, but it's not the best solution for a good user facing application.  Hope this helps clarify.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I may have misunderstood what you're referring to when you say "Continuous" and "Tabular". To me, Tabular has always equaled Datasheet, but I believe you're referring to a typical Continuous form that looks something like a Datasheet.

Several other Experts have provided you samples regarding cascading combos on  Continuous forms, but you may be setting up your forms differently. For example, if your combo isn't bound, then Access will normally reset all the visible combos when you change a value in that combo. If the RowSource of those other unbound combos is such that they do not contain the value, then you'll see the NULL indication (or some other).

I think it would be helpful is you could put together a small sample database that shows what you're referring to.
Helen FeddemaCommented:
A bound combo box on a datasheet subform will show the appropriate value for each record; an unbound one will show whichever value has been selected on the current row on all rows.  Here is one I am working on now:

Datasheet with bound combo box
Did you look at the example  I posted?  Did you not understand it?

I'll recap the problem again.  Each "row" in continuous or datasheet view is actually an instance of the form.  Access can only maintain ONE set of properties for the form.  Therefore, ALL instances of the form will have the same properties.  If you use code to change the color of a control, all instances of that control change color.  With the cascading combos, the issue is the combo's RowSource.  When you change the RowSource for the active row, it changes for all visible rows so it makes values seem to disappear.  My example shows this.  This is by design (good or bad) and is not a bug so it will never be fixed.  MS hasn't been convinced to change how forms work in 20 years and now that they are abandoning client/server, they certainly won't in the future.

In some cases such as color you can solve the problem by using Conditional Formatting which MS added as a partial solution to this issue.   Another solution is to create RowSources where you display the "key".  So instead of using a numeric ID that is hidden, use a visible text field.  The disappearing act happens because the combo isn't actually displaying the saved value, it is displaying the lookup value.  If you display what is saved, the disappearing act doesn't happen (except of course for unbound controls which can't effectively be used on continuous/datasheet forms).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
If  you want to colorize certain cells (or rows) in a datasheet subform, see my Access Archon article on this topic:

Here is a screen shot of the form:

Colorized datasheet rows
maknitCEOAuthor Commented:
Thanks to all of the posts on this submission. My apologies for the delay - I missed an update email.  I appreciate the innovative options and need to consider them more. In summary, my work around options seem to be the use of subforms (Helen) and the use of an overlapping text box (Hartman).  I think I can probably pick a solution or maybe even engineer an extended solution using these ideas, so I will flag these as such and close the submission. Thanks again for everyone's work on this.
maknitCEOAuthor Commented:
Solutions do a good job of dealing with what is a big oversight on Microsoft's part for dealing with bound / filtered controls. The solutions are enough to help me figure out an approach that works.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.