Microsoft Access: Applications and Utilities

Provided by Allen Browne, August 2006.  Updated April 2010.


VBA function list:

Caption4Control()

CaptionFromHeader()

FindAsUTypeChange()

FindAsUTypeLoad()

FirstVisibleColumn()

GetFilterField()

HasUnboundControls()

LogError()

MaxParentNumber()

ParentNumber()

ShowHideControl()

Find as you type - Part 2: How it works

This article documents the code in the Find-as-you-type utility. You can use the utility without working through this technical explanation.

To benefit from this article, you must read it in conjunction with the code in the ajbFindAsUType module of the utility. You can read the code in another window.

Most of the code arises from trying to handle any form, choosing the correct fields for filtering, presenting them in the tab order of the controls on the form and in the order the the controls on the pages if the form has a tab control, determining what name the user will recognise for the control, matching values in combos where the display column is hidden, and handling the differences in versions of Access.

Initializing the form

When your form loads, it calls the FindAsUTypeLoad() function, passing a reference to the form itself, and optionally the names of any controls you want to exclude from filtering. The ParamArray keyword lets you to pass any number of parameters, as an array.

The function checks it was called from a bound form (the RecordSource is not a zero-length string), and that the form has the two unbound controls we expect to find: cboFindAsUTypeField (the combo where we will the list of controls that can be filtered), and txtFindAsUTypeValue (the text box where the user enters the value to find.)

If these controls are not present, trying to refer to them will error, so we pass this task to HasUnboundControls() where the error can be handed gracefully. This function uses ParamArray as well, so it can check for any number of controls. It returns True if all the named controls are found, and are unbound (i.e. their ControlSource property is a zero-length string.)

After checking the 2 controls exist, FindAsUTypeLoad() sets the AfterUpdate property of the combo and the OnChange property of the text box. We do this programmatically so the developer needs to set up only one event at design time.

Our task is to load the combo with the names of the controls we can filter, and we want them sorted the same as the Tab Order of the controls on the form. But if the form has a tab control, each page has its own tab order so we need to present the controls in tab order within the page order of the tab pages, with any controls sitting directly on the form first. We therefore call MaxParentNumber() to loop through the controls on the form, and find any tab control. If this function finds one, it returns the page count less one (since PageIndex is zero-based); otherwise it returns -1 to indicate the control is sitting directly on the form (our constant mlngcOnTheForm.)

These controls are then written to the array astrControls(). This is an incredibly inefficient use of string memory, but it serves our purpose, so we ReDim the array such that the first dimension has enough places to handle all the controls on the form, the second has enough for all the pages of the tab control, and the third dimension differentiating the control name and the field name to use for filtering this control.

We also need information about these fields, which can be derived by examining the Fields in the RecordsetClone of the form. So, we loop through the controls on the form. We ignore any control that is not visible, and anything that is not a text box or combo. Then we loop through the exception list array and skip the control if we find its name there. Next, we examine its ControlSource to find out what it is bound to, and skip it if it is unbound (ControlSource is a zero-length string) or bound to an expression (ControlSource starts with "=".) Otherwise its ControlSource must be a field name, so we examine the Type of that field in the RecordsetClone of the form, and skip the control if its source field type is Boolean, OLE Object (dbLongBinary), indeterminate (dbBinary), replication id (GUID), or any of the complex data types in Access 2007 and later (Type > 100.)

If we have not set the flag to skip this field, we call GetFilterField() to retrieve the name of the field to use in the Filter for this control. The function returns the field name for text boxes, and for combos where the display column is the bound column, but other combos need special treatment. So, for combos, GetFilterField() calls FirstVisibleColumn() to determine which column is visible. FirstVisibleColumn() examines the combo's ColumnWidths property. If the property is a zero-length string, the first column (0) is visible, so we return zero. Otherwise we use Split() to parse the combo's ColumnWidths into an array, and loop through the array until we find a value greater than zero. If the array ran out before the number of columns did and we did not find a visible column, then the next column after the end of the array is the first visible one.

The result from FirstVisibleColumn() allows GetFilterField() to determine whether the combo's display column is the bound column. (Column() is zero-based, BoundColumn is not, so we subtract 1.) If not, Access 2002 and later can still filter on the value using this syntax:
    Lookup_<control name>.<field name>
Our code therefore uses this syntax. In each case it adds square brackets in case there are strange characters in the control or field name.

But that syntax does not work in Access 2000 or earlier. GetFilterField() therefore calls SysCmd(acSysCmdAccessVer) to get the version information. Val() trims any trailing non-numeric characters (such as "12.0 beta"), and Int() drops the minor version information, so we can see if we have 10 (Access 2002 version number) or later. If we are not able to determine the field name, or don't have an adequate version, we set the cancel flag so the procedure does not return its default (the name of the field the control is bound to.)

Back in the original FindAsYouTypeLoad() procedure, if GetFilterField() did not return a field, we mark the control as skipped. Otherwise we need to know which tab page this control is on. That will error if the control is sitting directly on the form, so we call ParentNumber() to handle the error. The function returns mlngcOnTheForm (-1) if the control is on the form, or the PageIndex if the control is on the page. Since we are handling text boxes and combos only, the control's Parent cannot be another control (as it could be for an attached label, or a control in an option group.) We can now use these index values to add the name of the control and the name of the filter field to our array.

Once we have loaded this array with all the fields in tab order, for each tab page, we loop through the array to build the combo's RowSource string. The array had enough elements for all the controls on the form, but only a few were actually used, so we skip those that are zero-length strings. cboFindAsUTypeField has 5 columns, from a Value List, so we create a string with 5 entries for each field. The constants (General Declarations section of the module) identify what goes in each column. (Several of these columns are not used; they are there for debugging/expansion.)

The combo's first column is hidden, containing the name of the control that yielded this entry. The second column is the visible label - what the user sees when choosing the field to filter on. This label is derived from the Caption4Control() function described below. The third column is the control type (acTextBox or acComboBox.) The fourth column is the name of the filter field. The last column is the data type of the filter field (not used: since the filtering involves pattern matching with wildcards, it is all string-based.)

Caption4Control() determines what name is shown to the user for this control. If the control has an attached label, that's our first choice. If it does not have an attached label, referring to it generates error 2467, which will Resume Next. If the attached label gave us no result, our second choice would be to see if this is a continuous form with a label in the column header directly over this control. Lots of things can go wrong in reading that, so we call CaptionFromHeader() so it can handle the errors. This function checks the CurrentView is Form view (1), the DefaultView is Continuous Form (1), and the form has a Form Header section (using error 2462 to jump out if it doesn't.) It then loops through the controls in the Form Header, searching for a label with a Left property within 120 twips (one-twelfth of an inch) of the Left of the control, and returns the Caption of that control if we found one. Caption4Control() then strips any trailing colon or any hotkey (ampersand) in the proposed string, while retaining one ampersand if there were two in a row (the triple Replace() lines.) And if we still got nothing, we just return the name of the control to show to the user.

Back to FindAsYouTypeLoad(). We remove the trailing separator, assign the string to the RowSource, default the combo to the first item in its list, and set the return value of the function to True since it succeeded.

Finally, if we did not succeed, we hide the combo and the text box from the user, since they are not set up to work correctly. But hiding them could fail (e.g. if the reason for failure is that the controls are not on the form), so we call ShowHideControl() so the error can be handled in that routine.

So, finally, our form is initialized.

Perform the search

FindAsUTypeChange() is called in the Change event of txtFindAsUTypeValue, so it fires with each keystroke. It is also called in the AfterUpdate event of cboFindAsUTypeField. If the function was called from the text box, we must use its Text property, since its Value has not been updated yet. But if the text box does not have focus, it does not have a Text property (unlike pure Visual Basic.) So, our first task is to determine whether the text box has focus, by checking the name of the ActiveControl of the Form. (In Access 2000 and later, we could use:
    bHasFocus = (frm.ActiveControl Is frm!txtFindAsUTypeValue)
but that code fails in Access 97.)

If the form has uncommitted edits, we will have to save the record before we can apply the filter. But saving has the side effects on our unbound text box: the Value is updated, the selection changes, and we don't know where the insertion point was. So, if the text box had focus, we read the Text, and remember the insertion point before we save the record by setting the form's Dirty property to False.

Next, we read the name of the filter-field from the hidden column in the combo where we stashed it during the initialization.

If there is no value to find or no field to filter, we show all records. Otherwise, we create the filter string, assign it to the form's Filter, and turn FilterOn on. The filter string consists of the field name, the Like operator, and the value. The wildcard character is whatever is in the module-level string const mstrcWildcardChar. The module-level boolean constant mbcStartOfField indicates if the wildcard should be added to the end of the value only (True), or to the beginning and the end (False.)

Having applied the filter, we now have the job of restoring the insertion point again in the text box if it had the focus.

And finally, we set the return value to True, in case someone cares whether the code completed successfully.

Other

The LogError() function is really just a place holder for the one in this article Error Handling in VBA.

The Replace() and Split() functions are needed for Access 97 only. We will not document them here, as their functionality is the same as the built-in functions with those names documented in Access 2000 and later.


Home Index of tips Top