FMS developer Molly Pell is a guest blogger on the Microsoft Access developer blog. This post demonstrates a neat trick that you can use to filter a Continuous or Split form while your users are typing in a Combo Box.
Check out the post here: Using a Combo Box to Search as you Type
Read our new paper on:
Microsoft Access Database Architecture: Storing Temporary Data and User Settings
There are many things a user does with an application that need to be preserved either during processing, between screens, between sessions, or between application updates/versions. When designing a system, it’s important to consider what needs to be kept and where/how to do this. If designed properly, the data should also support multi-user environments.
Users are commonly annoyed to be forced to re-enter their last specifications when the application should start with that as its default. After all, a computer is supposed to be good at remembering things, right?
There are several ways to preserve user information during a session, on a PC, and/or between PCs:
Read our paper, Microsoft Access Database Architecture: Storing Temporary Data and User Settings for more details and tips.
Microsoft Access combo boxes let you display data for users to select values from predefined lists. Using them effectively simplifies data entry and accuracy.
Brush up on the ComboBox Basics from Microsoft, and check out our Top Five Tips for using Combo Boxes on Microsoft Access forms. Each of them has articles describing them in more detail.
1. Set Important ComboBox Properties
To use combo boxes effectively, learn about the following properties:
* LimitToList: Set this property to Yes to prevent values that are not in your list.
* AutoExpand: Set this property to Yes to automatically select a matching value in the list as you type.
* ListRows: Set this value to a high value so that the drop down shows as many list items as space allows.
Use the ListIndexproperty instead IsNull to properly validate that a valid ComboBox list item is selected.
Use the following syntax to automatically select the first item in a ComboBox when the form loads, or when the value of another control on the form changes:
Me.ControlName = Me.ControlName.ItemData(0)
On a form with multiple ComboBoxes, you may want to make the selection in one ComboBox limit the choices in another ComboBox. To do this, add code to the “AfterUpdate” event of the first control that updates the RowSource property of the second control.
Use Total ZipCode Database from FMS to automatically fill a ComboBox with a list of valid City / State combinations when the user types a zip code.
Choosing BetweenWe are often asked by Microsoft Office power users whether, why, and when they should use Microsoft Access versus Microsoft Excel. Especially when they are very comfortable using MS Excel and don’t understand the reasons why anyone would use MS Access or databases. We’ve written a new paper that describes the issues in detail:
For more information, visit Microsoft Access versus Microsoft Excel for Data Analysis and Reporting (Spreadsheets vs. Databases).
FMS is pleased to announce Total Access Analyzer 2010 is now shipping with support for both the 32 and 64 bit versions of Microsoft Access 2010. Total Access Analyzer is the world’s most popular Microsoft Access product winning every Best Access Add-in Award since 1994. This is the tenth major release of Total Access Analyzer since its debut in 1992.
Total Access Analyzer examines each Microsoft Access database to provide detailed documentation of individual objects and their relationships to each other. Comprehensive code analysis of VBA module code and macros is also performed. A powerful search features lets you find any string across all the properties, macros, and modules. Over 375 presentation quality reports are available with a wide variety of customization, sorting, and filtering options.
The popularity of Total Access Analyzer is its ability to help Access users and developers improve their applications, avoid errors that can cause their solutions to crash, and learn best practices to increase their skills. By using Total Access Analyzer to take over an existing application, during development, and before deployment as part of one’s quality assurance process, developers can avoid embarrassing mistakes and improve consistency and performance.
Most importantly, Total Access Analyzer detects 300 ways to avoid errors, apply best practices, and improve performance. By leveraging our years of experience and customer feedback, FMS has created the most powerful system for diagnosing Microsoft Access applications. For instance, Total Access Analyzer can detect broken references to tables, fields, forms, reports, macros, and VBA code that will cause the database to crash as soon as they’re encountered. It finds unused objects (tables, queries, forms, and reports), macros, classes, procedures, variables, constants, etc. to help developers get rid of unnecessary and old work.
Advanced analysis is also performed to detect inconsistent field definitions across tables, duplicate SQL definitions, macro command problems, etc. Multi-level object and code relationships are presented with three advanced hierarchical diagrams showing application flow, data flow, and object containership.
Microsoft Access users, developers, and consultants of all levels rely on Total Access Analyzer to deliver great solutions. “Total Access Analyzer is an amazing product that I’ve relied on and recommended for years,” said Sal Ricciardi Programming Writer for Microsoft Corporation. “It’s a huge time saver.”
Total Access Analyzer 2010 adds many enhancements in addition to supporting both 32 and 64 bit versions of Microsoft Access 2010. Its VBA module code parser now supports the conditional compiler syntax (e.g. #If VBA7 Then) that’s common for supporting 32 and 64 bit environments. There’s advanced macro documentation and analysis that includes a “macro compiler” to validate if macro commands have the proper number of parameters. Improved views and reports simplify the review and printouts of macro lines scattered in embedded macros across the forms and reports. New temporary variable analysis documents and detects undefined and unused TempVars set by macros and modules. A variety of other new suggestions were added to detect timer event inconsistencies, query performance enhancement opportunities, and reserved word conflicts with the upcoming SQL Server 2012. With all the new features, Total Access Analyzer remains the most powerful diagnostic tool for Microsoft Access databases.
“Total Access Analyzer provides the comprehensive documentation and analysis that empowers Access users and developers to takeover existing Access applications and enhance them,” said Luke Chung, President and founder of FMS. “Total Access Analyzer offers a cost-effective way to understand what’s going on in a database, detect errors, improve quality, and learn Best Practices. It should be part of the quality assurance process during development and certainly before shipping. If it doesn’t pass Total Access Analyzer’s review, it’s not ready for deployment. It’s fundamental to our own Access development efforts.”
Total Access Analyzer 2010 is available immediately from FMS for $299. Existing owners of Total Access Analyzer can upgrade for only $199. Total Access Analyzer is available via ESD and also comes with a professionally printed user manual and CD.
When working with ComboBoxes and ListBoxes, we often find the need to select the first item in the list by default. This can be done when the form loads, or when the rowsource values of the ListBox or ComboBox are changed.
We’ve written a new paper containing an explanation and sample database of how to do this with the ItemData(0) property.
Our example database contains a form with a ComboBox containing ProductCategoties, and a ListBox containing Products.
When the form loads, it selects the first Category in the list. When the Category is changed, the Products list is updated, and the first product is selected.
To learn more, read our tip on Microsoft Access Forms: Selecting the First Item in a ComboBox or ListBox and download our sample database.
In Microsoft Access, a common need is to have multiple combo boxes or list boxes on a form, and to have the selection in one combo box limit the choices in a second combo box or listbox. For example, consider an Address form containing State and City lookups. When you select a state, you want the list of cities list to be limited the selected state.
This is known as cascading combo boxes or synchronized combo boxes.
We recently posted a tip and demo database containing a sample of species, both plants and animals, categorized by their taxonomic rank (kingdom, phylum, class, order, family, and genus). When you select the value “Animal” from the Kingdom combo box, the Phylum combo box is updated to only show Animal phylum. The Species list box is also filtered by your selection.
To learn more, read our page on Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms and download our sample database.
by Teresa Hennig, Rob Cooper, Geoffrey Griffith, and Jerry Dennison Written by our long-time friends who are Microsoft MVPs and members of the Microsoft Access development team, Teresa Hennig, Rob Cooper, Geoffrey Griffith and Jerry Dennison wrote Microsoft Access 2010 Programmer's Reference, which also applies to Microsoft Access 2007.
Thanks to a special arrangement with the authors and their publisher, Wrox, we are pleased to offer a limited quantity of this book for FREE to purchasers of any of our Access product suites. A $45 value.
and click here to add it to your cart, or mention this offer when ordering.
Data normalization is fundamental to database design. Properly normalized data makes it easy to support an application over time and simplifies the querying, displaying, and reporting features of an application.
Unfortunately, we don’t always receive or have normalized data. Tables that require adding fields as the data changes over time are particularly problematic and violate the basic premise of database design where adding records is free, but adding fields is expensive:
Here are some updated resources detailing the value of data normalization, including a sample database and VBA code to transpose and normalize your existing data.
These and other related papers are part of our developer centers:
Hope these help you create more scalable, maintainable, and analyzable databases.
For advanced data analysis, check out our Total Access Statistics add-in product.
The Microsoft Access 2010 version of Total Access Analyzer is close to shipping and we are pleased to offer a FREE preview for you to try it.
Total Access Analyzer is the most popular Microsoft Access add-in and the winner of every Best Microsoft Access Add-in award ever. With Total Access Analyzer, you can truly understand what's going on in your database. Generate comprehensive documentation of all your database objects, get detailed table and field lists, module code printouts, form and report blueprints, and much more. Get detailed cross-references for how objects are linked to each other so you know exactly where each table, query, form, report, macro and module procedure is used. The Application Flow, Data Flow, and Object Flow Diagrams show how code and objects flow across your entire application. Over 300 professional quality and customizable reports are available.
Almost 300 types of issues are uncovered to pinpoint errors, suggest design improvement, and recommend performance tips. Find unused objects, missing field references, unused code and many other things that should be fixed before you deploy your applications. Many of the tips are recommended Best Practices for Access development, so you'll learn how the pros improve their design and development techniques.
We've added many new features to support Access 2010, generate more useful documentation, identify more errors, and suggest more design improvements and best practices. We've also improved the user experience with the ability to open a cross-referenced item in design mode while viewing the information, previewing multiple reports at once, and adding color to all reports. Here is a description of new features.
Take this opportunity to learn what Total Access Analyzer can do for you. See how it documents your databases and identifies errors and opportunities for improving them at the object and code levels. Discover why so many Access users and professionals rely on the program to deliver and create more robust solutions.
The preview version is available for immediate download and is fully functional. It expires on March 15, 2012.