IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, August 2006. Updated April 2010
This utility finds matching records with each keystroke you type. Access 2007 and 2010 provide this functionality in its datasheets, as other software and browsers do.
It consists of two controls:
You can use these controls on any form by adding one line to the form's Load event procedure.
The screenshot below shows how it matches multiple results in a Continuous Form. It also works in Form view.
The code examines your form, and identifies which controls to list in the Filter the field combo.
It avoids the control if:
It also avoids controls bound to these data types:
The filtering of combo boxes depends on your version. All versions include the combo if its BoundColumn is the display column. Otherwise the combo can be filtered only in Access 2002, and only if the RowSourceType is "Table/Query."
Memo fields in Access 97 and Rich Text fields (in Access 2007 and later) may not filter correctly.
To add this functionality to your database:
You can use the utility as it is. The options below let you configure how it works.
If there are controls you do not want to offer for filtering in your form, list them in the form's On Load property. Use quotes around each control name, and commas to separate them.
For example, to suppress filtering for Combo1 and Text99, set the form's On Load property to:
=FindAsUTypeLoad([Form], "Combo1", "Text99")
The utility finds the text anywhere in the field. To match the beginning of the field only, open the module in design view and change the line:
Private Const mbcStartOfField = False
Private Const mbcStartOfField = True
Access uses the asterisk as the wildcard character. Tables attached from a different back end may need a different character, such as the percent character. Specify the character you need inside the quotes on this line:
Private Const mstrcWildcardChar = "*"
For simplicity of installation, the code uses a generic error handler that does not log the errors. If you would prefer true error logging, replace this function with the one in this article: Error Handling in VBA.
Access 97 did not have the Split() and Replace() functions, so the Access 97 version of the utility has custom functions with these names.
If you update an Access 97 database to 2000, you can comment out these two functions. The later version includes these two functions already commented out, in case you need to copy the code back to an old Access 97 database.
If you have difficulties, this might help.
|I put the controls on my form, but they do not show up when the form is opened.||1. Your form must be bound to a table or query.
2. Both controls (cboFindAsUTypeField and txtFindAsUTypeValue) must be unbound.
|The form does not filter when I type a value.||Check the form's On Load property is set correctly.|
|My fields are not available in the combo.||See limitations above, to see if the controls should be offered.|
|I get an error when the form loads.||See step 3 under installation above. You may need to add the DAO reference.|
|The combo shows the wrong name for a field.||Attach a label to the problem control, and set the Caption of this label to what you want. (Set the label's Visible control to No if you wish.)|
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Find as you type'?
|Kelly Wornell says...|
09 April 2008
The line "Private Const mbcStartOfField = True" can also be set to False, and that is a great tool.
Is there a way to control this with a check box on a form? i.e. a user sometimes want to search for a value anywhere and then next search wants to only use that value at begining.
|Dave Tunstell says...|
11 April 2008
This would be possible with a small tweak to the code. First, change the private constant definition into a public variable:
Public mbcStartOfField As Boolean
Then for your check box AfterUpdate event, try:
Private Sub MyCheckBoxName_AfterUpdate()
mbcStartOfField = MyCheckBoxName.Value
Have your say...