?
Solved

determine if combo box selected and filtering in vba

Posted on 2013-01-07
5
Medium Priority
?
463 Views
Last Modified: 2013-01-07
I have a form with 6 combo boxes each combo box has a range of values that I may want to filter the form on.

I might filter on all  or 1 or none.

I will need to concatenate the filters if more than one combo is selected.


so a single filter would be in this case on combo CBBodyType:

strFilter = "[transmission]=" & """" & Me.CBTransmission.text & """" & " OR isnull([Transmission])"

If more than one filter selected I would:

strFilter = strFilter & " and " & "[transmission]=" & """" & Me.CBTransmission.text & """" & " OR isnull([Transmission])"

How can I deduce if a combo box has had a value selected so I could do the following:

If combo box transmission selected = True Then
'increment counter
    Counter = Counter + 1

    Select Case Counter
 
        Case Is = 0 ' no filter applied
        Case Is = 1 ' only one filter applied
                If Not IsNull([Forms]![FrmSMMTMaster]![TRANSMISSION].[Value]) Then
                strFilter = "[transmission]=" & """" & Me.CBTransmission.text & """" & " OR isnull([Transmission])"
                 
                Else
                    Counter = Counter - 1
                End If
        Case Is > 1 ' more than one filter applied
                If Not IsNull([Forms]![cw_client_matching_form]![TRANSMISSION].[Value]) Then
                     
                            strFilter = strFilter & " and " & "[transmission]=" & """" & Me.CBTransmission.text & """" & " OR isnull([Transmission])"
                Else
                    Counter = Counter - 1
                End If
    End Select
End If
0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38751605
Not sure of your exact design here...  
(Datatypes, Control Properties, will it always be the one OR, or can this be "Multiple" concatenations,...etc)
...and I am not quite sure why you are referencing the .text property of the combobox.
...and you also seem to be referencing other form controls besides the combobox...
"[Forms]![cw_client_matching_form]![TRANSMISSION].[Value]"

A sample file would go a long way towards clearing these questions up:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38751652
Me.Filter = ""
If Not isnull(Me.CBTransmission) then
   Me.Filter = "[Transmission]='" & 'Me.CBTransmission & "' OR isnull([Transmission])"
endif
If Not isnull(Me.CBNext) then
   If Me.Filter = "" then
               Me.Filter = "([NextField]='" & 'Me.CBText & "' OR isnull([NexField]))"
   else
               Me.Filter = Me.Filter & " AND ([NextField]='" & 'Me.CBNext & "' OR isnull([NexField]))"
   end if
endif
.....

Open in new window

You can do it in some loop if your fields wil be named sequentally.
Take a look also at example from Helen Feddem's site:
http://helenfeddema.com/access.htm
#129 Fancy Filters
0
 

Author Comment

by:PeterBaileyUk
ID: 38753570
I have created a shrunk down db. each combo has its original singular filter remmed out after update now calls setfilters.

The count determines if a previous filter was set and if so creates the filter string with the strfilter and the "AND" part.

there is no control yet to ignore a filter if combo not selected.

so select vauxhall/opel and it should show just vauxhall/opel..select vauxhall and then select nom 2.4 and you get 2.4 vauxhalls. etc.
EE---2013.zip
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38753588
nailed it thx
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38753672
Hmm, looks like you already accepted a solution before I finished going through your code.  Anyways, here's your revised database.  I believe this code is much more efficient:
Function setFilters()
    Dim strFilter As String
    Dim strTemp As String
    Dim WordFilter As String
    
    If Len(Me.Txt_Search_For) > 0 Then WordFilter = fnTextFilter(Me.Txt_Search_For, Wholewords, "[concatenated Description]")
    
    'reset filter variable
    strFilter = ""
    
    'reset filter form property
    Forms![FrmSMMTMaster].Form.Filter = strFilter
    
    If Me.CBMarque & "" <> "" Then strFilter = strFilter & " and " & "[common marque]='" & Me.CBMarque & "'"
    If Me.CBNom & "" <> "" Then strFilter = strFilter & " and " & "[Nom CC] Between " & Me.CBNom - Me.LblVNomCount.Caption & " AND " & Me.CBNom + Me.LblVNomCount.Caption
    If Me.CBFuel & "" <> "" Then strFilter = strFilter & " and " & "[FUEL]='" & Me.CBFuel & "'"
    If Me.CBTransmission & "" <> "" Then strFilter = strFilter & " and " & "[transmission]='" & Me.CBTransmission & "'"
    If Me.CBBody & "" <> "" Then
        Select Case Me.CBBody
            Case "Coupe\Convertible"
                strTemp = "Left([Body type],2)='CO'"
            Case "Estate\MPV"
                strTemp = "[Body type]='Estate' Or [body type]='MPV'"
            Case "Hatchback", "Roadster", "Saloon"
                strTemp = "[body type]='" & Me.CBBody & "'"
            Case "Others"
                strTemp = "[Body type] NOT IN('Estate', 'MPV', 'Hatchback', 'Saloon', 'Roadster')"
        End Select
        strFilter = strFilter & " and " & strTemp
    End If
    If Me.CBDoors & "" <> "" Then strFilter = strFilter & " and " & "[doors]=" & Me.CBDoors
    If Len(WordFilter) > 0 And SmartFilterToggle = True Then strFilter = strFilter & " and " & WordFilter
    
    
    If strFilter <> "" Then
        strFilter = Mid(strFilter, 5) 'removes "and " at the beginning of strFilter
        'Filter form
        Debug.Print strFilter
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
    
End Function

Open in new window

You didn't have the category combobox in your code so I didn't add it either.
EE---2013.zip
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question