Adding * to the possible parameter values selected in a Combo Box

[Kerry]

I have created a form that is used to select filter criteria for a variety of
reports. The form has three combo boxes which have cascading dependencies
i.e., the results of the second are dependant on the selection made in the
first and the results of the third are dependent on the selection made in the
second. They are named cmbClient, cmb Entity and cmbState.

The boxes appear to function fine and the subsequent queries/reports work as
long as I have made a selection IN ALL THREE BOXES. Therein lies the
problem. I would like to allow the user to make a selection in only the
first combo box, cmbClient and not have to touch the other two.

I first tried setting the default value of cmbEntity and cmbState to * to no
avail. I then tried using an IIF statement in the queries to add * as a
parameter when cmbEntity or cmbState are null but I receive and errror
indicating the query was too complicated to resolve. Last, I modified the
queries on which cmbEntity and cmbState are based to include an additional
record with * (as the ID field) and ALL (as the display value) so that the
user could select ALL as an option. THis too produced no results.

Eventually I would like to allow the user to select any combination of the
three combo boxes but for now, I'll settle for getting the thing working
better than requiring a selection in all three.

Any suggestions would be appreciated.

 

[Klatuu]

Here are a couple of examples from one of my apps:

It involves using a union query. There are a couple of situations here.

In this first case, the value of the field InitPriority will be used
directly in the searching or filtering.

SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
InitPriority FROM dbo_Initiative WHERE InitPriority IS NOT NULL;

In this case, the search is done using InitStatID which is an Autonumber
primary key for the InitStatus table. The value is used as a foreign key in
the form's recordset, so that is what we want to search, but the user will
see the description. The combo is a two column combo with the first colum
bound, but hidden. The 0 is used because there should not be a 0 autonumber
value.

SELECT 0 As ID, "(All)" As Dummy FROM dbo_InitStatus UNION SELECT
dbo_InitStatus.InitStatID, dbo_InitStatus.InitStatDescr FROM dbo_InitStatus;

Now, when it comes time to set the filters, we check the values in the
combos and create the filtering string based on those values.

This same technique can be used for filtering reports as well. The way you
do that is to not put the filtering in the report's record source query
except when that filtering will always apply. For example, if you have a
table that has a field that identifies records as Active or Archived and the
report will always be for Active records only, then you put that filtering in
the query. The rest you do with this technique to build a filtering string
to use in the Where argument of the OpenReport method.

The same would apply to forms you may want to open from your current form,
or to subforms.

This example code comes from an application where the main form is a multi
tab dashboard. The code filters the records for the first tab, which will
then filter the records for the second tab which can be filtered with a
similar procedure, when filters the records for the third tab,etc.


Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard"
GoTo SetInitFilters_Exit

End Function

Here is the AddAdd function that adds the word And to the string when needed:

Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities"
GoTo AddAnd_Exit

End Function

 

[Allen Browne]

You will need to craft the WHERE clause of your query so that it returns
True if the combos are null.

Something like this:
WHERE (Field1 = [Forms].[Form1].[Combo1])
AND ((Field2 = [Forms].[Form1].[Combo2])
OR [Forms].[Form1].[Combo2] Is Null))
AND ((Field3 = [Forms].[Form1].[Combo3])
OR [Forms].[Form1].[Combo3] Is Null))


A more efficient solution would be to build the filter string for your
report dynamically, from only those boxes where the user chose a value. You
can then use the filter string as the WhereCondition for OpenReport.

For an example of how to build such as string, download the sample database
in this link:
http://allenbrowne.com/ser-62.html
The example applies a filter to a form, but it's exactly the same to filter
a report.