Microsoft Access debuted in 1992 and recently celebrated its 25th Anniversary! Over the decades, Microsoft Access evolved with a large number of enhancements, database formats, features both new and old. and discontinued features.
It’s hard to remember all the changes. Fortunately, we created a comparison matrix that shows the different Microsoft Access versions and changes. See when versions were released, their latest service packs, database formats, linked tables, field types, security features, Windows Operating Systems, and many other features both new and old.
The National Archives and Records Administration (NARA) is the record keeper for the United States. Of all documents and materials created in the course of business by the United States Federal government, only 1%-3% are important enough for legal or historical reasons that they are kept by NARA forever.
To ensure the quality of work performed by their Facilities Management service providers, the National Archives and Records Administration performs both random and targeted inspections of completed work orders.
Inspection findings were documented on paper, which ironically, wasn’t efficient for the NARA. Reports were manually created to generate the service results. This manual process was time consuming and prone to human error.
FMS was selected to create a professional, multiuser system to collect the inspection results electronically and generate a variety of management reports.Within two months, we deployed our solution which offers data entry screens to replicate a variety of existing forms and many new management reports. An intuitive user interface made it easy for users without requiring extensive training. More importantly, we established a solid database foundation to improve NARA processes both today and into the future.
Stores inspection results into a shared database
Increases efficiency and accuracy of the collection and reporting process
Gathers information and performs statistical analysis in ways that were previously not available
Our Professional Solutions Group was recently asked to diagnose a Microsoft Access database experiencing recurring compile errors with code behind a form that looks like this:
If IsNull(Me.Comments) Then
where Comments is not a control on the form, but a field in the form’s RecordSource.
In general, this compiles and runs fine, but on seemingly random occasions while the program is running, it generates a compile error saying that that field was not found. But the field always existed on the form’s RecordSource, so why was this happening?
There are a few ways to avoid this problem:
Change all the Me. to Me! which is the proper way to reference a field in VBA code, if there is no control bound to this field.
Create an invisible text box that assigns its ControlSource to that field, give the text box a different name (e.g. txtComments), and reference the text box in code.
Deploy the database so its compiled state cannot be changed (ACCDE or MDE)
We prefer the use of the invisible text box so that we can reference the control name via the “Me.” syntax rather than “Me!”. The “Me.” syntax is verified when the code is compiled so that a typo with the control name is caught. This is preferable to a runtime error that gets triggered when the user encounters that line of code.
Though we knew how to fix this, we were curious to understand why the compilation wasn’t consistent across users. It also didn’t fail when a specific event occurred. It seemed almost random when the compile error arose. And the form triggering the error seemed perfectly fine with a reference to a field that exists in its RecordSource.
The Real Cause for the Compile Error
Through our own research and help from our Microsoft Access MVP colleagues, we discovered that the compile error was tied to programmatically changing the RecordSource of a form. The change is not necessarily on the form where the compile error is triggered.
Microsoft Access seems to reset its internal list of field references some time after the RecordSource is modified, which triggers the compile error. This explains why some users experienced it and others did not since it depended on whether the user opened a form that changed its RecordSource. It also explained why the error didn’t occur immediately after a RecordSource was modified.
Special thanks to Dirk Goldgar for pointing this out. Hope you never encounter this!
Additional Resources for Database Compile and Field Reference Issues
Learn how to sort on multiple date fields when null values exist by using a calculated field. By using the IIf function with Is Null, you can easily sort to see the most recent (or oldest) records.
We’ve included information on avoiding the IsNull function to maintain SQL Server compatibility. Additional information and examples are provided to show why using the NZ function (NullToZero) is not equivalent and returns the wrong results.
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.
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.
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.
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.
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.