Filtering a Continuous Forms Combo Box with a Meaningless Bound Column (1 Viewer)

In my databases in the past I always used "Real" fields as Primary Keys, but recently I decided (reluctantly) to convert to the arguably "more conventional" method of using meaningless autonumbers as Primary Keys - (but only when the Real key field is longer than, say, 5 digits). There's no Orphan problem if you use referential integrity properly (cascades etc). But I'm not here to rant - another time!

But these autonumber primaries have made life difficult in this situation:

I have a continuous forms subform with a bound combo box for Documents and 3 unbound combo boxes for Company, Discipline and Document Type, which I use to filter the Documents combo (otherwise its row list would be far too long).

In Access, the Rowsource property of a combo box not only controls what you can select, but also what is displayed (grrr). Also, when filtering the rowsource of a combo box in a continuous forms subform, if the bound column is not also the visible column, the filter only works for the first sub-record in the series and the combos in the remaining sub-records go blank. I don't remember reading that on the tin!

Even if you include the subrecord's ID in the rowsource (...OR cboDocID = ‘” & Me.DocID & “’ etc) it still only works for the current subrecord. That is not the case if the bound column is visible - I tested this (all other factors being equal) and then all records DID show (their meaningless numbers).

The Limit to List property could not be turned off either (greyed out), because to apply Limit to List, the bound column must be the visible column (for reasons best known to Microsoft).

Because the bound column of the combo is this meaningless autonumber, I have to display a user-friendly column (not the bound column). So my way round this was to apply the filter only at the time of selecting the Document combo - on Got Focus. Then the event sub checks if any or all of the 3 unbound combos is populated and if so, modifies the rowsource accordingly. Then on Lost Focus, the rowsource is returned to the way it was.

It does work, but it’s so clunky – surely there must be a better way. My tyrannically tidy mind hates inelegant solutions! But am I missing something obvious here?

Orthodox Dave said:

In Access, the Rowsource property of a combo box not only controls what you can select, but also what is displayed

Click to expand...

No, not true...

You can control what a combo box displays by altering the column width see item 38 in this Google document

Create a Form With Basic Combo Box Lookup

and the second video on this page from Time index 3:40:-

Create a Form with Basic Combo box Lookup

Reading the rest of your question, I suspect you already know about the column width property!

Re:- "surely there must be a better way..."

I'm not sure it would work in your situation but it might be worth a try. I have a special form for handling combo boxes.

A common problem with combo boxes is people attach them to too much data and they become a bit unwieldy.

I created a special pop-up form which allows you to limit the list of Records Returned ( within the popup form)..

Once you make a selection it passes the reference to that selection back to the original combobox.

There's a video of my combo box form here:- Combo-box Alpha List if you want a free copy of it email me at mailtonyhine@gmail.com and I will explain how you can get a free copy...

@Tony
I looked at your video. What a great feature you created.

Thank you Allan...

If you wanted to have a look at it then I'd be happy to send you a copy for free as well Allan. If you email me, I will explain how you can get a free copy...

Thanks for your efforts Tony (Gizmo). You're right I knew about the column width.

Your video showing how to limit the combo using your alpha list form was interesting and innovative, but not really suited to my particular application. My 3 unbound combos do the job of limiting the list from, possibly several thousand, down to 10 or 20 documents. Engineering documents (which is what these are) are easily categorised in this way, whereas people need a more nuanced approach (like yours).

Making the adjustment from meaningful to meaningless primary keys has been a bit of a learning curve. In my previous version, the document reference number was the primary key, so could be shown as the bound column, with none of the problems I get now. But these reference numbers can be 20-30 string characters, translating to up to 40 bytes vs Long Integer 4 bytes. As the Documents are the most manipulated data in the database, this can seriously impact processing speed.

So I suppose I just need to swallow the medicine!

Orthodox Dave said:

The Limit to List property could not be turned off either (greyed out), because to apply Limit to List, the bound column must be the visible column (for reasons best known to Microsoft).

Click to expand...

Are you sure about this? I'm certain I've never seen or encountered this restriction.

As a caveat - I very rarely use combo's that aren't limited to the available data, and have used ID fields for ever as SQL server based data is just simpler to manage that way..

Originally Posted by Orthodox Dave
The Limit to List property could not be turned off either (greyed out), because to apply Limit to List, the bound column must be the visible column (for reasons best known to Microsoft).

Click to expand...

To confirm what Minty said that's DEFINITELY not true (Access 2010)
Set the width of the bound column to zero so it is hidden & Limit to List = Yes

BUT I think you know this already

Sorry but this is true - of Access 2016 at least (not greyed out but restricted nonetheless). See screenshot attached.

I may have given the wrong impression - that Limit to List was not available if the bound column is not visible. I meant that you can't turn it OFF unless the bound column is visible. The whole point was to stop the RowSource restricting what was shown.

I've just retested in Access 2016 & its definitely not true for me.



It would be such a stupid restriction that even MS wouldn't do it.
I suggest that something else in your form(s) is causing this behaviour.

EDIT: It would be such a stupid restriction that even MS would do it.
Sorry - you're right - you can't change Limit to List from Yes to No in Access 2016 for either single forms or split forms
Similarly in Access 2010 - very odd - it must be deliberate if its lasted several versions

I'll investigate further...

Your screenshot shows Limit to List showing "Yes". Try turning it off.

Posts crossed - just did so - see edit to last post!

Thanks Colin I'll owe you a cider if you get it!

I think I now get what you are trying to do, and you are dead right - Access 2010 has the same restriction.

The simplest (messy) way around your problem is probably to only restrict the displayed items when you are editing or adding one of the sub form records. So create a second combo or display textbox with an unfiltered record source, and place it over the top of your filtered one, lock it for editing. This will always display the selected item, in the continuous form..

If someone clicks in the unfiltered combo, move focus to the hidden filtered one to allow your data entry. A bit messy but should get you where you want. Or go with a altered version Tony's solution.

Thanks Minty. That is a solution, although messy as you say.

Probably Access designed the combo box when primary key fields were meaningful and expected to be displayed. Then when they recommended using autonumbers, it was too late to redesign the combo box because of incompatibility with older databases. That's my guess anyway.

Just retested this in A2010/2016 & realised that the hidden column is either a PK field or being treated as such for indexing

Try this

1. To switch off Limit To List, temporarily set the bound column width to >0
2. Change column width back to 0. Limit to List resets automatically to Yes

Now I think about it, it DOES make sense

Sadly even though I'm less than 1km from Thatcher's cider in Sandford, I hate the stuff ... but thanks anyway
Even more sadly I gave up all alcohol at the end of January!

Thanks Colin,

I tried what you said and, yes, Limit to List did indeed switch back to Yes.

Well done on keeping up your new year's resolution by the way! Having lived my early life on a farm in Somerset (Near Exmoor), I still love that part of the world.

3 weeks later I sort of cracked it - good enough for me anyway. I basically added an OR criteria expression to the combo row source query to include any record where the subform join field (Master / Child) is the same in the current subrecord.

So now the combo box list includes all the filtered items Plus all the items already in the subform. It's not perfect, but the list is short enough to be manageable. And crucially all the records are now displayed.

Thanks to all who helped earlier.