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.

[dsatino]

You're not getting the string in the immediate window?

[Michael Adams]

Yes, you will need to open something with your results populated from the search. It can be most anything you wish. Just depends on the final result you are looking for.

[newbie2010]

No not getting anything.

[newbie2010]

So would it be something like

DoCmd.OpenReport "rptResults",,,strFilter?

[Michael Adams]

I am trying to do the exact same thing with my form/subform. What I did was I created a new form in design mode then added a button to open an already existing form that I will use to hold all relevant data. Looking at that code it did give me a docmd.openform. Then I used the criteria string to equal what was input into the text box and Viola. It Worked.
Here is the final code I came up with:

Private Sub command0_click()
 
Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "SubSearch"
stLinkCriteria = "[workordernumber]=" & Me.Text2
 
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Hope this helps, caveate: make sure on your subform/form you bound each text box to the table-field you want to have populated or it will remain blank.

BTW, I am now going to be putting in an error msgbox if no text is entered.

[newbie2010]

I have even tried to create a query and then just code the search button to call the query, but when I do it that way I can only get one of the text boxes to return data and it will only return one record and not multiples.

SELECT tblUIC.[DOD Component], tblUIC.Uic, tblUIC.Name, tblUIC.[Major Command], tblUIC.City, tblUIC.State, tblUIC.Zip
FROM tblUIC
WHERE (((tblUIC.[DOD Component]) Like [Forms]![FRMSEARCH].[txtDoD] & "*") AND ((tblUIC.Uic)=[FORMS]![FRMSEARCH].[TXTUIC]) AND ((tblUIC.Name) Like [Forms]![FRMSEARCH].[txtUnitName] & "*") AND ((tblUIC.City) Like [Forms]![FRMSEARCH].[txtCity] & "*") AND ((tblUIC.State) Like [Forms]![FRMSEARCH].[txtState] & "*"));

[Michael Adams]

Let's slow down and back way up.
Do this, make a new form in design mode and link it to the table UIC. Put in 5 text boxs and under all in the properties of each text box go to control source and link the field from the linked table you wish to have populated when you click the search button. Now do a save and name this form subform1.
Create another new form from the design mode and put a commad button on it. when the command button wizard comes up select Form Operations form the categories and Open Form from the Actions. Click next. Now select subform1 and select next two more time then finish.
Now save this form as commandform1.
while still in design mode of commandform1 place a text box. click on the lable and delete it.
Go to all in the properties and note what the name of the text box is.
Go to your code for the command_click and see where your subform1 is located? Under that use this

 stlinkcriteria = "[fieldfromsubform1 you wish to match] =" &me.textbox from commandform1

This code should be directly under stdocname and above the docmd.

Try that and tell me what happens.

Thanks,

MJA

[newbie2010]

Michael in this example is SubSearch your sub form and stDocName your main form?

[Michael Adams]

Subform is my sub form but the command0_click() is on the main form. StdocName is just a variable and it is assigned the name of the sub form.

[newbie2010]

When I type in a value on the command form I get another message box that pops up and asks for a parameter value for the text box on subform1. Once I enter a value into that box it brings back the first record in the table instead of the record that matches my criteria.

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "SubForm1"
    stLinkCriteria = "[text3] =" & Me.Text11
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command10_Click:
    Exit Sub
 
Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
 
End Sub

Don't know why it didn't work.

[Michael Adams]

Are you entering the same value? I have the exact same message when I enter text not a number. I re-entered my same data and got the results.
Try that and lets see what happens.

[Michael Adams]

The reason it is asking for a parameter value is because I have multiple values in that field on the table. I am trying to figure out how to get the exact match from the search form to the sub form.
Make sense?

[newbie2010]

Yes that makes sense. That's what I want for my form as well an exact match so that the user would not have to enter the criteria twice.

[Michael Adams]

Yeah Baby, found it!!!!! Check this out, and it works!

Private Sub command0_click()
 
 
Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "SubSearch"
 
If Not IsNull(Me.Text2) Then
    stLinkCriteria = "[workedby]=""" & Me!Text2 & """"
End If
If IsNull(Me!Text2) Then
    MsgBox ("Please enter something...")
Else
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub

This also includes the error box that tells the users to input something in the box.
So, in both of our cases, we would need multiple if statements or a case statement for each field. I will try with multiple case statements to see if it works. If it does work then I will post the results here.

Hope this helps out alot, cause I have been working without a solution to this problem for over a week and now we both have it! :)

[newbie2010]

I tried that too and I still get that other message box come up asking for the parameter again...does it matter that mine is in the Access 2000 file format?

[newbie2010]

I have reworked my code and now I am getting a run time error 3075 telling me I have a syntax error (missing operator) in query expression. I have been over the code and can not find what's missing. When I debug it it point to this statement

Me.Filter = strFilter

Any suggestions would be greatly appreciated. The full code is listed below.

Private Sub Command10_Click()
 
Dim strFilter As String
strFilter = ""
 
    If Not IsNull(Me!txtDoD) Then strFilter = strFilter & "([DOD Component] Like ""*" & Me!txtDoD & "*"") And"
 
    If Not IsNull(Me!txtUIC) Then strFilter = strFilter & "([Uic] Like ""*" & Me!txtUIC & "*"") And "
 
    If Not IsNull(Me!txtUnitName) Then strFilter = strFilter & "([Name] Like ""*" & Me!txtUnitName & "*"") And"
 
    If Not IsNull(Me!txtCity) Then strFilter = strFilter & "([City] Like ""*" & Me!txtCity & "*"") And"
 
    If Not IsNull(Me!txtState) Then strFilter = strFilter & "([State] Like ""*" & Me!txtState & "*"")"
 
    If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
 
    Me.Filter = strFilter
    Me.FilterOn = True
 
 
 
    DoCmd.OpenReport "rptResults", acViewPreview
 
 
 
End Sub

[Michael Adams]

Comment out lines 7 through 17 then run your code again.

[newbie2010]

Still getting the same error message. It's not liking the query string.

[Michael Adams]

Post your dim's please.

[newbie2010]

Dim strFilter as String
strFilter = ""

[Michael Adams]

Comment out the And at the end of line 6.

[newbie2010]

I tried that too and still getting the same error.

[dsatino]

Have you ever printed the resulting string that are building?

[newbie2010]

No I have not. How do I go about that?

[dsatino]

From your code window click View/Immediate Pane
Then in your code insert

debug.print strFilter

right after you finalize what the string is supposed to be. The next time you run the code, the string will appear in the immediate window

[newbie2010]

I will try that and let you know what happens. Thanks.

[newbie2010]

Hi dsatino,

No string showed up in the immediate window for me. Not sure why.

[dsatino]

Sounds like your string isn't being constructed at all. Let's change the variable scope. Cut this out of procedure

Dim strFilter As String

Create a new module and put this in it

Global strFilter As String

Then run your code. If you don't get any results in the immediate window then type

? strFilter

directly in the immediate window and press enter. If nothing appears, then your string isn't being built at all.

[newbie2010]

I didn't see anything. Why wouldn't my string be built?

[dsatino]

First, let's make sure the variable is global. In your immediate window, type
strFilter="test"
Press Enter
Type
? strFilter
Press Enter

If the immediate window returns nothing, then your variable isn't truly global.If it returns: test, then your code really isn't building the string.


Possible reasons:
1. You don't have anything in your text boxes. (obvious, but I doubt you're making that mistake)
2. You're not actually calling the procedure. Check the properties of the button your pushing to see if you actually have the On_Click event set to run this procedure.
3. The NOT ISNULL() may not be evaluating the way think it is. Testing for nulls can be tricky. I always use the nz() function which i find easier because you actually set the outcome of the evaluation and test for your own outcome.

My guess is that you have situation #3. Try changing the NOT ISNULL() to ISNULL()=True

[newbie2010]

Ok...I did what you said in the immediate window and I did get "test" which means that my code is not building the string correctly. Looking at the possible reasons some of the textboxes may be empty when the program is run. The user may choose to only populate one of the textboxes and leave the other four blank and only get results to match the one criteria. That is why I need my program to disregard the textbox if it is blank. I will try the changes to the NOT ISNULL statements and see how that works. Thanks.

[newbie2010]

When I made the recommended changes and ran the program I did not get the error. I am actually getting data back for the field where there is an exact match, but on the fields that have multiple matches I am not getting anything back. I commented out the line in my code that reads

Me.Filter = strFilter

I also commented out the code in my Form_Open(Cancel As Integer) section that reads

Me.Filter = "(False)"

and

Me.FilterOn = True

I guess now I just need to figure out how to get the fields with multiple matches to appear and this will be done.

[dsatino]

I didn't really follow that? Do you mean that when you enter only one criteria, you're getting results but when you enter two criteria you're not getting results?

[newbie2010]

What I meant was that I can type criteria into the 'UIC' field and get records to return, but when I type a name in the 'City' field nothing comes back. The 'UIC' field is an exact match field because each group only has one UIC,but there will be many matches for the 'City' field as there are many groups in each city. I hope this is not too confusing.

[dsatino]

First thing to do is isolate it, meaning you are only asking for results based on city and no other criteria. Again, print your results to the immediate window to be certain that your filter string is syntactically correct. Keep in mind that the syntax for filtering a numeric field is different for text field.