Full Version: Creating a search form
UtterAccess Forums > Microsoft® Access > Access Forms
My database contains client records stored in a table callled tblClients. I want the users to be able to search this table to see if a client exists before they add a new record. I thought I would create a simple form called frmSearch that would allow them to enter a text string that could be part of a client name.
x - they type union
When the click the command button, their text entry is stored to an unbound text box on the form called criteria along with some wildcard operators with an end result looking like:
criteria = Like "*union*"
Everything is visible right now so I know it's working up to this point because I can see the text box called criteria get populated as shown above.
The results form uses a query that has a parameter that references my text box called criteria. The criteria for the ClientName field in the query is [forms].[frmSearch].[criteria] which in this example would be Like "*union*"
The query compe up blank every time. If I remove [forms].[frmSearch].[criteria] and type in Like "*union*" in the criteria field in the query window, it works just fine.
May be a better way to do this but I went with what I knew so far.
Thanks for any advice you have.
Your criteria in the query needs to be:
Like [forms].[frmSearch].[criteria]
You missed the keyword Like.
I tried that and got the same results. The keyword like is part of what gets passed to the query. What ever the user types gets coupled with what I thought was the proper format.
If they type health, the field criteria gets set to read as follows: Like "*health*"
In theory, when the query opnes, the criteria in the query, [forms].[frmSearch].[criteria] should be populated with Like "*health*"
But I get a blank result which I know is wrong. If I type in Like "*health*" in place of [forms].[frmSearch].[criteria] and run the query, it works fine and returns all nine records.
Balaji's suggestion is correct, but also there is an issue with your building of the criteria.
That you illustrate is actually a way to implement Query by Form (QBF). e.g., you would build the statement and then execute the query in code (with your example, it would be strCriteria = " Like '" & txtCriteria & "'"). To refer to the value from within a Saved Query just refer to the value of txtCriteria -- for the Criteria row: LIKE "*" & [forms].[frmSearch].[criteria] & "*"
This is perfect. Thanks for your help. This site really is an outstanding resource. I discovered it about 10 years ago and am quite happy to see it still exists!
You're welcome! thumbup.gif Good luck with your project!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.