Welcome

Welcome! Live Search Form with Results

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

In Lesson 33, we will walk through how to convert a natural language search form into a search-as-you-type form in Microsoft Access. As we discuss search-as-you-type functionality, you will see how to handle key events, the importance of ASCII codes, and the best event to trigger instant search updates with each keystroke. I will show you how to address quirks like handling the space character, keep the cursor position correct, and requery your search results in real time. We will also cover best practices such as indexing fields to ensure your search remains efficient.

Navigation

Prev: D08
Next: Lesson 01

Keywords

Access Developer, search as you type, instant search, search box events, OnChange event, key press event, SQL search, ASCII character codes, combo box search, real time search results, requery results, search performance, indexed fields, search multiple f

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

In lesson 33, we are going to turn our natural language search form into a search-as-you-type form. As we key in our search phrase, it refreshes the search results with each character.

One of the more popular questions I get asked is how do we make this a search-as-you-type form? In other words, I do not have to wait till I press the search button to have the search results updated. I would like to have it update every time I press a key. Kind of like how Google works now. As you are typing in your search query, you can see the recommendations for common search terms up here as you are typing.

Wouldn't that be nice here too if I could start typing in access and it starts showing me a list of courses on here that have that word in it right away? Now we know that with current combo boxes, if you click in here and start typing in something, they see, for example, if that item exists in this combo box in the list of items, you only have to type in a couple letters and the whole thing will appear.

What I want here is something slightly similar where as I start typing in ACC, the list of full search results appears down here. This word could be anywhere. It could be in the title, it could be in the description, or any field that we choose to search using the SQL techniques we have learned so far.

Now I do not want to lose what I have built so far. So I am going to make a copy of that course search form, copy, paste. This is going to be course search F. Let's make this one, and then we will work with this course search F here with the new stuff.

Now, there are a couple of different events that we have to learn about before we can go ahead with this. There are some pros and cons and some caveats and some other things we will talk about.

Click on this text box right here and bring up the property sheet. Go to events. There are a bunch of different events in here. These all fire at different times when you are working with the text in that text box.

The ones I am most interested in happen as you are pressing keys. Now there are a couple of different key events. Here is key down, key up, and key press. Now what is the difference? Well the key down event fires whenever you press any key down. The key up event fires when that key is released when you let it go. Key press only fires when you press an actual printable character key.

For example, key down will also fire if you hold down the shift key or the control key or any of the non-character keys. Key press only fires when you actually press a key that is going to go into that text box like a 4 or a D.

We are more interested for this particular usage in key press because I do not care about controls and all that stuff.

Now the problem with key press is that key press kind of takes place before the value goes into the box. You can go through and actually see how the timing works by just doing a series of message boxing. For the on key press, for example, you can message box what the key was. I will just give you a quick example.

Instead of message boxing it, let's put it into the MySQL field so you can see what happens here. Watch this. Let's go back to the text box. The key press event.

I will just say right here, SQL or MySQL equals. Now you could say whatever key it was and then pressed. Now this key ASCII right here tells you what key it was. That is an integer representing a numeric value for that key.

So, I will go, pressed, and then whatever that key ASCII was, just so you can see what it looks like.

Alright, so I will save that and then we will close it and reopen it. Now, when I press a key it should show up in that box down below. I am going to press the letter A. See, pressed 97.

I will tell you how to get what that code means in just a minute. I press number one. Press 49. See that? F is 102.

So that is how you can see what key was pressed. Now if I press a shift key nothing happens. If you wanted to test it with that key down event you could do that as well. But we only care about regular keys.

Now how can I tell what that was? Where am I getting that number from first of all? That is called an ASCII character code.

ASCII stands for the American Standard Committee for Information Interchange. They basically made up a table, excuse me, a table of character codes and what they represent. In fact, if you Google ASCII table, you will get this. This is actually on a website called asciitable.com.

You will see all the different codes. You are looking for the decimal code right here, for example, and what the corresponding character is that it represents. Pay no attention to the stuff in the middle here. That is the hexadecimal, octal, and the HTML code. Look over here for example. Character 65 is an A. Character 80 is a capital P.

Uppercase and lowercase letters are different. The uppercase letters are here, the lowercase letters are over there. You do not have to memorize any of this stuff by the way. You just have to know it for this function right here.

Now, I do not particularly care what the character codes are for anything except the only one we need to worry about for now is the space character. You will see why in just a minute.

Now, how do I determine what the space character is? All you really have to do if you want to know what it is, is just hit the space. It is a 32.

So character 32 is the space. Remember that. We will come back to it in a few minutes. In fact, I will put it down here in the notes. ASCII 32 equals a space.

There are a couple of functions you can use to go back and forth between the character code and the actual number. So if I go here, CHR key ASCII, the CHR function converts from the number to the character code.

So if I hit the thing now, if I put in a capital A, you will see 65A. If I hit a space, 32, and then of course there is a blank space there. The number 3 is a character code 51.

You can also go back and forth. There is another one which is the ASC of a value, and you give it the character and it will tell you what the string value is. That is just going backwards.

But we do not need that for today. All we care about is what the actual key that was pressed was. For now, get rid of that. We do not need that just yet. I just wanted to teach you about ASCII character codes and the different events and when they fire.

Now, the key press, key up, key down events all take place before the value is actually stored in the field. If you do not believe me, here we can go key press like that. I will say my SQL equals mySQL and put a VB new line. I do not think we can do that here. Yeah, we can try it, VB new line. I might not be able to get away with this. And search term. That will show you what the value of the search term is at the moment the key is pressed.

So, save that. You will see because timing is very important here. I am going to delete that, delete, and then press the A again, and then the B.

Now look, it says right here is 66B. The first character actually gets saved fine. But then the BCDEF... and the characters are not actually saved in that field yet. So I cannot do anything with this value. It is just displaying an A down here.

Now if I click off of it, sometimes then back on, sometimes it refreshes. There it goes. I set the focus down here and then back again, but it is still shy a character.

So there is another, better event that takes place after the value technically goes into the field. Now we are going to use the OnChange event.

This is a little bit tricky. It is all a matter of form timing. It is what goes at what times and what events open this up. There is another one here. Now there is after update and there is OnChange. OnChange actually fires each time you put a new character into that field.

But is that quite the same as a key press? It is a little bit different than the after and before updates. After and before updates happen when that data actually gets saved to the table, but since we are dealing with an unbound form here, that data is not actually going into a table.

So we want to use the OnChange event. It just works better. This was about two hours of experimenting to find the right combination of what works here.

So we need the OnChange event for this guy here. OnChange.data.co.v. Search term change. When the value changes, but notice I do not know what the key that was pressed was. So that is a bit of a trick too.

Now, let's see what happens. Just out of curiosity, when this event fires. I am going to say my SQL equals, and how about search term? Save that. Let's see what happens as I press each key now. Just to get some of this event timing down.

Ready? Course search. What's happened in A? Hmm, okay, nothing happened in a C. See, okay, I am not getting any value down here below. That is because we have to refresh this first.

The event is firing, but nothing is happening because there is no value down here. In fact, we can prove that by saying change like that so we can actually see something. The event fires, but there is nothing in the box. I am going to have to close this and reopen it for you to see this. Watch A. See? The event is firing, but this value has not been committed yet. It has not been saved yet.

Even though it is an unbound form. So we need one more step in here. Actually we need a couple more steps in here. What we are going to say is me.refresh right there. You type the value in, the change event fires, it refreshes it. So it basically commits that change to the text box. That is one step shy of the after update event, which commits it to the table, which does not happen. Now we should see what is in that search box.

Save changes, yes. Open it up. Ready? There is an anor. But look what happened though, I hit refresh. So now it has selected the whole thing. So if I had C now, now it is just a C. So it is working. The event is working, it is firing. But because I have to refresh it, it is essentially selecting the text in here. Now I need a way of saying, hey, put the cursor there after you do that. Put the cursor at the end of this thing.

We can handle that, we have done that before. We know how to move the focus around. Me.refresh and then search term.selstart. We know how to set the start of a selection equals the end of it. How long is that search term? Go to be Len search term.

However long that is. Now because I do not want to run into problems if that is blank, which it will generate an error, I am going to wrap that inside of an NZ and turn that into zero. Because if I try to say search term.selstart equals, there is no search term, and then it returns a null, then you will get an error message.

Now save it. Now let's see what happens here. A, B, C, D. Oh, look at that. E, F. The event fires. Well, actually, let's take it from the beginning. We type the value in. It refreshes the form, the event fires, and then we move the selection to the end of the text box. Confusing, yes, I know, confusing. It took me hours to figure this out, by the way.

Now one more thing we need to do is requery this guy right in the middle there. The event changes. Refresh the form, requery the list, which is all my SQL stuff above there. I can get rid of that now, and then move the selection to the end of that text box.

Let's try it again. I can get rid of this here, clear. A, C, C. Oh, look at that. E, S, S. Perfect. Back it up. E, X, C, E, L, look at that. How about VLOOKUP, V, L, O, O, perfect. So as I type each character in, it is updating my search results, just like a Google instant search, whatever they call it.

Now we have got one minor problem left to fix, and that is why I had to teach you about that key code, that ASCII code. Watch this, here is the problem. If I type in Excel, space, oh, what is happening? I am hitting space character, nothing is happening. That is because it is a quirk with Access, but whenever you type a value into a text box and you refresh it or save it, Access automatically trims off any leading and trailing spaces and tries to do you a favor that way, so you do not have any extra spaces in here.

Essentially what we have to do is we have to look and see what key was just pressed. If that key was a space, make a note of it, and do not refresh this list. Because essentially it does not change the query at all anyways, we are just adding them on the term.

Unfortunately, we cannot easily pass that value between these two functions, and that is why we had to learn that ASCII32 is a space. Now there are a couple ways you can do this. You can set up a global variable, you can set up a variable inside this form, or you can just do it the cheesy way with a checkbox.

I will drop a checkbox right here, which we will hide later, but go up to the design, find the checkbox, drop it down here, I am just going to chop that label off. I am going to name this checkbox, let's call it space pressed. This checkbox will indicate whether or not the last key pressed was a space or not. How do we know that well? We know that because of the search term key press.

I am going to say right here, if key ASCII, which is this guy right here that is sent to this function, equals 32, then space pressed equals true, else space pressed equals false. So that little checkbox will tell us now whether or not the last key that the user pressed was a space or not.

What do we do up here? I am going to say up here, if not space pressed, then go ahead and do this stuff. End if. Get it, make sense? You kind of had to understand how the events fire in what order to understand how this works.

Here we go. Let's do access space. The event did not fire, I was able to press a space in there without it getting erased. And then DLOOKUP. And our code down here is looking for anything that includes access or DLOOKUP.

What if I change this to there and search? And it did not find that exact phrase. What if I get rid of all that and go Excel, space, V, lookup. Looks good, looks good. And yes, the word Excel is in here. Open it up.

Does that code we still have? Let's see. Excel, search, open that up. Yep, it is in here. Just making sure.

So that is how you can do an instant search as you are typing space. As you are typing, it requires this list. And yeah, you could have just used a simple combo box up here, which would work for your search terms, your descriptions, but not the note fields. Now, you may find, depending on the size of your database, if you have gigabytes and gigabytes of data, and you are searching through lots and lots of stuff, you may find that this actually is too slow to requery your results every single time a key is pressed.

If that is the case, you can kind of flip this and only update the search results when the space is pressed. That is not how to do it. It is just reversing a couple of the conditions. You are saying only refresh that list if there is a space, which is certainly possible. That way the user would type in Excel, space, and then it refreshes.

But I am not going to go through all that code because it is not that much to change.

So that was one of the more popular questions that people ask me, how do I do a search on the fly? Like as I am typing into a text box, it searches through multiple fields or multiple tables and gives me a list of all the options. Well, there it is.

It is only to do it right. It is only eight lines of code, nine lines of code. It is this stuff here and that stuff there. You just have to put them in the right events so they fire in the right sequence.

Of course, that does not count all the SQL code that we built previously that handles the actual refresh of the list. But that is how you do it.

Since we really did not change that form too much, I just added some stuff to it. That is the new one. That is the old one. I will rename this one old. This one will just be the main one. So that is fine.

Oh, and one last thing to speed up your searches and sorts, make sure your fields are indexed. I just went in here and checked. We built this table kind of quickly. Make sure your fields are indexed. So the course name should be indexed, duplicates OK. The description should be indexed, duplicates OK. If you do not have these indexed, then that will definitely slow down your searches and sorts on those fields.

Thank you.

Intro

Quiz

Q1. What is the main goal of turning the natural language search form into a search-as-you-type form?
A. To refresh the search results with each character typed in
B. To shorten the search query automatically
C. To submit the form only after clicking a button
D. To display a static list of results

Q2. Which event is best to trigger the search when a user presses a printable character key in a text box?
A. OnClick
B. KeyPress
C. KeyDown
D. AfterUpdate

Q3. What does the KeyDown event do?
A. Fires when you release a key
B. Fires when you press any key down, including non-character keys
C. Fires only when printable characters are pressed
D. Fires after a value is saved to the table

Q4. Which event would you use if you want to trigger code AFTER the value actually appears in the text box?
A. KeyPress
B. BeforeUpdate
C. OnChange
D. KeyDown

Q5. In the context of this lesson, what does the ASCII code 32 represent?
A. The Enter key
B. The letter A
C. The Shift key
D. The space character

Q6. Why is the Combo Box's built-in search not suitable for this search-as-you-type requirement?
A. It only looks for exact matches at the start of items
B. It cannot display full search results as you type
C. It does not allow searching in multiple fields
D. All of the above

Q7. Why does typing a space into the Access text box sometimes not update the search results?
A. Access turns spaces into underscores
B. Access automatically trims leading and trailing spaces after refresh
C. The space key is not recognized by the text box
D. The event only works with numbers and letters

Q8. What does the CHR function do in VBA?
A. Converts a character to its ASCII code
B. Converts an ASCII code to its character representation
C. Clears the contents of a text box
D. Changes a field property

Q9. What is the main purpose of the line 'me.refresh' in the OnChange event code?
A. To save the field to the table
B. To refresh the value in the text box and the form display
C. To initialize the form controls
D. To restore default values

Q10. Why do we use 'searchterm.selstart = len(nz(searchterm, 0))' in the OnChange event?
A. To prevent selection of text in the box
B. To always set the cursor at the beginning of the text box
C. To keep the cursor at the end of the existing text after a refresh
D. To convert text to uppercase

Q11. How can you detect when a user presses the space bar in the search text box?
A. By checking if KeyASCII equals 32 in the KeyPress event
B. By checking the value of searchterm in the OnChange event
C. By using the AfterUpdate event
D. It is not possible to detect space bar presses

Q12. What is a method suggested in the lesson to track if the space bar was just pressed?
A. Use a hidden list box
B. Use a hidden label
C. Use a hidden checkbox
D. Use a public function

Q13. Why is it important to index fields (like course name and description) in the search table?
A. To allow duplicate values
B. To make searches and sorts faster
C. To enable foreign key relationships
D. To compress the database size

Q14. What could slow down search-as-you-type functionality in a large Access database?
A. Using too many forms
B. Large amounts of data and lack of indexing
C. Using combo boxes instead of list boxes
D. Adding too many events to the text box

Q15. If live search is too slow, what adjustment does the instructor suggest?
A. Only update results when a space is pressed
B. Turn off the OnChange event
C. Make the text box read-only
D. Save results to a new table each time

Answers: 1-A; 2-B; 3-B; 4-C; 5-D; 6-D; 7-B; 8-B; 9-B; 10-C; 11-A; 12-C; 13-B; 14-B; 15-A

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 is all about taking your natural language search form and upgrading it to a search-as-you-type form. Instead of waiting for a user to click the search button, we want the search results to update dynamically with every keystroke, much like the real-time experience you see when using Google search.

The idea is to allow the user to start entering a term like "access," and instantly see a list of matching courses below. This keyword could appear anywhere you choose to search - in the title, description, or any other field you set up in your SQL statement.

To preserve our existing work, I always recommend copying the original search form. This way, we can create a separate version, such as "CourseSearchF," and experiment there without worrying about breaking anything important.

There are several events available for text boxes that relate to key presses: Key Down, Key Up, and Key Press. Each one triggers at a distinct point. Key Down fires the moment a key is pressed (including non-character keys like Shift or Ctrl). Key Up occurs when you release a key. Key Press specifically fires for keys that produce actual characters - so letters, numbers, and punctuation, but not things like Shift.

For our purposes, we focus on Key Press, since we are interested only in actual input characters. With Key Press, you can capture information about which character the user typed. For example, using the KeyAscii value, you can get the ASCII code of the key - every character corresponds to a specific number. If you check an ASCII table, you'll find that A is 65, a space is 32, numbers, and so on. Uppercase and lowercase letters have different codes, and you do not need to memorize them, but it is good to know how this works if you need to capture or filter out certain keystrokes.

You can convert between the ASCII code and the actual character using built-in functions. For instance, CHR turns a number into a character and ASC goes from character to code. That can help you debug or tailor your search to react to certain keys.

However, there is a timing problem to consider. The Key Press, Key Down, and Key Up events all fire *before* the character is actually placed in the text box. This makes it tricky if you want to work with the full, updated value of the search input in these events.

To demonstrate this, you could set up the Key Press event to display the character and its code, or show the value of the search box before and after typing. You will see that often the value at the time the event fires does not yet include the character you just typed.

To solve this, we use the On Change event. This event fires every time the value in the text box changes, after the character has been added or removed. That means you can work with the updated value as the user types. The After Update event happens later, usually when you leave the control, so On Change is the right place for our instant search logic.

Inside the On Change event, we need to refresh the form so the change is committed, and then we need to requery the list of results based on the new input. However, when you call Refresh, Access tends to select all of the text in the box, which puts the cursor at the start or highlights everything. To give a smooth user experience, set the selection start to the end of the text by using the length of the current search term. Wrapping this in an NZ function ensures you do not run into errors if the box is empty.

Now, as the user types in the text box, the On Change event fires, the form is refreshed, the search results are updated based on the query, and the cursor stays at the end of the input. This achieves the search-as-you-type effect.

There is a minor issue you will notice with space characters. When you type a space and refresh the form, Access will often trim leading or trailing spaces from the search term. As a result, if you hit the space bar, the search does not appear to update as expected. Also, sometimes you may not want the form to requery on a space, or you may want to handle spaces differently.

To work around this, you need to know which key was last pressed. Since Key Press fires before the On Change event, you can set a hidden checkbox or use a variable to track whether the space key (ASCII code 32) was pressed. In the Key Press event, check if the key equals 32. If so, set your indicator (like a hidden checkbox) to true. Then, in the On Change event, check the indicator. If it is true, do not refresh the results. This way, pressing the space bar just adds a space, but does not unnecessarily requery the database.

By following this sequence, users can type in multiple words or entire phrases, and the search results instantly update after each keystroke, except for extraneous spaces.

Keep in mind that this method works best with smaller datasets. If your database is very large or the query is resource intensive, real-time requeries on every keystroke may become slow. If you encounter speed problems, you may want to adjust your logic to only refresh results on certain key presses, such as pressing space or enter.

Also, for best performance, always make sure that the fields you are searching against are indexed. Indexing fields like course name and description (with duplicates OK) will significantly improve search and sort speeds.

This approach lets you provide users with an instant-search experience, dynamically updating results as they type, across multiple fields using the SQL techniques that we have covered previously. The key is understanding when different events fire and working with the right ones to keep everything smooth and responsive.

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

Copying and modifying a search form in Access
Understanding key events: KeyDown, KeyUp, KeyPress
Difference between KeyPress, KeyDown, and KeyUp events
Capturing and using ASCII character codes
Using the CHR and ASC functions in VBA
Timing of events relative to text box value changes
Testing event timing with MessageBox and SQL field output
Working with the OnChange event for instant search
Refreshing and requerying the form and controls
Placing cursor at end of text box after refresh
Handling space characters with ASCII code 32
Creating and using a hidden checkbox to store state
Linking KeyPress and OnChange events for instant search
Preventing errors using NZ function with Len
Optimizing search performance in Access forms
Indexing fields to speed up search queries

Primary Topics

Access search forms, live search, OnChange event, key event differences, handling space character, text box events, SQL requery, indexed fields

Secondary Topics

ASCII character codes, combo box searching, user interface quirks