I use MS Access (Windows version 2003 still) for managing my important lists such as my inventory list for my eBay store. I deal primarily in one-of-a-kind goods so I have, over time, over one thousand listings. This is a lot to keep track of.
I needed a good way to search for and find items in various fields. You can use the “[control] + f” keyboard shortcut, or you can use the Edit | Find menu, to search, but Access defaults to match the whole field and you have to change it if you want something else. I usually don’t want Match whole field. I usually want Match any part of field.
Some people like to use keyboard short cuts. I’m more of a mouse person if there is a one-click way to do something. If there is only a many click way that goes through menus, even using only two clicks such as Edit | Find, then I will usually resort to pressing [control] + f. But, this article is not about whether one method is better than another; this article is about offering another way to search using an Access database form.
So, I have developed a small button system that I use to find items in a particular field. I put the button to the right of the field that it works on. I can get to the find dialog box set up the way I want it with one click.
I add the button in form design by using the button tool. If you choose the Record Navigation | Find Record action (a logical choice), then the wizard gives you the following code (we will change the code later):
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10,, acMenuVer70
The first line, Screen.PreviousControl.SetFocus is nice. It sets the focus of what field will be searched to the last field you touched. You could have one button for all searches this way, but you will have to first click on a field and then click on the button. That’s two clicks and not every user may understand that is the way it works.
The second line, DoMenuItem, is specific to each version of Access and means, in this case, the tenth item in the Edit menu. You would have to change this for every version of Access.
If you choose the Record Navigation | Find Next action instead, the wizard gives you the following code:
Screen.PreviousControl.SetFocus
DoCmd.FindNext
The FindNext is better than the DoMenuItem because you do not have to change the code for each version of Access. The dialog box that comes up when you click the custom button in this article has a Find Next button, so you really don’t need two buttons, Find and Find Next. Find by itself will do nicely.
The Access wizard coding is okay but you have no control of the parameters for the search. I dislike having to set the match box from match whole field to match any part of field after I forget and I have already searched using the whole field and cannot find what I know must be there.
I needed a way to search each field with one click, so I chose a button to do it.
I have multiple fields on which I want to be able to search. I did not want to write the same code over and over for each field. If I find a better way to code it, then I have to recode every instance of that old code. Having one procedure is much better.
My button sets the focus of the search to the one field to the left of the button and then it calls one procedure to invoke the find command. Every such button on the form sets the focus to the field at its left and calls the same, one function to perform the search.
Before I get to the code, I need to cover the concept that it matters where you put the procedure. If you have many buttons but only one form, then you can add the procedure in the code for the form itself. If you have more than one form, or if you wish to make the procedure more general in case you do need to use it later on in another form, then it is best to add this procedure to a module and not in your forms. I call my module General but you could add separate modules with one or more related procedures so you could easily import them into new databases as you need. This could be your FindRecord module. You find Modules in the main database window along with Tables, Queries, Forms, Reports, and Macros.
If you put your procedure in your form code, then the scope of your procedure is valid for that form only. If you have your procedure in form 1 and you need to call it in form 2, you will get an error because form 2 cannot find it. In that case, you would either have to add another procedure to form 2 and maintain two procedures, or better, put the procedure into a module so that both forms can find it and you only have to maintain one procedure.
Once you use a general module, your code references must also be general. You cannot use the Me shortcut for a field name as you can in a procedure within a form. When a procedure is within a form, the code interprets Me to refer to the form.
If you need to refer to a field name in a general module, you must use a general statement such as:
Forms.frmInventory.PurchaseTotal
or
Forms![frmInventory]![PurchaseTotal]
(You need to add brackets if the names contain spaces)
Here, PurchaseTotal is a field name in the frmInventory form in the Forms Access database collection. If this were in the frmInventory code, it would just be:
Me.PurchaseTotal
In our case, we will use the Me in the OnClick property in the form to set the focus to one specific field using the Me method and then call a procedure that has no need of referring to a field name, thus bypassing the Me problem.
Here’s what to do to create a handy find button:
* Make a button using the button tool and its wizard.
* Set the button to display text and set that text to F.
* Name the button cmdFind + Field Name, so to find on a field called InvNum the name would be cmdFindInvNum.
After the wizard completes, edit the button:
* Set the font size to 6, a small but readable size.
* Set the tab stop to NO because users do not need to stop at the button if they are tabbing through the form.
* Set the Status Bar Text and the ControlTip Text to Find Record so that users can easily remind themselves what the button does when they hold their mouse pointers over your button.
Size your button as small as you can and still be able to see the F. I use 0.1708 inches wide by 0.166 inches high with Arial text with my form grid spacing. Your size may vary. This is a good size because its height is the same as the height of my text and combo boxes so they all fit togther well in each line.
(After you create one button this way, you can copy and paste it for all of the rest of your buttons with all of these settings saved and set in the copy. All you have to do is to change the name of each button and add the OnClick Event Procedure below.)
Now you set the OnClick property to [Event Procedure]. You are setting the event procedure to act on one specific field. If the field name is ProductNumber, then write your event procedure as:
Me.ProductNumber.SetFocus
FindRecord
The first line, the SetFocus action determines which field your procedure will search and the Me.Productumber specifies just one field name. If you use Screen.PreviousControl.SetFocus, as the wizard recommends, for your first line, then you will search on whatever field you last touched with your mouse. This is nice, if that’s what you want, but it is not what I want this button to do.
The second line, FindRecord, calls your custom procedure. (In Windows. after you add your custom FindRecord procedure, you can highlight FindRecord here in the form code and right click and select Definition to immediately go to the code.)
Then add your custom procedure to a module and save it:
Public Sub FindRecord()
DoCmd.GotoRecord,, acFirst
SendKeys “%ha%n”, False
DoCmd.RunCommand acCmdFind
End Sub
The first command is optional so you can comment it out by adding a single quote mark in front of it if you don’t want to use it.
The first command sets the record pointer to the first record to start the search. I did this because I thought it best to begin searching at the beginning, but it is not necessary for this procedure to work.
The second command sends key codes to set up the find dialog box. Here are some possible options from a Microsoft Knowledge Base web page http://support.microsoft.com/kb/120912: [out]
Select the Match box: %h
Match Any Part of Field: %ha
Match Start of Field: %hs
Search Only Current Field (cleared): %e
Match Case (selected>): %c
Search Fields as Formatted (selected): %o
Select the Search box: %r
Search Up: %ru
Search Down: %rd
Select the Find What box: %n
For example, SendKeys %h selects the Match box and SendKeys %ha selects the Match box and sets the Match box to match any part of the field. You don’t have to do both; use a variation of the second statement. The same is true for SendKeys %r and SendKeys %ru or SendKeys %rd.
The particular combination in your procedure as written here sets the Match box to match any part of the field and it then selects the Find What box for the focus of your cursor. Thus, you are set to type in what you are searching for. Write your procedure with the settings that you want.
The second part of the SendKeys statement is an optional, boolean value specifying the wait mode. If it is set to False (default), control is returned to the procedure immediately after the keys are sent. If it is set to True, then keystrokes must be processed before control is returned to your procedure. Technically we do not need to specify it because we want False and the default is False; however, I like to specify it so it is clear to me six months from now that is what I wanted.
The % in front of each key value represents the Alt key in Windows, and I suppose it represents the Command key in Macs. In the code window, if you highlight SendKeys in the code and press the F1 function key; then a help screen will come up and explain general information about SendKeys. Don’t look to Access help for the specific Find key codes shown above; Access help is general and not all inclusive.
The third command runs the Access Find action as set up by you. This is finally the command that does what you want.
I admit that this is a crude way to do things, but this is what Access gives us. The SendKeys way is better than the DoMenuItem way but it can have problems in a multi-user environment, as I have read but not experienced.
You can experiment with different combinations of key codes. You can send keystrokes on different lines or combine them into one line as I have done. I recommend placing them all in onelike as the example does. If you use the %n key code, then put it at the end because it sets the focus to the Find What box and it should come last.
Try this out on your Access forms and see if this is useful. I find it helpful because general users will not know to press [control] + f or to use the Edit | Find menu to find something. Both are too nerdy. If they do know about them, it is my experience that they will be tripped up occasionally by the default match whole field setting and not know how to get what they want. A lot depends on training and frequency of use. People who are well trained will do better than poorly trained users. People who search infrequently may not remember as well as frequent users.
If users see a button next to the field, and if you teach them that F means find, they will probably use use it since searching is so fundamental to using a database they will want a simple way to search.
I will show you how to add other useful form buttons to Access forms in other articles.