Combo Filter < All> or Some

Combo Filtering

You can set this up yourself by following the instructions below, or you can study the example file which can be downloaded from the Nifty Digital downloads site for a few dollars.. will normally appear as the first entry in your list because the angle bracket puts it at the beginning of most “Sort Orders”, however if you have strange characters in your combo-box it might not appear in the right place, you might have to use some other format.

Combo Filter < All> or Some

Combo Filter < All> or Some

Video 1 (2:26)

The “Combo Filter < All> or Some” functionality is demonstrated in the Video.

Basically the Combo Box selection filters the records. If you don’t make a selection, then the criteria of the Subform is adjusted by VBA to allows all records to be shown..

Video 1 (2:26)

Nifty Access YouTube Thumb Nail
Play Video

… …

Instructions in Text and Pictures

I have reproduced some slides taken from a presentation. This first one is a picture of the working form. Most people get to the stage of adding three combo boxes or more, the combo boxes control the selection appearing in the subform, however they have a problem when a combo boxes left empty, instead of the expected result of returning all available options for that combo box, none are returned.

You may notice in the Picture below that the combo boxes display the word: <All> This is added by using a union query, union queries are not available from the MS Access query designer grid, you have to write them manually.

The Picture below is of the same form in design view, the left hand combo box property sheet has been opened showing the combo box row source displayed in the zoom window. Notice it is basically a select query with the addition of the word “distinctrow” and the final part which begins union select.

Below is the combo box row source (incorrectly marked as “Record Source” in the pictures below)

In this picture below you can see the parts which have been added to a basic select query, “distinct row” just make sure the query only returns one item, where the table may have several items that are the same name. And below, the “union query” which adds a Star “*” to the left hand column of the query results and the text”” to the right hand column of the query results. However the combo box only displays “” because although the first column is present in the combo box it is hidden by the combo box settings.
.

The picture below just shows a basic select statement, it’s not used in the example it’s here to show you what the union query is based on.

The picture below shows the query designer grid for the query “qryMup” there’s only enough room to show the criteria for the first to columns, but the third column would be very similar to the first two if it was displayed. You may notice that this criteria contains a function.

Below is the full criteria of the first combo box, notice the function “fCboSearch”

Below is the full SQL of the query “qryMup”

The picture below is of the function “fCboSearch” basically what happens is the combo box result is passed in as the variable “vCboSearch” and processed. This function is designed to detect if the combo box is empty, in which my case it may contain a Null value or it may contain what is termed a zero length string “”. If it contains either of those then they are replaced with the “*” which instructs the query to return all the results for that combobox election.

… …

Add "(All)" to a Combo Box or List Box

I found this code on the Microsoft website completely by accident. https://docs.microsoft.com/en-us/office/vba/access/concepts/controls/addallto-a-combo-box-or-list-box I haven’t used it myself, and I know hardly anything about it, except it does look overly complicated! I’ve posted it here because it’s intriguing! When I get a minute I’m going to have a go with it. If you have used it, then please let me know how you got on with it. You can find me here on (AWF) Access World Forums. https://www.access-programmers.co.uk/forums/members/uncle-gizmo.14645/ Please note:- You will need to sign in to (AWF) Access World Forums before you can contact me through that link

… …

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.