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 recordsource 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.
.
