Search Button
By Richard Rost
6 years ago
Create a Single-Field Search Button on your Forms. Filter Property.
In this Microsoft Access tutorial, I will teach you how to create a button that you can use to search a single field in your forms. Nice and simple for your users. We will also learn about the LIKE keyword and wildcard searches so you can find parts of a string.
Reis from St. Paul MN (a Silver Member) asks: The people who work in my office aren't computer experts. I'm trying to make my database as simple as possible for them to use. Do you have any tips for creating a real simple search or find button they can click on to find a record? All of those ribbon and right-click commands can be overwhelming, especially for non-computer people.
Members
I'll show you how to search for RANGES of numeric or date values. Specify a start date and an end date, or a min and max value. Use the BETWEEN keyword to search accordingly.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Update
Subscribe to Search Button
Get notifications when this page is updated
Intro In this video, I will show you how to add a simple search button to your forms in Microsoft Access, allowing users to easily find records by a single field with just one click. We will look at two methods: using the built-in Command Button Wizard for a quick solution, and then creating custom search buttons for individual fields with a bit of VBA code, including how to use wildcards for partial matches. This approach is especially helpful for beginners or users who find the standard ribbon and Find tools overwhelming.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to put a simple search button on your forms so your users can search for records by a single field with one click. Today's question comes from Reese from St. Paul, Minnesota, a Silver Member. Reese asks, The people who work in my office are not computer experts. I am trying to make a database as simple as possible for them to use. Do you have any tips for creating a real simple search or find button they can click on to find a record? All of those ribbon and right-click commands can be overwhelming, especially for non-computer people.
If someone who does not really know Access or even Microsoft Office well, looks at the ribbon, it can be extremely overwhelming. Usually, what I do for those people is I hide the ribbon, and then I just give them their own search tools right on the form they have to use.
Let me show you how to make - I will show you two different ways to make a button. One, we will use the button wizard. There is a real simple Find tool that might be good enough for you. If not, I will show you another, slightly more complicated way, but it gives you a better result in the end.
Grab yourself a copy of my customer template. It is part of the Blank Template package you can get from my website, absolutely free of charge. You will find a link in the description down below the video where you can download it. It basically has a couple of basic forms and a simple customer table with a few records in it.
The customer form opens up like this. There is the customer form, and you can see I have got First Name, Last Name, Email, and your basic fields. And, even though there is a big Find button right here, sometimes people just see all these buttons and get overwhelmed.
Usually, what I will do on their system is just minimize the ribbon. You can just double-click on one of these commands up here and it maximizes or minimizes the ribbon. And, of course, hide their navigation pane. I cover how to do all this in VBA code in my more advanced lessons. That way, all they see is a nice, pretty interface like this.
Now, for you and me to develop, let's open that stuff back up. Let's put a Find button on the form. First, let's go to Design View. There is a Command Button Wizard that does a single Find button.
Find the command buttons here. Click on your form somewhere. The wizard starts up, pick Record Navigation, Find Record, Next, pick whatever you want, binoculars or the text Find Record, whatever. The binoculars are fine. Next, give the button a meaningful name like FindRecordButton or whatever you want to call it, and then Finish.
Here is your button. Now, this button is going to work for all of the fields. I am going to shrink it up a little bit and put it right there. Save that.
When the user goes into the form, there is a little bit of training involved. All you have to do is tell them: If you want to search on a particular field, click on that field first, then click on the Find button. So, if I want to search by Last Name, click on Last Name first, then click on the Find button. It will open the Find and Replace dialog, ask you what you want to find - current field, whole field, all the basic Find dialog box. The same thing pretty much that you get by clicking up here.
Again, sometimes for some users, this could be overwhelming. So, I can show you how to do a real simple Find box that is specific to each field that you want them to find records based on. But it is going to involve a couple of lines of VB code. It is not hard.
For you, the developer, there is a little bit of setup time, but for your end users, it is going to be the simplest possible solution without any extraneous prompts and options and buttons and combo boxes and so on.
Let's go back to design view and get rid of that button. Delete it.
We are going to make a button that is specific to each field. So, if they want to search on First Name, we will have a First Name search button right there. Last Name, search button right there. I have found that this is the easiest and best solution for beginner or newbie computer users. It just works easily for them. I build it in my databases all the time.
Leave a little bit of space here. Now, find the command button again, drop it right here. This time, cancel the wizard. We are going to make our own button. You can just put 'Find' in here for now. We will replace it with an icon in a minute.
Shrink it up a little bit, just take it right here. Give this button a good name, double-click on it, go to All. Let's call this guy 'SearchFirstNameButton.' That is the name of the button.
Let's put the picture in now. Go to Picture where it says None, click the dot-dot-dot button and then whatever you want in here, you can use the binoculars. We get the binoculars, right there - binoculars - hit OK. And there you go. There are your little binoculars. Shrink that button up a little bit, make it fit right in that spot. Good enough.
Basically, you will put one of these for each field that you want to search on, because usually, there are only two or three fields in here that your people are normally going to search on. They are going to search on Last Name, First Name, maybe Email Address and Phone Number, possibly Address. The rest of the stuff they are not going to search on, usually. You can put as many on here as you want or just the two or three that they use most often, and then tell them if you need to search on more than that, you need to learn how to use the standard Find feature.
Let's put the code in this button. If you have never done any programming in VBA (Visual Basic), I have another video called Intro to Access VBA. You will find a link down below in the description below the video. Click on that, watch that first if you have never done any programming before.
We are going to right-click on this guy. Go to Build Event. If you are asked what kind of builder you want, pick Code Builder. I have that option set to Always On. I will show you how to do that in that video.
Now, right in here, this is the code that is going to run when we click on that button.
First, we need a temporary variable to store some information that the user is going to provide. So I am going to say Dim s As String. I have made a variable called s.
Now I have to ask the user for that value, s. So s = InputBox. InputBox basically puts a box up on the screen, kind of like a message box, so you can click OK and Cancel, but this actually prompts the user to type something in.
I do not have a TechHelp video for InputBox. I am going to make one. It is on my list for the future. I do cover InputBox a lot in my regular VBA classes.
First, you have the prompt - what do you want to prompt the user for? So, we are going to prompt them for "Please enter first name." Then, a title optionally on top - "First Name." Then you can give it a default value. Now, since I am searching on First Name, why do we not default it to whatever the First Name is on the current record? So just put in here [FirstName]. That is the First Name field. The rest of those options are xpos, ypos. You can control where the box goes. We do not need all that, so close that up.
So, s = InputBox. Let's just MessageBox it back and see what we got right now. MessageBox s. Let's just see what the button does at this point.
Close that, open it back up again. Click my Find button. I got my InputBox up here. There is the prompt, "Please enter first name." There is the title, and there is the default value, which is what is in there right now. See that? I put First Name in there. And you can change it. I am assuming they are looking for other Richards, but of course you can come in here and type in "Joe." Hit OK, and now it popped up "Joe." That is what we told it to do. I just MessageBox at this point.
Let's go back to the editor. Get rid of that MessageBox. The first thing I want to handle is if they click Cancel or if they do not return anything. So, if s is equal to an empty string, then Exit Sub. An empty string means what they did was they either clicked on Cancel or they hit OK with nothing in that box. That will return empty strings in both cases.
Now, to do the actual search, I do not like to use the Find and MoveNext commands because you can find a record, but it will leave all the records in the form and just find the first one. You can click on Find Next, Find Next, and Move Next to go through them. I do not like doing that. I like to apply a filter. What that does is it filters the records so the only ones you will see in the form now will be the ones that match your search criteria.
I am going to say Me.Filter. That is the filter for the current form. Set it to: FirstName = "s". Since s is a string, I have to make sure I enclose it inside of double quotes. It has to look like this: double quote, quote and s and quote, quote, quote, quote. I know that is confusing. Basically, these double quotes get converted into a double quote inside that string. So if s is Joe, that is going to look like: FirstName = "Joe". That will be inside of this string. So it is FirstName, then those double quotes go in, then the s, which is Joe, then another double quote at the end.
I have a video that explains what the deal is with these double double quotes. They are confusing, but you need to know them to use string concatenation. You can use single quotes in place of double quotes like this, but I do not recommend using them with names because you can have names - especially last names - with apostrophes in them, and that throws the search off. So trust me, do it like this. That will say FirstName = "Joe".
I just put a link in the description down below under the links section that will take you to a video I have called Concatenation. Putting these strings together like this is called concatenation. Merging multiple strings together better explains this whole double double quote thing. A lot of people ask me that, I know it is confusing. I cover it in my beginner classes, and people are always like "What?" So I made a separate video just on that topic.
Now that I have set the filter, I have to turn the filter on. So now I say Me.FilterOn = True. That activates the filter. Save it. Come back over here. Click on my button. There is my first name InputBox. "Richard" is the default. I will hit OK. It created a filter that said FirstName = "Richard" and now down here it says 1 of 1 filtered. Any "Richards" you have in here will now show up.
Let me turn the filter off by clicking on that button. Let's cheat and make a second Richard. Let me turn Jean-Luc Picard into Richard Picard. Now, I will come back over here at the beginning, and again, I will do a search. Click on that button. First name Richard, hit OK. Now it should say 1 of 2. There are two Richards in here.
So that is how you do a search by applying a filter. All you have to teach the user is click on the search button that you want, and when you are done, if you want to go back to all the records, just turn the filter off. That is much easier than teaching how to use the standard Find dialog.
Now, if you want to get a little more complicated, usually when you are doing a search you want to search for part of a name or part of a phone number or part of an address. You do not want to always have to type in the whole thing. You might want everybody whose name starts with "Rich" or "Ric." For that, we have to use wildcards.
Wildcards look like this. For example, if you have FirstName = "Richard", that is an exact match. But if you say FirstName LIKE "Rich*", then it has to begin with "Rich" and can end with any characters. If you put a star at the beginning, then it can begin with any characters and end with "Rich", for example. If you want it on both ends, you can do that too - put two stars on there.
I do not have a wildcard TechHelp video. Let me check my list real quick. I do not have a TechHelp video for that either, for the LIKE keyword doing wildcard searches. I cover it in depth in my classes. I cover wildcard searches and the LIKE keyword in my Access Beginner 5 class, and the InputBox function is covered in Access Expert Level 32. I will put links to those classes in the description down below. I am going to be making a TechHelp video on both of those topics eventually. It is on my list. Not sure how soon I will get to it. Keep an eye open for it. That list is pretty long; it is about 500 people right now.
We need to basically put this inside of here. So we have to put those little stars, little asterisks, right inside of that set of double quotes. It is going to look like: "LIKE '*" & s & "*'". I know it is confusing. I did not invent the system, I am just teaching you how it works. But this will give you the easiest possible solution for your end user.
Now that this is in place, save it, go back over here and see how it works. Click. I will just type in "RIC". Go. And nothing happened. What did I do? I forgot to change the equal sign to LIKE. You have to use the LIKE keyword. So you have to put your asterisks in there and use LIKE.
Let's try it again. Ready? Click. "RIC." Go. Boom! There are two results. Now, this could be Rick Picard, and you do not want to capitalize "Rick Picard." Close that, save changes, sure, open it back up again. Search. "RIC." All right, it got me and this one, Rick Picard.
Want to do the same thing for Last Name? Come in here, Design View, make some room, copy this button, copy-paste, slide it right in there. Open it up, use the name "SearchFirstNameButton." I will copy that, and we will call this guy "SearchLastNameButton."
Right-click on it, Build Event, just copy this code right here. Copy and paste. Usually I do not like duplicating my code, but this is not that big of a deal. Just change it to Last Name. You have a bunch of places where you have to change it too. So, Last Name, the default here is Last Name, and then in here, Last Name.
If I was going to make 30 of these, then yeah, I would want to avoid duplicating code. Let me get rid of some of this extra space in here. Save it, close it, come back out here, open it up. Now, let's search for just an 'R' in the Last Name. Go. There is one there - Kirk. Looks like everybody has an R.
Let's search for "RO." Search. "RO." Go. There is me, and Troy, and that is it.
See how easy that is? Now you know how to put a search button next to any field you want.
If you want to learn more about making these search buttons, in the extended cut for my Silver Members and up, I cover making buttons to search ranges of values for numeric and date fields. For example, Family Size, Customer Since, Credit Limit - all those things. You will get two prompts this time, a start date and an end date, or enter minimum value, maximum value, and so on. Then, I show you how to apply a filter using the BETWEEN keywords so we can search between two values. That is in the extended cut for members.
How do you become a member? Click on the Join button below the video. Silver Members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different membership levels that are available, each with their own special perks.
But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What is the primary reason for creating custom search buttons on Access forms as described in the video?
A. To provide a more streamlined experience for non-technical users
B. To increase database security
C. To speed up form loading times
D. To allow users to edit multiple records at once
Q2. What does minimizing the Access ribbon and hiding the navigation pane do for end users?
A. Prevents them from making changes to the database structure
B. Makes the interface simpler and less overwhelming
C. Increases the speed of the database
D. Allows only advanced users to access the form
Q3. Which built-in tool does the Command Button Wizard add to a form when creating a Find button?
A. A search combo box
B. The standard Find and Replace dialog
C. A custom VBA prompt
D. An automatic filter based on the last name
Q4. When using the Command Button Wizard's Find Record option, what must users do before clicking the button to search by a specific field?
A. Enter the value in a separate search box
B. Click on the field they wish to search first
C. Filter the form manually
D. Right-click on the field
Q5. Why might using the built-in Find button be confusing for some users, according to the video?
A. It automatically searches every field
B. It opens a dialog with too many options and prompts
C. It lacks filtering capability
D. It deletes records when searching
Q6. When creating a custom search button for a specific field, what programming method is used in the video?
A. Embedded macros
B. VBA code with the InputBox function
C. Property sheet configuration
D. Table relationships
Q7. What is the purpose of using the InputBox function in the custom search button?
A. To display an error message
B. To ask the user for a value to search for
C. To automatically select a record
D. To restrict access based on username
Q8. What is applied to the form to show only the matching records after the user enters a search term?
A. A sorting order
B. A filter property
C. A macro command
D. Form requery method
Q9. Why should double quotes be used when setting the filter for a text field in VBA?
A. Single quotes are not recognized by Access
B. Double quotes ensure the value is treated as text
C. Double quotes are faster to type
D. It is required for all field types
Q10. What common search enhancement does the video suggest for allowing partial matches?
A. Numeric search
B. Using the LIKE keyword with wildcards
C. Implementing search macros
D. Adding a search combo box
Q11. What do the asterisk symbols (*) represent when used with the LIKE keyword in the filter?
A. Mandatory values
B. Text length limiters
C. Wildcards for any number of characters
D. Required numeric values
Q12. To make a search button for another field (such as Last Name), what does the instructor recommend?
A. Reusing the same button for all fields
B. Duplicating the button and updating the VBA code for the new field
C. Creating a complex macro
D. Using only one generic search box per form
Q13. What does the instructor suggest as a best practice to avoid code duplication if you need many search buttons?
A. Avoid VBA entirely
B. Use a loop in VBA to create all buttons automatically
C. Find a way to generalize code for reuse, though for two or three buttons it is fine to duplicate code
D. Use table relationships instead
Q14. What additional search feature is offered in the extended cut for members?
A. Searching across multiple tables
B. Searching for a range of numeric or date values using BETWEEN
C. Exporting filtered records to Excel
D. Auto-generating reports
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-C; 14-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how to create a simple search button on your Access forms, making it easy for users to find records by a specific field with just one click. This method is especially helpful for people who are not experts with computers and find the standard Access interface, with all its ribbons and right-click menus, to be overwhelming.
When designing a database for users who are not very familiar with Access or even Microsoft Office, it is best to hide features like the ribbon and navigation pane to simplify the interface. By presenting them with a clean, straightforward form that contains only the buttons and tools they need, you can make the database less intimidating and more user-friendly.
There are two approaches for adding a find or search button to your form. The first method uses the built-in Command Button Wizard to add a general Find button. This button opens the standard Find and Replace dialog, allowing users to search for information in any field, depending on where the cursor is when they press the button. It is quick to set up. All you need to do is add a command button, choose the Record Navigation and Find Record options, pick an icon, and assign the button a meaningful name. Placing and resizing the button is simple, and users just need to learn that they must click on the field they want to search before pressing the button. The familiar Find dialog will then appear.
However, for beginners, even this can be intimidating. Instead, a more tailored solution is to provide a dedicated search button next to each field. For example, you can place a 'Find' button beside the First Name and Last Name fields. This approach involves a bit of setup for you, the developer, but it is the most straightforward and intuitive option for your users. They will know exactly which button to use for which field, and there are no confusing options or extra steps.
To set this up, you start by adding a command button in design view, but this time, you cancel the wizard and label the button as 'Find'. Give the button a meaningful name based on the field it will search. For example, if it is searching for First Name, call it 'SearchFirstNameButton'. You can also add a binoculars icon to make it visibly recognizable as a search function.
You should repeat this process for any field you want users to be able to search, like Last Name, Email, Phone Number, or Address. Generally, users only need to search by a few fields, so you can decide which ones to add based on their needs.
The functionality of the button is managed with a few simple lines of VBA code. When the button is clicked, an InputBox prompts the user to enter the search value. You can even set the default value to whatever is currently in the field on the form, making things even easier for the user. If the user cancels or leaves the entry blank, the code simply exits.
Instead of using the standard Find and MoveNext commands, which can be confusing and show all records, this method applies a filter to the form. That means the form re-displays itself showing only the records that match the user's input. If you have multiple records with the same value (for example, two people named Richard), the filter will narrow the list down, making it very easy to see only the matches. If the user wants to remove the filter and see all records again, they just need to clear it.
To make your searches even more flexible, you can use wildcards with the LIKE keyword in your filter. This allows users to search for part of a value, such as all names starting with 'Ric'. Setting this up requires tweaking the code so that it applies a filter using the LIKE operator with asterisks as wildcards. This lets users find any records where the field contains the entered text.
You can easily copy and modify the button and its code for other fields, like Last Name, by adjusting the field names in the VBA code and the InputBox. If you are adding just a few search buttons, this duplication is straightforward and manageable.
For those who want to see how to build search buttons that work for ranges (such as date or numeric fields), in the Extended Cut for Silver Members and higher, I demonstrate how to prompt users for a start and end value, and then apply a filter using the BETWEEN keyword. This makes it possible to search for records within a range, like finding customers who joined between two dates or whose credit limits fall within certain values.
If you are interested in accessing the Extended Cut videos where I show these techniques step by step for different data types, you can become a member on my website. There are several membership levels, each with its own benefits, including access to live sessions and additional content for Silver Members and higher.
Even if you do not join as a member, rest assured that my free TechHelp videos will continue as long as there are viewers finding them useful.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Hiding the Access ribbon and navigation pane for users
Adding a Find button using the Command Button Wizard
Customizing the Find button with icons and names
Training users to use the Find button for specific fields
Creating individual search buttons for specific fields
Naming and placing custom search buttons on forms
Assigning icons to custom search buttons
Writing VBA code to prompt user input with InputBox
Filtering form records using VBA based on user input
Handling empty input and cancel actions in VBA
Using Me.Filter and Me.FilterOn to filter form records
String concatenation for building filter criteria in VBA
Switching from exact match to wildcard searches using LIKE
Updating VBA code to allow partial matches with wildcards
Duplicating and customizing buttons for multiple fields
|
