Select search criteria from 3 combo boxes

Robert B · Feb 6th 2006

Hi All

I have a requirement to use three combo boxes to successively refine the selection of records. I recently saw a solution by A9192Shark which I have attempted to adapt to my case.

It works up to a point but in the end selects on only one parameter, and then occasionally throws up an error.

Code

Private Sub BuildFilter()
    strFilter = ""
    If IsNull(Me.cmbCode) Then
        strFilterCode = ""
      Else
        strFilterCode = "(Code = '" & Me.cmbCode & "')"
    End If
    
    If IsNull(Me.cmbDescription) Then
        strFilterDescription = ""
      Else
        strFilterDescription = "(Description = '" & Me.cmbDescription & "')"
    End If
    
    If IsNull(Me.cmbBand) Then
        strFilterBand = ""
      Else
        strFilterBand = "(Band = '" & Me.cmbBand & "')"
    End If
    
    If strFilterCode <> "" And strFilterDescription <> "" And strFilterBand <> "" Then
        strFilter = strFilterCode & " AND " & strFilterDescription & " AND " & strFilterBand
      Else
        strFilter = strFilterCode & strFilterDescription
    End If
    
    With Me.subfrmP_OpalBuy.Form
        .FilterOn = False
        .Filter = strFilter
        .FilterOn = True
    End With
    Me.txtFilter = IIf(strFilter = "", "No filter", strFilter)
    Me.Requery
End Sub

Display More

Can someone see where the error/s might be?

Thanks to A9192Shark for getting me to this point, which is a whole lot closer than I would have been starting from scratch

Regards

Robert

A9192Shark · Feb 6th 2006

Re: Select search criteria from 3 combo boxes

Robert,

The problem will be in this part of the code. What this says is:

If I have a value in all three combo boxes then do something, else do something else.

However the something else does not do something sensible.

Code

If strFilterCode <> "" And strFilterDescription <> "" And strFilterBand <> "" Then
        strFilter = strFilterCode & " AND " & strFilterDescription & " AND " & strFilterBand
      Else
        strFilter = strFilterCode & strFilterDescription
    End If

Assuming that you can pick 0-3 of the combo boxes then you need to code for all possible scenarios.

You can use a case statement or simply a conditional build (note that some do not like the use of IIF() as it is a 'slow' function. In this case I suspect you will not notice the difference.

Code

StrFilter = ""
If strFilterCode <>""  then strFilter = "(" & strFilterCode & ")"
if strFilterDescription <>"" then strFilter = strFilter & iif(strFilter<>"", " AND (","(") & strFilterDescription & ")"
if strFilterBand <> "" then strFilter = iif(strFilter<>"", " AND (","(") & strFilterBand & ")"

Does that help?

A.

Robert B · Feb 6th 2006

Re: Select search criteria from 3 combo boxes

Hi A.

I see what you mean, what I was hoping to achieve was not 1 of 8 possible combinations but rather a pick from each combo box that defined the final selection. The possibility of making selections in only one or two boxes I was hoping to avoid.

If this is not possible, am I right in thinking that the code you suggested should be a straight replacement for the code you quoted?

Thank you for your time and assistance

Regards

Robert

Robert B · Feb 6th 2006

Re: Select search criteria from 3 combo boxes

Hi

Having looked at my reply again I realised that the single selection could be made by removing all the "if..then..else" statements. If I were to do this I suspect that the form on open would have blank parameters and then would find no records. If this is true is there a way in which I could default the selection OnOpen to "All"?

Regards
Robert

A9192Shark · Feb 6th 2006

Re: Select search criteria from 3 combo boxes

Robert,

All of the records should be displayed if the filters are all null.

However, I think what you are saying is that the user MUST pick something from each combo box before the filter will be used. In that case your initial code will work, but could be improved slightly.

Code

If strFilterCode <> "" And strFilterDescription <> "" And strFilterBand <> "" Then 
        strFilter = strFilterCode & " AND " & strFilterDescription & " AND " & strFilterBand 
    Else 
        strFilter = ""
    End If

This would only create a filter if all three combo boxes are not null.

A.

Robert B · Feb 7th 2006

Re: Select search criteria from 3 combo boxes

Thats great, thanks for all your help it is much appreciated

Regards

Robert

Robert B · Feb 15th 2006

Re: Select search criteria from 3 combo boxes

Hi All

I would like to expand this concept by allowing the user to first select the table from which the selections are made. So an additional combo box would be added listing the available tables, the selected table would be shown and the filter created on that table. The tables are similar in that they contain the selection parameters, although they are not identical in the fields within each record.

Any help is much appreciated

Regards

Robert

Select search criteria from 3 combo boxes

Participate now!

Share