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

[newbie2010]

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:

Private Sub cmdSearch_Click()
 
Dim strFilter As String
 
strFilter = ""
 
    If Not IsNull(Me!txt1) Then strFilter = strFilter & "[Field1]='" & Me!txt1 & "'And"
    If Not IsNull(Me!txt2) Then strFilter = strFilter & "[Field2]='" & Me!txt2 & " 'And "
    If Not IsNull(Me!txt3) Then strFilter = strFilter & "[Field3]='" & Me!txt3 & "'And"
    If Not IsNull(Me!txt4) Then strFilter = strFilter & "[Field4]='" & Me!txt4 & "'And"
    If Not IsNull(Me!txt5) Then strFilter = strFilter & "[Field5]='" & Me!txt5 & "'And"
 
End If
 
 
 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.

[newbie2010]

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.

[dsatino]

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.

[newbie2010]

I entered the city into my program and then pressed the button to run it and nothing came up in the immediate window.

[dsatino]

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:

If Not IsNull(Me!txt1) Then strFilter = strFilter & "[Field1]='" & Me!txt1 & "'And"

Instead of that, do this first and see what happens

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.

[newbie2010]

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.

[dsatino]

From here, I think I would use a 3-stage evaluation of each box that would look something like this:

 
If nz(me.txt1,"0")="0" then
   'do something/nothing
ElseIf me.txt1="" then
   'do something/nothing
Else
   'do something/nothing 
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.

[newbie2010]

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

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.

[dsatino]

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.

So
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:

If nz(me.txt1,"0")="0" then
   'do nothing
ElseIf me.txt1="" then
   'do nothing
Else
   strFilter = strFilter & "[Field1]='" & Me!txt1 & "'And"
End if
 

[newbie2010]

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.

[dsatino]

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.

[newbie2010]

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.

[dsatino]

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.

[newbie2010]

So would my code look something like this and I would just put the name of my report in the place of subCustomers?

If strFilter="" Then 
       Me!subCustomers.Form.FilterOn = False 
    Else 
       Me!subCustomers.Form.Filter = strFilter 
       Me!subCustomers.Form.FilterOn = True 
    End If 

[newbie2010]

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.

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.

[dsatino]

At a glance, it looks like you need a space between [state] and Like

[newbie2010]

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)

If Not IsNull(Me!txtDoD) Then strFilter = strFilter & "[DOD Component]Like" & strQt & "*" & Me!txtDoD & "*" & strQt & " AND "
If Not IsNull(Me!txtUIC) Then strFilter = strFilter & "[Uic]Like" & strQt & "*" & Me!txtUIC & "*" & strQt & " AND "
If Not IsNull(Me!txtUnitName) Then strFilter = strFilter & "[Name]Like" & strQt & Me!txtUnitName & "*" & strQt & " AND "
If Not IsNull(Me!txtCity) Then strFilter = strFilter & "[City]Like" & strQt & Me!txtCity & "*" & strQt & " AND "
If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]Like" & strQt & Me!txtState 
 
 
 
If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
DoCmd.OpenReport "rptResults", acViewPreview, , strFilter
 
End Sub 

[dsatino]

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:

[State]Like"DE"

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:

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:

If Right(strFilter,5)=" And " Then strFilter = Left$(strFilter, Len(strFilter) - 5)
 

[newbie2010]

Thanks for the tip. How would I go about 'cleaning up' the code? I want the syntax to be correct.

[dsatino]

Simply put a space before and after the word Like.

[newbie2010]

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?

[newbie2010]

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.

[dsatino]

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.

trim([State])...trim(me!txtState)

[NeoPa]

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 :)

[dsatino]

NeoPa,
Thanks very much for the interjection. This type of example is very useful on many levels.

Newbie2010,

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.

[newbie2010]

Great...I will take a look at it and let you know what happens from there. Thanks for the advice.

[NeoPa]

Always a pleasure :)

DSatino:
NeoPa,
Thanks very much for the interjection. This type of example is very useful on many levels.

And thanks for the kind words.

[newbie2010]

Thank you so much for all of your help. Everything is working great!!

[dsatino]

Glad to hear it! Just out of curiousity, did the form example NeoPa sent help? I'm betting it did.

[newbie2010]

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.

[NeoPa]

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.