Create filter (dropdown and textbox input)

[PhilippeM]

I am still very new at this (first timer actually).. I am also trying (with numerous efforts) to create a search box in a form. I have made a dropdown box and a textbox to enter the searchcriteria. I have copy pasted the folowing code:

Private Sub Find_Click()
 
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search stringtxt."
        
    Else
    
        'Generate search criteria
        GCriteria = "[" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"
        
        'Filter frmCustomers based on search criteria
        Me.Filter = GCriteria
        Me.FilterOn = True

               
        'Close frmSearch
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

I have problems defining:
Me.Filter = GCriteria
Me.FilterOn = True

How do I create this filter, and do I put it in this code, or in a module where I refer to?

Further assistance would be very nice. Thank you!

[hjozinovic]

Hi Philippe!

Attached here is a sample database with modification of your code that works the way you wanted it to work.

H.

[NeoPa]

First check out Example Filtering on a Form. Hopefully it will answer all your questions AND provide some sample code for you to work with.

If you still need further help on this, please feel free to post back.

Welcome to Bytes!

[NeoPa]

Hi Philippe!

Attached here is a sample database with modification of your code that works the way you wanted it to work.

H.

H,

All help is appreciated, especially when you go the extra mile to provide some code for the member.

However, for future readers of the threads, we prefer if possible, that explanations are provided.

The principal reason for the site is to help other professionals (and amateurs as well of course) to learn and progress in the subjects.

Best regards -NeoPa.

[PhilippeM]

Hi Philippe!

Attached here is a sample database with modification of your code that works the way you wanted it to work.

H.

Thank you for your reply!

Although I appreciate your effort, I did not illustrate my problem well.

In my form I have a combo box, where users can select the field (for example: company) they want to search in. Then I have a textbox, where they can enter their search (in this example: the company name).

In the code you wrote, the combobox shows the items in the table, while mine shows the fields.

I hope that I explained it better this time. Sorry for the inconvenience and thank you for your assistance!

Kind regards,

Philippe

[NeoPa]

Philippe, it's not clear who you're addressing these remarks to I'm afraid.

[PhilippeM]

I'm sorry. I was adressing myself to hjozinovic, refering to the code he wrote. The had already consulted the link you gave me, but I find it very confusing, and am not able to translate it into my problem.

Philippe, it's not clear who you're addressing these remarks to I'm afraid.

[hjozinovic]

Hi all!
To NeoPa:
I was trying to reconstruct a sample database for this problem so i found it easiest to simply attach the database here.
In the future I'll try to give more suitable explanations.

To Philippe:
I just used the code you gave to us in your first post.
I copy/pasted it to the sample database I gave you at the beginning.
Then I added a new control naming it txtSearchString .
Next, I put in Combo box's property Row Source Type: field list
And the Row source of the combo box was the query containing the fields you want to search.

After all this it worked smoothly for me. I'm using Acc2003. Not sure why are you having troubles with the same code.

[NeoPa]

I'm sorry. I was adressing myself to hjozinovic, refering to the code he wrote. The had already consulted the link you gave me, but I find it very confusing, and am not able to translate it into my problem.

That's fine.

I'll keep an eye on the thread though in case you change you mind :)

PS. @H
Don't feel I was trying to criticise. It was a good job I'm sure. I was just giving a little friendly direction ;)

[PianoMan64]

I'm sorry. I was adressing myself to hjozinovic, refering to the code he wrote. The had already consulted the link you gave me, but I find it very confusing, and am not able to translate it into my problem.

Hey PhilippeM:

I understand your frustration in learning VBA, I was there once myself. But it isn't really that hard once you learn a few basics.

The first thing that you want to understand is that when you're using a combo box to select which field you want to search on, this in itself is confusing to the user, but not so much for the developer. I've found this to be very true.

What I would suggest, and it will also make your code a bit more involved, but also a great deal flexible in what you can search on.

First thing I would do, is to create a unbonded control for each item that you're wanting to search on. For example:

You have the following fields in your table:

CustomerID, FirstName, LastName, Address, City, State, Zipcode,  Phone

And you want to be able to search by say Firstname, LastName, State, and Zipcode

You would then create unbonded controls on your form that would be named:

FirstName, LastName, State, and ZipCode

Now here is what you need to keep in mind. When referring to controls on a form. in the VBA code module that is for the form, you can refer to the control by the ME. reference. for Example:

me.firstname

The other thing you're going to do is to create a button on the form that will be named SearchBtn. The Caption will be set to "Search"

So when you have that completed you will have a form that has a Fields for each criteria item that you want to be able to search on.

The last step before we start writing any code is, to create an area that will list the results of the search. For me, I've always enjoyed using the ListControl that comes with MS Access. So at this point create a List Control on the form and name it "ResultsLis t"

Now a few explainations about the list control. If you have already worked with the Combo Box control, then you have a real good idea here of how the list control works as well. You simply are going to be providing the SQL Statement that is going to go into the RowSource property of the list control. This is what the VBA code that we're going to write is going to populate.

The other settings of the control can either be set on the control itself, or we can do it through VBA code. I'll show you how to control that through VBA.

So now that you have the List control on the form and have it placed where you want to result window to be, you'll have a better idea as to how the look of the form is going to look like.

Ok. Now let's get into the meet and potatoes of the operation the VBA code.

So the first thing we're going to do is right-click on the Search button you created on the form, and go to it's properties.

Scroll down until you see the On Click event box. once you are there go ahead and click in the box. you will then see a button on the right end of that field that will display ... at the end. Click on the ... button it will ask you what you want to write. Select "Code Builder" from the list and click ok.

You will then be taken to another screen that will allow you to script out what you're wanting this button to do when it is clicked.

Paste the following code in between the Private Sub SearchBtn_Click () and the End Sub statements

    Dim strSQL As String
    Dim Criteria As String
    
    strSQL = "SELECT CustomerID as ID, FirstName, LastName, City, State, ZipCode, Phone " & _
             "FROM Customers WHERE "
    If IsNull(Me.FirstName) And _
       IsNull(Me.LastName) And _
       IsNull(Me.State) And _
       IsNull(Me.ZipCode) Then
            MsgBox "Must Enter at least one value in " & _
                   "order to search database.", vbOKOnly
    Else
        If Not IsNull(Me.FirstName) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND FirstName = '" & Me.FirstName & "'"
            Else
                Criteria = Criteria & "FirstName = '" & Me.FirstName & "'"
            End If
        End If
        If Not IsNull(Me.LastName) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND LastName = '" & Me.LastName & "'"
            Else
                Criteria = Criteria & "LastName = '" & Me.LastName & "'"
            End If
        End If
        If Not IsNull(Me.State) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND State = '" & Me.State & "'"
            Else
                Criteria = Criteria & "State = '" & Me.State & "'"
            End If
        End If
        If Not IsNull(Me.ZipCode) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND ZipCode = '" & Me.ZipCode & "'"
            Else
                Criteria = Criteria & "Zipcode = '" & Me.ZipCode & "'"
            End If
        End If
        
        strSQL = strSQL & Criteria
        Me.ResultList.ColumnCount = 7
        Me.ResultList.BoundColumn = 1
        Me.ResultList.ColumnHeads = True
        Me.ResultList.ColumnWidths = "720;1440;1440;720;720;720;720"
        Me.ResultList.RowSourceType = "Table/Query"
        Me.ResultList.RowSource = strSQL
        Me.ResultList.Requery
    End If

If you take a look at the code, you will notice that I've put at the top of the code an if statement that checks to see if any of the fields have a value, if so, then it will build the Criteria string that will get combine with the SELECT statement string that is defined in strSQL.

Then I check each field and build the Criteria section one field at a time. once that has completed, I set the values for the control, and populate the RowSource property with the resulting strSQL Variable that now has the SQL String that will be put into the RowSource property.

When I execute me.resultlist.r equery it runs the sql statement and populate the form with what the SQL statement says.

I've went ahead and attached an example of what I've just written, in hopes it may answer some more questions that you may have.

If you need more help, let me know.

Joe P.

[PhilippeM]

Dear Joe,

Thank you so very much for the awesome reply! I've learned a lot from your explinations, although it did not entirely fix my problem.

The main difference with your code is that I am not using a ResultList instead I would like to see the data in my subform filtered. So I have a main form (named Contact List), which includes a subform (named Contacts subform) which consists fields from the "Contacts" table. Now I am trying to install a quick search in this subform.

So I have put in:
- a searchbutton (called Find)
- a combobox (called cmbSearchField & with rowvalues "Company", "Last Name", "First Name")
- a textbox (called txtSearchString )

My intention was to create a code, where the user can choose from the possibilities to search by (company, last name, first name) and then enter the first letters of the search criteria in the textbox.

The folowing code is the end result of my numerous efforts and attempts to understand your explinations:

Private Sub Find_Click()

Dim Criteria As String
    
    
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        'set focus to the combo box to select a search string
        Me!cboSearchField.SetFocus
    
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search stringtxt."
        'set focus to the text box to select a search string
        Me!txtSearchString.SetFocus
    
    Else
        If " & Me.cboSearchField & " = "Company" Then
                Criteria = Criteria & " AND Company Like '" & Me.txtSearchString & "'"
            Else
                Criteria = Criteria & "Company Like '" & Me.txtSearchString & "'"
            End If
        If " & Me.cboSearchField & " = "Last Name" Then
                Criteria = Criteria & " AND Last Name Like '" & Me.txtSearchString & "'"
            Else
                Criteria = Criteria & "Last Name Like '" & Me.txtSearchString & "'"
            End If
        If " & Me.cboSearchField & " = "First Name" Then
                Criteria = "First Name Like '" & Me.txtSearchString & "'"
            Else
                Criteria = Criteria & "First Name Like '" & Me.txtSearchString & "'"
            End If
    End If
            
        'Filter frmCustomers based on search criteria
        
        Me.Filter = Criteria
        Me.FilterOn = True
        'Close frmSearch
        MsgBox "Results have been filtered."
    
End Sub

For some reason, it still doesn't work, i don't get any errormessages but nothing is filtered. Could you please take a look at it?

Thank you for all your help!

Kind regards,

Philippe

Hey PhilippeM:

I understand your frustration in learning VBA, I was there once myself. But it isn't really that hard once you learn a few basics.
...
If you need more help, let me know.

Joe P.

[PianoMan64]

Dear Joe,

Thank you so very much for the awesome reply! I've learned a lot from your explinations, although it did not entirely fix my problem.

The main difference with your code is that I am not using a ResultList instead I would like to see the data in my subform filtered. So I have a main form (named Contact List), which includes a subform (named Contacts subform) which consists fields from the "Contacts" table. Now I am trying to install a quick search in this subform.

So I have put in:
- a searchbutton (called Find)
- a combobox (called cmbSearchField & with rowvalues "Company", "Last Name", "First Name")
- a textbox (called txtSearchString )

My intention was to create a code, where the user can choose from the possibilities to search by (company, last name, first name) and then enter the first letters of the search criteria in the textbox.

The folowing code is the end result of my numerous efforts and attempts to understand your explinations:

Private Sub Find_Click()

Dim Criteria As String
    
    
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        'set focus to the combo box to select a search string
        Me!cboSearchField.SetFocus
    
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search stringtxt."
        'set focus to the text box to select a search string
        Me!txtSearchString.SetFocus
    
    Else
        If " & Me.cboSearchField & " = "Company" Then
                Criteria = Criteria & " AND Company Like '" & Me.txtSearchString & "'"
            Else
                Criteria = Criteria & "Company Like '" & Me.txtSearchString & "'"
            End If
        If " & Me.cboSearchField & " = "Last Name" Then
                Criteria = Criteria & " AND Last Name Like '" & Me.txtSearchString & "'"
            Else
                Criteria = Criteria & "Last Name Like '" & Me.txtSearchString & "'"
            End If
        If " & Me.cboSearchField & " = "First Name" Then
                Criteria = "First Name Like '" & Me.txtSearchString & "'"
            Else
                Criteria = Criteria & "First Name Like '" & Me.txtSearchString & "'"
            End If
    End If
            
        'Filter frmCustomers based on search criteria
        
        Me.Filter = Criteria
        Me.FilterOn = True
        'Close frmSearch
        MsgBox "Results have been filtered."
    
End Sub

For some reason, it still doesn't work, i don't get any errormessages but nothing is filtered. Could you please take a look at it?

Thank you for all your help!

Kind regards,

Philippe

Hey Philippe:

I'm glad that you found my example a good one for what you're trying to do, but from what you have explained to me of what you're trying to do and what I explained, are totally two different things all together.

From what you are saying in your post, you have a main form that has some controls on it (Not sure if they are bound to a table or not) and a sub-form that has bound controls to a table that may or may not contain the values that are on the main form?

I have a few questions about your design in order for me to get a better picture of what it is that you're trying to do:

1. The controls on the main form (The combo box and fields), are they bound to a table and/or query that you have in your database?

2. If they are un-bonded controls on the main form, what is the relationship between the fields on your main form, and what is located in your sub-form?

Like in my explanation, If I have unbounded controls on a form that is going to search for results, the use of a sub-form is a great deal harder because you have to set a filter for the sub-form and then refresh that sub-form control. On top of that you don't have the functionality to be able to easily select a particular row of data, if for some reason to need to create a more detailed view of that particular row of data.

If you can explain that in as much detail as you can, or simply send me a example of what you have so far in a zipped MDB file, then I would be able to see what it is that you're trying to do.

Hope this helps,

Joe P.

[PhilippeM]

Reply: How do I attach a zipfile in this message? :p

[NeoPa]

Reply: How do I attach a zipfile in this message? :p

Put in a reply, then edit it within the hour and scroll down the page a little.

There are Attachment Management options there.

[NeoPa]

Sorry Philippe, my previous post had a typo and didn't make proper sense. I've corrected it so please read it again :(