posted
i'm in a little jam in Access and was hoping someone could help out.
I have a form that brings up all the information for a certain table. Now, there's a huge amount of records here, and I want a button on the form that can query the database and bring up a correspending record based on a specific Field search. And I have no idea how to do this.
Can anyone offer any help?
Posts: 8741 | Registered: Apr 2001
| IP: Logged |
posted
Also for my field search i'll need some sort of text box to come up so i can enter my search requirement. And i need it to do a partial word match also. i hope this is all possible. actually, i'm sure it is, and i'm sure i just don't know how to do it.
Posts: 8741 | Registered: Apr 2001
| IP: Logged |
posted
It's surprisingly easy. If all you want to do is find a corresponding record, what you're really making is a Find button. And there's actually a wizard for Find buttons; just place the button, and select the "Find a Record" function from the Record Management button.
Otherwise, you want to write a macro or script that applies a filter based on a field's content when the button is pressed, which is also easy.
--------
If you don't want to use the built-in Find function, here's the easiest way, IMO:
Create a filter (i.e. a saved query) that filters the records you desire for all matches on a variable within a specific field; if you surround the phrase "Enter the search term" by square brackets, and surround that variable with asterisks (separated from the variable by ampersands) on either side, applying the filter will cause a message box saying "Enter the search term" to apply, and anything typed in that box will become the partial-word search criteria for the record to which you've assigned the variable.
Next, create a macro that applies a filter to the form. Save it as "FilterMacro" or something.
Finally, create a button that runs "FilterMacro."
In this scenario, clicking the button causes the messagebox to pop up, prompts for user input, and then applies a filter containing that input.
There are more 133t ways to do this, but this is one of the simplest.
1. Add a form header. Be sure it’s a form header, not a page header.
2. In the header, place a text box called "Search_Criteria".
3. In the header, place a button called "Execute_Search".
4. Add an On_Click event for the button. In the code, place the line "Me.Requery".
5. Go to the form properties and go to the Record Source property. This should have the name of the table you are using for the form's data.
6. Click the "..." next to the property and answer "Yes" when it asks if you want to make a query based on the table.
7. Double-click the "*" in the table's list of fields in the query builder to add it as a column to the query.
8. In the second column of the query builder, add this in the field row:
Len(Trim("" & [Forms]![Form1]![Search_Criteria]))=0 Or [Field1] Like "*" & [Forms]![Form1]![Search_Criteria] & "*"
Change “Form1” to the name of your form and “Field1” to the name of the field you want to search on.
9. Add “True” to the Criteria row of the second column.
10. Close the query builder.
This isn’t the most efficient solution, but it works well. If you only need to search for the beginnings of words (for example, find “pr” in “prince” and “principle” but not in “appropriate”, you can get rid of the “*” & in the expression above. This will make searches faster if you have an index on the field you are searching on. If you only want to find exact matches, you can change that part of the expression to “[Field1] = [Forms]![Form1]![Search_Criteria]”. If you have an index, this will be very fast but will also limit the utility of the search.
This technique can also be used with combo boxes to allow selection of search criteria from a list (for example, state names, etc.). This works best with well normalized data.
I’ve made some assumptions about your Access abilities – if you need more specific help just leave another post with the item you have trouble with.
Dagonee
Edit: Forgot to add - this will return multiple records that meet the search criteria, which is usually a good thing.
*writes a genetic algorithm that evolves a field programmable gate array that parses mdb files into inductive statements written in Basque, which when solved contain the results of the search, which is input by holding up a megatokyo comic, with the characters which make up the search term circled, in front of a scanning laser controlled by the FPGA*
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
Dagonee, i went with your method. Sorry Tom, yours confused me. While i liked the idea of having a button i could click that brought up the message box, rather than having an input box AND a button..you lost me.
fugu, i'll get to yours later.
Posts: 8741 | Registered: Apr 2001
| IP: Logged |
posted
Dagonee's method IS my method. The only difference is that Dagonee has put the Search_Criteria object directly into the form, whereas my suggestion involved using a named object that did not EXIST in the form.
In MS Access, when you call an field name that does not exist, it prompts for user input using the field name you've chosen. So if you put a variable into a query like [Please type your name below], the user will be prompted with a box that says "Please type your name below," and the query field associated with that variable will contain whatever they choose to input.
In other words, you can leave out the Search_Criteria text box and replace all instances of Search_Criteria in the Query Builder with something like "Please type search criteria," and it will still work (unless I missed a step somewhere). Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
Tom's method is probably the straight up simplest to implement. Placing the controls on the form makes the user have to do one less click to run the query, lets you have all the records up if no query criteria is entered, and, if you're really ambitious, have multi-field queries.
This was the bread and butter for our main browse/navigation form in about a hundred Access applications my company did over the years. You can get very sophisticated results this way.
We used to use subforms to make it look prettier, though.
posted
Tom, i tried taking away the text box and replacing all instances of "Search_Criteria" with "Enter Search Term". but no dice. nothing happens when i push the button. Is there anything else i have to do to change Dagonee's method to yours?
Posts: 8741 | Registered: Apr 2001
| IP: Logged |
Let me explain what some of Dagonee's code does, and I'll explain the changes you'd need to make to use my approach (if desired).
Len(Trim("" & [Forms]![Form1]![Search_Criteria]))=0 Or [Field1] Like "*" & [Forms]![Form1]![Search_Criteria] & "*"
Placed in the RecordSource property of the form, this means that this is the primary query for the form. This will cause a problem, as we'll see below.
The first section, before the "Or" statement, checks to see if the Search_Criteria control located on the Form1 form is empty (has a length of 0.) In the original example, this returns all contents of the query if the button is clicked when the Search_Criteria field is blank.
The second section, after the "Or" statement, returns only those records whose Field1 field has content that matches the contents of the Search_Criteria control on Form1.
In my case, there IS no Search_Criteria control. For this reason, you don't need to check to see if it's blank before querying on it. So you can leave out the whole section before the "Or," as well as the "Or" itself.
Furthermore, the messagebox is not located on the Form1 form, so you can remove the ENTIRE [Forms]![Form1].[Search_Criteria] variable, and replace it with [Enter your search criterion] or something.
Now, here's the downside. Since we've put a blank variable into the form's RecordSource, it'll actually prompt you the moment you open it for "Enter your search criterion," because it needs that input to create the original query. (In Dagonee's example, using a field that starts out blank, it just sends a blank control to the query the first time out.)
For this reason, unless you WANT this behavior, I suggest using a filter rather than putting this into the RecordSource. I'll walk you through this procedure (again, under the assumption that Field1 has the data you want to search on.)
------
1) Build the form normally, including a normal RecordSource for its data.
2) Open the Form Properties.
3) In the Filter property, type [Field1] Like "*" & [Enter search criterion] & "*"
4) If you NEED to create a button that turns the filter on, you can do that in the Button Wizard. But there's already a button that applies any predefined filter on the toolbar -- which, if you're making a basic Access app, will probably still be available to users. So why not get them comfortable with the interface by telling them to click on the little gray funnel?
Printer-friendly view of this topic
![[Smile]](smile.gif)
While i liked the idea of having a button i could click that brought up the message box, rather than having an input box AND a button..you lost me.
