469,268 Members | 942 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 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()
  2.  
  3. Dim strFilter As String
  4.  
  5. strFilter = ""
  6.  
  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"
  12.  
  13. End If
  14.  
  15.  
  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 #1
79 3723
Steven Kogan
107 Expert 100+
You've populated the strFilter string.

You also want to set the filter with something like:
Expand|Select|Wrap|Line Numbers
  1.    me.Filter = strFilter
  2.    me.FilterOn = true
  3.  
Aug 10 '10 #2
dsatino
393 256MB
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.
Aug 10 '10 #3
Hi Steven,

When I add that code and run the program I get an error.
Aug 11 '10 #4
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?
Aug 11 '10 #5
dsatino
393 256MB
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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. me.requery
after that, I'm not sure. If your string is built correctly so that the form understands it, it will work.
Aug 11 '10 #6
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim strFilter As String
  3. strFilter = ""
  4.  
  5.     If Not IsNull(Me!txtDoD) Then strFilter = strFilter & "[DOD Component]='" & Me!txtDoD & "'And"
  6.     If Not IsNull(Me!txtUIC) Then strFilter = strFilter & "[Uic]='" & Me!txtUIC & " 'And "
  7.     If Not IsNull(Me!txtUnitName) Then strFilter = strFilter & "[Name]='" & Me!txtUnitName & "'And"
  8.     If Not IsNull(Me!txtCity) Then strFilter = strFilter & "[City]='" & Me!txtCity & "'And"
  9.     If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]='" & Me!txtState & 
  10.  
  11.     If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
  12.  
  13.     Me.Filter = strFilter
  14.     Me.FilterOn = True
  15.     Debug.Print strFilter
  16.     Me.Requery
  17. End Sub
Aug 11 '10 #7
dsatino
393 256MB
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.
Aug 11 '10 #8
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.
Aug 11 '10 #9
Did you not include your full code? I noticed that this is your last line of code
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!txtState) Then strFilter = strFilter & "[State]='" & Me!txtState &
if this is correct you are missing
Expand|Select|Wrap|Line Numbers
  1. "'
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?
Aug 11 '10 #10
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.
Aug 11 '10 #11
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?
Aug 11 '10 #12
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.
Aug 11 '10 #13
Where is all this information being populated to, another form/report?
Aug 11 '10 #14
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.
Aug 11 '10 #15
dsatino
393 256MB
You're not getting the string in the immediate window?
Aug 11 '10 #16
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.
Aug 11 '10 #17
No not getting anything.
Aug 11 '10 #18
So would it be something like
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptResults",,,strFilter?
Aug 11 '10 #19
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub command0_click()
  2.  
  3. Dim stDocName As String
  4. Dim stLinkCriteria As String
  5.  
  6. stDocName = "SubSearch"
  7. stLinkCriteria = "[workordernumber]=" & Me.Text2
  8.  
  9. DoCmd.OpenForm stDocName, , , stLinkCriteria
  10. 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.
Aug 11 '10 #20
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblUIC.[DOD Component], tblUIC.Uic, tblUIC.Name, tblUIC.[Major Command], tblUIC.City, tblUIC.State, tblUIC.Zip
  2. FROM tblUIC
  3. 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] & "*"));
Aug 11 '10 #21
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
Expand|Select|Wrap|Line Numbers
  1.  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
Aug 11 '10 #22
Michael in this example is SubSearch your sub form and stDocName your main form?
Aug 11 '10 #23
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.
Aug 11 '10 #24
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2. On Error GoTo Err_Command10_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "SubForm1"
  8.     stLinkCriteria = "[text3] =" & Me.Text11
  9.  
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_Command10_Click:
  13.     Exit Sub
  14.  
  15. Err_Command10_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_Command10_Click
  18.  
  19. End Sub
Don't know why it didn't work.
Aug 11 '10 #25
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.
Aug 11 '10 #26
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?
Aug 11 '10 #27
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.
Aug 11 '10 #28
Yeah Baby, found it!!!!! Check this out, and it works!

Expand|Select|Wrap|Line Numbers
  1. Private Sub command0_click()
  2.  
  3.  
  4. Dim stDocName As String
  5. Dim stLinkCriteria As String
  6.  
  7. stDocName = "SubSearch"
  8.  
  9. If Not IsNull(Me.Text2) Then
  10.     stLinkCriteria = "[workedby]=""" & Me!Text2 & """"
  11. End If
  12. If IsNull(Me!Text2) Then
  13.     MsgBox ("Please enter something...")
  14. Else
  15.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  16. End If
  17. 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! :)
Aug 11 '10 #29
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?
Aug 12 '10 #30
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
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = strFilter
Any suggestions would be greatly appreciated. The full code is listed below.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2.  
  3. Dim strFilter As String
  4. strFilter = ""
  5.  
  6.     If Not IsNull(Me!txtDoD) Then strFilter = strFilter & "([DOD Component] Like ""*" & Me!txtDoD & "*"") And"
  7.  
  8.     If Not IsNull(Me!txtUIC) Then strFilter = strFilter & "([Uic] Like ""*" & Me!txtUIC & "*"") And "
  9.  
  10.     If Not IsNull(Me!txtUnitName) Then strFilter = strFilter & "([Name] Like ""*" & Me!txtUnitName & "*"") And"
  11.  
  12.     If Not IsNull(Me!txtCity) Then strFilter = strFilter & "([City] Like ""*" & Me!txtCity & "*"") And"
  13.  
  14.     If Not IsNull(Me!txtState) Then strFilter = strFilter & "([State] Like ""*" & Me!txtState & "*"")"
  15.  
  16.     If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
  17.  
  18.     Me.Filter = strFilter
  19.     Me.FilterOn = True
  20.  
  21.  
  22.  
  23.     DoCmd.OpenReport "rptResults", acViewPreview
  24.  
  25.  
  26.  
  27. End Sub
Aug 13 '10 #31
Comment out lines 7 through 17 then run your code again.
Aug 13 '10 #32
Still getting the same error message. It's not liking the query string.
Aug 13 '10 #33
Post your dim's please.
Aug 13 '10 #34
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter as String
  2. strFilter = ""
Aug 13 '10 #35
Comment out the And at the end of line 6.
Aug 13 '10 #36
I tried that too and still getting the same error.
Aug 13 '10 #37
dsatino
393 256MB
Have you ever printed the resulting string that are building?
Aug 13 '10 #38
No I have not. How do I go about that?
Aug 13 '10 #39
dsatino
393 256MB
From your code window click View/Immediate Pane
Then in your code insert
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 13 '10 #40
I will try that and let you know what happens. Thanks.
Aug 13 '10 #41
Hi dsatino,

No string showed up in the immediate window for me. Not sure why.
Aug 16 '10 #42
dsatino
393 256MB
Sounds like your string isn't being constructed at all. Let's change the variable scope. Cut this out of procedure
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
Create a new module and put this in it
Expand|Select|Wrap|Line Numbers
  1. Global strFilter As String
Then run your code. If you don't get any results in the immediate window then type
Expand|Select|Wrap|Line Numbers
  1. ? strFilter
directly in the immediate window and press enter. If nothing appears, then your string isn't being built at all.
Aug 16 '10 #43
I didn't see anything. Why wouldn't my string be built?
Aug 16 '10 #44
dsatino
393 256MB
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
Aug 16 '10 #45
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.
Aug 16 '10 #46
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
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = strFilter
I also commented out the code in my Form_Open(Cancel As Integer) section that reads
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "(False)"
and
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 16 '10 #47
dsatino
393 256MB
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?
Aug 16 '10 #48
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.
Aug 16 '10 #49
dsatino
393 256MB
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.
Aug 16 '10 #50

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
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.