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.

[Steven Kogan]

You've populated the strFilter string.

You also want to set the filter with something like:

   me.Filter = strFilter
   me.FilterOn = true

[dsatino]

What you want is pretty simple in general, but....

What is that 'End If' for? It's just hanging out.

If your evaluating your text boxes in a specific order every time then you know that nothing comes after the 5th one so why have the 'And' attached to the end. You can always check if it's there and then something if necessary.

Then once you have your string, that's great, but it doesn't appear your doing anything with. All you appear to be doing is setting a string variable. You can't refer to it from other modules because it's declared within the procedure which means it's scope is confined to the procedure.

[newbie2010]

Hi Steven,

When I add that code and run the program I get an error.

[newbie2010]

Hi dsatino,

The End If was commented out in the program. I see what you are saying about the last 'And', but everything else kind of went over my head. Do I need to create a different form for this to show?

[dsatino]

No, no need for a different form. From what I can see, it looks like you're trying to create a filter based on user input.

So somewhere in your form you have these 5 boxes in which the user can fill out, click a button, and the form will display results based on these selections. This a pretty standard thing for forms, maybe even the most common use, so you definitely don't need a new form.

In any case, there's several different ways to go about this. But first, let's start conceptually with why your code is doing anything.

Your machine and Access are not inherently smart by any means. They will do only what you tell it to do and only if it understands what you're asking it.

By the looks of your code, it is doing something. It's building a string based on the user input. If you put the code

debug.print strFilter

right before the End Sub statement and click your button, it should show you what your code built as the string filter.

But it looks like that's all your asking the code to do, just build the string, which it probably is. The real question now becomes how do you make that string act as a filter? This where you have to make a choice on how you want your form to operate. From the looks of the way you built the string, your goal is set the filter property of a bound form. In this case, put the code supplied by Steve Kogan above to the end of your sub. You may also need to add

me.requery

after that, I'm not sure. If your string is built correctly so that the form understands it, it will work.

[newbie2010]

dsatino I really want to thank you for all of your help...I am very new at this.

Ok so I have made all of the suggested changes and I am now getting a run-time error '2448' telling me that I can not assign a value to this object when I try to populate any of the text boxes to do a search. My code now looks like this.

Private Sub cmdSearch_Click()
Dim strFilter As String
strFilter = ""

    If Not IsNull(Me!txtDoD) Then strFilter = strFilter & "[DOD Component]='" & Me!txtDoD & "'And"
    If Not IsNull(Me!txtUIC) Then strFilter = strFilter & "[Uic]='" & Me!txtUIC & " 'And "
    If Not IsNull(Me!txtUnitName) Then strFilter = strFilter & "[Name]='" & Me!txtUnitName & "'And"
    If Not IsNull(Me!txtCity) Then strFilter = strFilter & "[City]='" & Me!txtCity & "'And"
    If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]='" & Me!txtState & 

    If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)

    Me.Filter = strFilter
    Me.FilterOn = True
    Debug.Print strFilter
    Me.Requery
End Sub

[dsatino]

This is probably a case where it doesn't understand the string you are giving it. comment out the two filter statements and send me the resulting string that you get from the debug.print.

For the most part it looks like your quotes and spaces might be a little screwy.

[newbie2010]

When I comment out the filter statements I get nothing when I hit the search button. I don't get any errors or any data returned.

[Michael Adams]

Did you not include your full code? I noticed that this is your last line of code

If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]='" & Me!txtState &

if this is correct you are missing

"'

Could this be why you are not returning anything? Also, maybe I am not as smart as these other programmers, but what are you searching for and where is that info populated?

[newbie2010]

Hi Michael,

I have a form that I have created and it's linked to a table. What I want is for the user to enter data into the 5 text boxes and hit the search button on the form and have the results displayed based on the criteria they entered.

[Michael Adams]

What results are being populated and where are they being populated to? As in, I enter a number into the txtDOD text box and hit search, what should happen?

[newbie2010]

When you enter a value into the txtDOD then all records having that value in the corresponding field in the table should come back. So let's say that you entered '1' into that text box then all records in the table having 1 in that field should come back.

[Michael Adams]

Where is all this information being populated to, another form/report?

[newbie2010]

That's the question I had earlier...would I have to create another form/report to see the results. I have a report created but I don't know how to get the results displayed.