469,315 Members | 2,189 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How do you have a form search a table and then have the results displayed?

I am currently working on a program in Access 2003. I have a form that consists of five text boxes and a search button. I want the user to be able to put search criteria into the text boxes and when they hit the search button the table is searched and they should get the results that matches the criteria they entered. Here's what I have so far:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  3. Dim strFilter As String
  5. strFilter = ""
  7.     If Not IsNull(Me!txt1) Then strFilter = strFilter & "[Field1]='" & Me!txt1 & "'And"
  8.     If Not IsNull(Me!txt2) Then strFilter = strFilter & "[Field2]='" & Me!txt2 & " 'And "
  9.     If Not IsNull(Me!txt3) Then strFilter = strFilter & "[Field3]='" & Me!txt3 & "'And"
  10.     If Not IsNull(Me!txt4) Then strFilter = strFilter & "[Field4]='" & Me!txt4 & "'And"
  11.     If Not IsNull(Me!txt5) Then strFilter = strFilter & "[Field5]='" & Me!txt5 & "'And"
  13. End If
  16.  If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
When I run this code nothing happens. I know that I am missing something, but what that something is I'm not sure. Any help would be greatly appreciated.
Aug 10 '10
79 3730
When I do that I get a compile error saying that the variable not yet creatd in this context. Obviously I am missing something. I have not had much experience with this immediate window.
Aug 16 '10 #51
393 256MB
Enter the city that you want. Press the button to run it. Then in the immediate window type ?strFilter and press enter. As long as strFilter is still global, then it should show up.
Aug 16 '10 #52
I entered the city into my program and then pressed the button to run it and nothing came up in the immediate window.
Aug 17 '10 #53
393 256MB
Then it's not building anything. I think before you go trying to fix this, you should stop and rewrite this code from scratch. It will be an easy process. Just cut and paste your code into a text file and then start moving things back into your procedure one piece at a time.

Basically what I'm saying is build each little piece and run it to see if it works. This way you'll know exactly what's failing. Also, I would take it one step further and test the ISNULL() function you are using.

For instance, your first line of code is this:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txt1) Then strFilter = strFilter & "[Field1]='" & Me!txt1 & "'And"
Instead of that, do this first and see what happens
Expand|Select|Wrap|Line Numbers
  1. debug.print "Result of IsNull:" & IsNull(Me!txt1)
This will show the result of the IsNull() evaluation which can be True, False, or null (in which case you won't get a result after the ':'). You should run it a couple of times for each text box in different states. For instance, when you first open the form and each text box is empty the IsNull() probably evaluates to True. If you put something in the text box then IsNull should return False. If you then delete the contents of the box, IsNull may be evaluating to false because it doesn't see the box as Null, just empty.
Aug 17 '10 #54
Well it looks like it is not reading the box and Null when there is nothing in it. It is just reading it as empty. The IsNull() is always reading False whether there is something in the box or not. That was a good idea...thanks. Now I know that I need to start all over and try to figure out a way to have this box evaluated if it's empty or not.
Aug 17 '10 #55
393 256MB
From here, I think I would use a 3-stage evaluation of each box that would look something like this:

Expand|Select|Wrap|Line Numbers
  2. If nz(me.txt1,"0")="0" then
  3.    'do something/nothing
  4. ElseIf me.txt1="" then
  5.    'do something/nothing
  6. Else
  7.    'do something/nothing 
  8. End if
What this does is cover all situations. The first line with the nz() tests for null. I like this method better than IsNull() because it has only 2 possible results rather than 3. You set it to return "0" (or whatever you want)if something is null and then test for "0". The ElseIf tests for a zero-length string. And the Else test for any condition not met by one of the first two. This should pretty much allow you to what you want.
Aug 17 '10 #56
Ok...so if I want my program to check the first textbox for input if it's null then it should just accept that empty string and keep going to check the next textbox. If the first textbox has input then I want the program to pick up that data and continue on to check the other textboxes and then search the table and return the results. Should I have something like
Expand|Select|Wrap|Line Numbers
  1. If Nz(me.txt1,"0")="0" Then me.txt1 = 0 ElseIf me.txt1="" Then me.txt1="" Else me.txt1=me.txt1 End If
I have never worked with the Nz() so I am feeling my way around with this code.
Aug 17 '10 #57
393 256MB
No, the concept is the same as your original concept. This just uses and 'if...then...elseif' rather than your original 'if...then'. You're using the nz() function in the same manner you were using the IsNull() function. The nz() is just more predictable since it has only two outcomes: 1. your outcome if null 2. not your outcome if null.

nz() has to requirements to work: 1. item to test for null 2. value to return if tested item is null.

So nz(me.txt1,"0") is saying that if me.txt1 is null then the value of the nz() function is "0".

The power of this comes from the ability to test the value of the nz() function as I showed in the previous if function.

If nz(me.txt1,"0")="0" then
'do something
is simply saying set the value of nz() to "0" if me.txt1 is null and if the value of nz() does evaluate to "0" then do something. You could also invert that and say if nz() does NOT evaluate to "0" then do something.

So for txt1 your code would be like this:

Expand|Select|Wrap|Line Numbers
  1. If nz(me.txt1,"0")="0" then
  2.    'do nothing
  3. ElseIf me.txt1="" then
  4.    'do nothing
  5. Else
  6.    strFilter = strFilter & "[Field1]='" & Me!txt1 & "'And"
  7. End if
Aug 17 '10 #58
I am now having an issue with my results printing out. If I want my results to print to a report, what do I set the record source to for that report? When I set it to the table that my forms is set to it brings back all of the records in my table and not just those that meet my criteria.
Aug 18 '10 #59
393 256MB
Correct, all of the filtering you've done only affected the form. You have a couple of options from this point with respect to the report.

1. You can set reports recordsource to the table and then set the reports filter property in the same manner as you did with the form. I think this method may be a bit messy since reports are a little different to work with than forms.

2. Set the reports recordsource to a query based on the table and filter the query based on custom functions that you build. This way may seem more difficult on the surface, but I think it's cleaner.
Aug 18 '10 #60
Ok so I have built a query based on the table that my form is based on, now when you say flter the query based on custom functions I build you mean the code I have created for the 'search' button? If so that would be yet again another new area for me.
Aug 18 '10 #61
393 256MB
Sorry, I misspoke. This method will only work if each function returns a result. In other words, no nulls or empties in any of the text boxes.

To make this method work, you have to rewrite the querydef every time.

So you just want to go ahead and set the filter property in the same method as the form. To do this, the report must be open and in design view.

So you'll have to open the report in design view, set the filter property, turn the filter on, and then open the report in print preview.
Aug 18 '10 #62
So would my code look something like this and I would just put the name of my report in the place of subCustomers?

Expand|Select|Wrap|Line Numbers
  1. If strFilter="" Then 
  2.        Me!subCustomers.Form.FilterOn = False 
  3.     Else 
  4.        Me!subCustomers.Form.Filter = strFilter 
  5.        Me!subCustomers.Form.FilterOn = True 
  6.     End If 
Aug 18 '10 #63
I have finally gotten most of my code to work!! The only problem I am having is that when I put a value into the 'State' text box I am getting an error that is pointing me to my last If statement.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]Like" & strQt & Me!txtState
I know I don't need the trailing 'AND' at the end, but I am not sure what is giving me the error.
Aug 19 '10 #64
393 256MB
At a glance, it looks like you need a space between [state] and Like
Aug 19 '10 #65
That did not work...I thought that was the problem too. Here is my full code.
Dim strFilter As String
Dim strQt As String

strFilter = ""
strQt = Chr$(34)

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txtDoD) Then strFilter = strFilter & "[DOD Component]Like" & strQt & "*" & Me!txtDoD & "*" & strQt & " AND "
  2. If Not IsNull(Me!txtUIC) Then strFilter = strFilter & "[Uic]Like" & strQt & "*" & Me!txtUIC & "*" & strQt & " AND "
  3. If Not IsNull(Me!txtUnitName) Then strFilter = strFilter & "[Name]Like" & strQt & Me!txtUnitName & "*" & strQt & " AND "
  4. If Not IsNull(Me!txtCity) Then strFilter = strFilter & "[City]Like" & strQt & Me!txtCity & "*" & strQt & " AND "
  5. If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]Like" & strQt & Me!txtState 
  9. If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
  10. DoCmd.OpenReport "rptResults", acViewPreview, , strFilter
  12. End Sub 
Aug 19 '10 #66
393 256MB
Your syntax is definitely poor. It sounds like you may be getting away with it, but you should definitely clean it up.

Using the state line as an example, assuming DE is the state, and the the NOT ISNULL is evaluating the way you want, your code reads:


More than likely, Access is inserting the missing spaces for you when you apply the filter, but it's a habit you should get out of because it won't always be corrected.

You definitely have a problem with statement:
Expand|Select|Wrap|Line Numbers
  1. If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
If the state field is populated then the end of your strFilter will read like this: [State]Lik
Change that code to this and you should be ok:
Expand|Select|Wrap|Line Numbers
  1. If Right(strFilter,5)=" And " Then strFilter = Left$(strFilter, Len(strFilter) - 5)
Aug 19 '10 #67
Thanks for the tip. How would I go about 'cleaning up' the code? I want the syntax to be correct.
Aug 19 '10 #68
393 256MB
Simply put a space before and after the word Like.
Aug 19 '10 #69
After making those changes I get nothing returned when I enter text in the text boxes. Does it matter that the length of text will be different for each field? Do I have to code something differently?
Aug 19 '10 #70
I have figured out why I was not getting anything back before...the form needed to be reset, but I am still not getting any results when I put a value in the 'State' text box. All of the others seem to be working ok.
Aug 20 '10 #71
393 256MB
Couple of things to try. First debug.print the string to be sure it's correct. Second, is check the source table to be sure that the state you are entering is in the table.

If it is there try adding the trim() funtion around the state field and the txtState field.

Aug 20 '10 #72
32,173 Expert Mod 16PB
I know I'm coming in to this late, and I don't want to disturb any ongoing conversations, but for the original question you may like to play around in Cascaded Form Filtering and see if that won't help you to resolve your basic issues.

As I say, I can see some good advice going on here about cleaning up code etc, and that's still very helpful stuff to pick up, so I don't want to interrupt that. I just hope the linked article can help you to understand better the subject and give a few tips on some tried and tested techniques of handling form filtering.

Good luck on your quest :)
Aug 20 '10 #73
393 256MB
Thanks very much for the interjection. This type of example is very useful on many levels.


I would strongly suggest opening this up and walking through it start to finish. Many users of this forum, myself included, will simply attempt to take your current problem and help you make it work as intended. But essentially, this is a blind exercise for us. Even if we can get you up and running, it may simply be teaching you methods that are not desirable. NeoPa's example is a ground up, pre-built form that is essentially doing what you are trying to do. Even if we can solve your current problem, it's far more valuable for you to open up this piece and walk through it. You may find many parallels that you can translate or concepts that are useful. You may even find that you can easily rebuild your form based on this one.
Aug 20 '10 #74
Great...I will take a look at it and let you know what happens from there. Thanks for the advice.
Aug 20 '10 #75
32,173 Expert Mod 16PB
Always a pleasure :)
Thanks very much for the interjection. This type of example is very useful on many levels.
And thanks for the kind words.
Aug 21 '10 #76
Thank you so much for all of your help. Everything is working great!!
Aug 27 '10 #77
393 256MB
Glad to hear it! Just out of curiousity, did the form example NeoPa sent help? I'm betting it did.
Aug 27 '10 #78
Yes it did help a lot!! I read the article and tweaked my code a bit and it worked like magic. I truly want to thank the both of you for all of your help.
Aug 30 '10 #79
32,173 Expert Mod 16PB
I've been away, but pleased to come back and find all resolved :) You're welcome of course to any help I was able to offer.
Sep 6 '10 #80

Post your reply

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

Similar topics

2 posts views Thread by Bill Hand | last post: by
1 post views Thread by SC | last post: by
8 posts views Thread by gracealic | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.