By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Create filter (dropdown and textbox input)

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Find_Click()
  2.  
  3.     If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
  4.         MsgBox "You must select a field to search."
  5.  
  6.     ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  7.         MsgBox "You must enter a search stringtxt."
  8.  
  9.     Else
  10.  
  11.         'Generate search criteria
  12.         GCriteria = "[" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"
  13.  
  14.         'Filter frmCustomers based on search criteria
  15.         Me.Filter = GCriteria
  16.         Me.FilterOn = True
  17.  
  18.  
  19.         'Close frmSearch
  20.  
  21.         MsgBox "Results have been filtered."
  22.  
  23.     End If
  24.  
  25. 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!
Aug 7 '08 #1
Share this Question
Share on Google+
16 Replies


100+
P: 167
Hi Philippe!

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

H.
Attached Files
File Type: zip SampleDatabase.zip (26.1 KB, 702 views)
Aug 7 '08 #2

NeoPa
Expert Mod 15k+
P: 31,266
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!
Aug 7 '08 #3

NeoPa
Expert Mod 15k+
P: 31,266
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.
Aug 7 '08 #4

P: 15
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
Aug 7 '08 #5

NeoPa
Expert Mod 15k+
P: 31,266
Philippe, it's not clear who you're addressing these remarks to I'm afraid.
Aug 7 '08 #6

P: 15
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.
Aug 7 '08 #7

100+
P: 167
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.
Aug 7 '08 #8

NeoPa
Expert Mod 15k+
P: 31,266
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 ;)
Aug 7 '08 #9

Expert 100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. CustomerID, FirstName, LastName, Address, City, State, Zipcode,  Phone
  2.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. me.firstname
  3.  
  4.  
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 "ResultsList"

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

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.     Dim Criteria As String
  3.  
  4.     strSQL = "SELECT CustomerID as ID, FirstName, LastName, City, State, ZipCode, Phone " & _
  5.              "FROM Customers WHERE "
  6.     If IsNull(Me.FirstName) And _
  7.        IsNull(Me.LastName) And _
  8.        IsNull(Me.State) And _
  9.        IsNull(Me.ZipCode) Then
  10.             MsgBox "Must Enter at least one value in " & _
  11.                    "order to search database.", vbOKOnly
  12.     Else
  13.         If Not IsNull(Me.FirstName) Then
  14.             If Len(Criteria) > 0 Then
  15.                 Criteria = Criteria & " AND FirstName = '" & Me.FirstName & "'"
  16.             Else
  17.                 Criteria = Criteria & "FirstName = '" & Me.FirstName & "'"
  18.             End If
  19.         End If
  20.         If Not IsNull(Me.LastName) Then
  21.             If Len(Criteria) > 0 Then
  22.                 Criteria = Criteria & " AND LastName = '" & Me.LastName & "'"
  23.             Else
  24.                 Criteria = Criteria & "LastName = '" & Me.LastName & "'"
  25.             End If
  26.         End If
  27.         If Not IsNull(Me.State) Then
  28.             If Len(Criteria) > 0 Then
  29.                 Criteria = Criteria & " AND State = '" & Me.State & "'"
  30.             Else
  31.                 Criteria = Criteria & "State = '" & Me.State & "'"
  32.             End If
  33.         End If
  34.         If Not IsNull(Me.ZipCode) Then
  35.             If Len(Criteria) > 0 Then
  36.                 Criteria = Criteria & " AND ZipCode = '" & Me.ZipCode & "'"
  37.             Else
  38.                 Criteria = Criteria & "Zipcode = '" & Me.ZipCode & "'"
  39.             End If
  40.         End If
  41.  
  42.         strSQL = strSQL & Criteria
  43.         Me.ResultList.ColumnCount = 7
  44.         Me.ResultList.BoundColumn = 1
  45.         Me.ResultList.ColumnHeads = True
  46.         Me.ResultList.ColumnWidths = "720;1440;1440;720;720;720;720"
  47.         Me.ResultList.RowSourceType = "Table/Query"
  48.         Me.ResultList.RowSource = strSQL
  49.         Me.ResultList.Requery
  50.     End If
  51.  
  52.  
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.requery 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.
Attached Files
File Type: zip Search Sample.zip (14.0 KB, 239 views)
Aug 7 '08 #10

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Find_Click()
  2.  
  3. Dim Criteria As String
  4.  
  5.  
  6.     If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
  7.         MsgBox "You must select a field to search."
  8.         'set focus to the combo box to select a search string
  9.         Me!cboSearchField.SetFocus
  10.  
  11.     ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  12.         MsgBox "You must enter a search stringtxt."
  13.         'set focus to the text box to select a search string
  14.         Me!txtSearchString.SetFocus
  15.  
  16.     Else
  17.         If " & Me.cboSearchField & " = "Company" Then
  18.                 Criteria = Criteria & " AND Company Like '" & Me.txtSearchString & "'"
  19.             Else
  20.                 Criteria = Criteria & "Company Like '" & Me.txtSearchString & "'"
  21.             End If
  22.         If " & Me.cboSearchField & " = "Last Name" Then
  23.                 Criteria = Criteria & " AND Last Name Like '" & Me.txtSearchString & "'"
  24.             Else
  25.                 Criteria = Criteria & "Last Name Like '" & Me.txtSearchString & "'"
  26.             End If
  27.         If " & Me.cboSearchField & " = "First Name" Then
  28.                 Criteria = "First Name Like '" & Me.txtSearchString & "'"
  29.             Else
  30.                 Criteria = Criteria & "First Name Like '" & Me.txtSearchString & "'"
  31.             End If
  32.     End If
  33.  
  34.         'Filter frmCustomers based on search criteria
  35.  
  36.         Me.Filter = Criteria
  37.         Me.FilterOn = True
  38.         'Close frmSearch
  39.         MsgBox "Results have been filtered."
  40.  
  41. 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.
Aug 8 '08 #11

Expert 100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Find_Click()
  2.  
  3. Dim Criteria As String
  4.  
  5.  
  6.     If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
  7.         MsgBox "You must select a field to search."
  8.         'set focus to the combo box to select a search string
  9.         Me!cboSearchField.SetFocus
  10.  
  11.     ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  12.         MsgBox "You must enter a search stringtxt."
  13.         'set focus to the text box to select a search string
  14.         Me!txtSearchString.SetFocus
  15.  
  16.     Else
  17.         If " & Me.cboSearchField & " = "Company" Then
  18.                 Criteria = Criteria & " AND Company Like '" & Me.txtSearchString & "'"
  19.             Else
  20.                 Criteria = Criteria & "Company Like '" & Me.txtSearchString & "'"
  21.             End If
  22.         If " & Me.cboSearchField & " = "Last Name" Then
  23.                 Criteria = Criteria & " AND Last Name Like '" & Me.txtSearchString & "'"
  24.             Else
  25.                 Criteria = Criteria & "Last Name Like '" & Me.txtSearchString & "'"
  26.             End If
  27.         If " & Me.cboSearchField & " = "First Name" Then
  28.                 Criteria = "First Name Like '" & Me.txtSearchString & "'"
  29.             Else
  30.                 Criteria = Criteria & "First Name Like '" & Me.txtSearchString & "'"
  31.             End If
  32.     End If
  33.  
  34.         'Filter frmCustomers based on search criteria
  35.  
  36.         Me.Filter = Criteria
  37.         Me.FilterOn = True
  38.         'Close frmSearch
  39.         MsgBox "Results have been filtered."
  40.  
  41. 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.
Aug 8 '08 #12

P: 15
Reply: How do I attach a zipfile in this message? :p
Aug 8 '08 #13

NeoPa
Expert Mod 15k+
P: 31,266
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.
Aug 8 '08 #14

NeoPa
Expert Mod 15k+
P: 31,266
Sorry Philippe, my previous post had a typo and didn't make proper sense. I've corrected it so please read it again :(
Aug 8 '08 #15

P: 15
I found a much easyer solution, working through a querry.
Thank you all for trying to help me though!

Philippe
Aug 12 '08 #16

NeoPa
Expert Mod 15k+
P: 31,266
That's fine Philippe, and thanks for letting us know. We appreciate the feedback as always :)
Aug 12 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.