GRATEFUL DEAD

DIVERSIONS

OUTDOORS

ALTERNATIVE

MISCELLANY

INTERNET

INNOVATIONS

Microsoft Access Programming Tips
by John Jarvi

I use the following guidelines when working in Microsoft Access, Versions 2.0 thru 8.0.

This document contains some things which I may do in Access Development. It may be freely distributed - that is my intention. What you do is your choice.

(Regarding Table Definitions, the same may be appropriate to SQL Server Databases, Sybase, etc.)

Table of Contents

1. Naming Databases, and Database Objects
1.A. Characters in Object Names
1.B. Naming Conventions - General
1.B.1. Naming Conventions - DateStamping Databases, or Database Objects
1.B.2. Naming Conventions - Within the Database
1.B.3. Naming Conventions - the Database (and related Files)

2. Tables
2.A. Table Fields - General
2.A.1. Data Typing - General
2.A.2. Data Typing - Special
2.B. Table Fields - Special Purpose

3. Queries
3.A. Query Types
3.B. SQL Statement Types

4. Forms
4.B. Access Basic Code: Specific Tips
4.B.1. "ComboBox: The NotInList Event"
4.B.2. "Null Values: Reducing Confusion About IsNull"




1. Naming Databases, and Database Objects

1.A. Characters in Object Names

Microsoft allows a great deal of flexibility in the Names of Database items, whether for Reports or Fields. I usually avoid using anything but letters in Names, although occasionally I use numbers. For length of the Name I consider about 20 characters to be optimum.

Microsoft allows the use of not only letters, but numbers, spaces, hyphens, and many other special characters, in the Names of Database items. Also the Names may be rather long, perhaps 64 characters for some items. For example, a Field may be named "Inv $ Ytd", "Invoice_Dollars_YTD", or "IdY". It is obviously a matter of personal choice - there is no right way to do it.

I prefer the "proper-case" approach to Names. "FirstName" is both self-explanatory, and easy to read, and thus is a perfectly good Field Name. Plus this choice can save me a lot of time in design work. In most any design situation of an Access Database, the User may double-click a "word" and that "word" is highlighted, ready for perhaps copy-and-pasting. That is, if the "word" is a consecutive string of letters and / or numbers. However, if that "word" contains special characters OR spaces, not all of the characters are highlighted.

The second consideration, the number of characters in the Name, can also affect not only design time, but accuracy. Often in Access design mode, where a choice of some object, i.e., Field or Report, a DropDown List Box will appear. Object Names much longer than 20 or 25 characters may appear truncated. Which may cause the Developer to make the wrong selection. Or else the Developer can "waste" the time to enlarge the DropDown List Box to see what was truncated. On the other hand, using very short names, like "FN" for FirstName (or FullName), seem to me some kind of false economy. That which is subject to interpretation is more likely to cause errors.

1.B. Naming Conventions - General

A Name is a succinct way to refer to something. It need not be descriptive of what it designates - consider people's names. However, it may be descriptive. A Naming Convention is a set of rules to be used for naming items in some group. A good Convention leads to the choice of Names which are descriptive of the object named,. Further each Name will convey some information about the relation of the individual to the group. It may also result in the objects, when in a sorted list, to appear in a meaningful sequence.

1.B.1. Naming Conventions - DateStamping Databases, or Database Objects

A Database, or Database Object, may need to include DateStamping in its Name. If thoughtfully setup, this can be done so the dating is NOT subject to interpretation, in context of the Application. This can be accomplished with a minimum of characters in the Name too. The Date element should be the last component of any Name.

The Name's Date element need only consist of numbers, and rarely letters, perhaps a "Q" for quarter. Dashes and slashes are entirely unneeded in most cases.
Use Numbers, not Letters and Acronyms, when referring to Months. Consistently using 2 digits, instead of maybe 1 and maybe 2, for Months, as well as Days, prevents confusion. Further this leads to Name lists being sorted consistently. Likewise use as many digits for a Year as possible - 4 is ideal, and 2 is better than 1.
The sequence of the date's elements used should be largest cycle to smallest. Year followed by Quarter is preferable to the reverse. Year - Month - Day is much better than Month - Day - Year.

For example, Databases related to Evergreen Quarterly Activity are named Ever98Q4.Mdb and Ever99Q1.Mdb, not Ever1Q99.Mdb and Ever4Q98.Mdb. Short Names like these might be required where a Novell LAN restricts File Names to 8 characters. In the case of Tables, less restricted in length, Names might be like tblLbeSales19990801 and tblLbeSales19990901.

Distinguishing current vs. archived data may also be methodical. When a Datestamped Table exists, I usually create a special variation of it. This variant Table is for managing the "current data". This allows the Datestamped Table to simply store data. All processes, queries or code, refer to the Name of the "current" Table. This minimizes the renaming of Tables, and the inherent potential for mistakes when Names are changed. If a Report is to be rerun, the Table(s) with archived data can be "Copied As" the "current" Table(s). Thus the integrity of archived Data is not jeopardized yet any programming automatically works since it refers to the generic Name(s) of "current" Tables.
The Naming Convention for a current Table is to substitute the suffix "Cur" for the Date Element, i.e., tblLbeSalesCur & tblLbeSales19980901, respectively.
The Data processing cycle begins with deleting any Records in the current Table, then new data is added to that same Table. Any Reports are designed to process Table(s) named "…Cur". At the completion of any processing, the current Table is "Copied As" the appropriately named Archive Table.
Please note "Copied As", i.e., copy-and-paste, is something which I seldom do with Tables. Likewise I only rarely run MakeTable Queries. Normally I copy a Table Structure, then run an Append Query to populate it with data. When necessary to repopulate a Table, I run a Delete Query followed by an Append Query. This method provides a more certain way of maintaining Tables, which are consistent in structure. Copy-and-Paste on a Linked Table may blank out some Field properties. MakeTable Queries, not only blank out some Field properties, but alter others, especially Field Lengths.

1.B.2. Naming Conventions - Within the Database

The use of a Naming Convention for Database Objects can save time - a lot of time. Thoughtfully picking an Object Name may take a few extra seconds - one time. Not using a Naming Convention may waste a few extra seconds - many times. Is this Object which is called "Customer", a Table, or is it a Query based on a Table, may be a question arising repeatedly,

I might have objects named tblCustomer, qlkpCustomer, and frmCustomer in an Access Database. All these would have some obvious connection with Customers. The names also indicate that they are a Table, Lookup Query, and Form, respectively. That is because I use the Lezynski-Reddick Naming Convention for Access. This is probably the most widely used Convention, but you can use another, or create your own.

The point is that using some Naming Convention will almost certainly save a lot of time in the lifetime of a database. For example, when selecting the RecordSource for a Report, a DropDown List Box appears filled with the names of ALL the Tables and Queries in the Database. In a modest size database, this may easily be a list of 50 names. If a Naming Convention has been consistently used, then at a glance the right item, Table or Query, can be selected.

The general approach with the Lezynski-Reddick Naming Convention is to construct an Object Name with a "Base Name", a prefix, and perhaps a suffix. The Base Name is descriptive of the data, i.e., a Table named "Customer" or a Field named "FirstName". The prefix is usually indicative of the category of object, i.e., "tblCustomer" and "frmCustomer" designate a Table and the primary Form which updates the data in that same Table, respectively. Likewise on a Form, a ComboBox would be named "cboState" and a TextBox "txtState". A suffix may designate the relationship of 2 of the same object. Suffixes allow tblAcctBalanceCur and tblAcctBalanceYTD to both appear consecutively in a sorted list, yet indicate their distinction. Stan Leszynski and Greg Reddick further suggest that any Base Name be used throughout a Database to indicate "connectedness". The Base Name in "frmCustomer", "qryCustomer", and "rptCustomer" imply their connectedness to a Table named "tblCustomer" and NOT "tblCustAcctBalance".

Using Lezynski-Reddick is not necessary. Nevertheless being consistent with naming is very beneficial. Just simply using names like "tCustomer", "fCustomer', "qCustomer", and "rCustomer" for a Table, Form, Query, and Report, respectively, can save a lot of time, and possibly prevent some errors.

1.B.3. Naming Conventions - the Database (and related Files)

Database Names may be created with perhaps 3 elements, according to the Convention which I use. They are
Base Name
Usage Code
Version Code

This Convention can be applied to any other Files which are related to an App, like Spreadsheets and Word Documents. The use of the same Base Name for a Database and a Word Document implies their "connectedness". Also both Files appear consecutively in a sorted File Name list. This results from making the Base Name the first element of a Name. The Usage Code, if used, is the following element. The Version Code, if used, is always the last Name element.

In an App most or all of the Files may be consecutive in a sorted File Name list. The following 6 Files constitute most of an Application, Rejected Payments, which I developed.
RejPay.Doc the Primary Documentation
RejPay.Mdb the Primary Front End for the Client, i.e., the "App"
RejPayD.Mdb the Primary Data used in this Application
RejPayI.Mdb the programming which imports the Text Files used
RejPayM.Txt a (Status) Message Text File created by the import Database
RejPayS a Windows Desktop Shortcut to RejPay.Mdb

Base Name

The Base Name is not more than 6 characters. Only letters should be used if at all possible.

Usage Code

A Usage Code character, when I use one, should immediately follow the Base Name. This is regardless of how many characters are in the Base Name. These are the characters which I use most often to indicate Usage.

Usage Codes for Access Database Files
blank = only Database in App,
OR the (Primary) User Interface program, if more than 1 Database in App (implicitly the "Production" programming, if that distinction must be made)
A = Archive programming, programming which processes Current into Historic data
B = Beta, programming, if a distinct Beta program instance is required for the User;
(the precursor to a Production Version);
removing this letter converts the copy to a "production" copy
C = Compact programming, programming which compacts Database(s) in the App
D = Data only
H = Data only - Historic
I = Import programming - programming especially for bringing data en masse into the App; data may be in spreadsheets, text files, database tables, etc.
W = Work In Progress programming, a testing copy of the program for the Developer (the precursor to a Beta Version or Production Version);
removing this letter converts the copy to a "production" copy
Usage Codes for related Files
M = (Status) Message Text File
S = (Desktop) Shortcut to some Database in the App

(Access) Version Code

The Version Code, if used, is always the last Name element. These are the characters which I use to indicate Access Database Version:
2 = Access Version 2.0
7 = Access Version 7.0
8 = Access Version 8.0
(Note that Access Help may declare Version as "Access 95" or "Access 97" but in programming Code Version must be "7.0" or "8.0", respectively.)

Usually naming a Database to include an indication of its Version is unnecessary. It is assumed that a Database and the PC, on which it is intended to be executed, have the same Version. However, there are 2 situations were it may desirable to indicate the Version in the Name. 

Version Upgrade for a specific Database may temporarily require keeping Database copies of both before and after conversion. Typically when a Database is upgraded to a newer Version, the new copy is given the same Name as the old Version. If a copy of the old Version is to be saved, it can be moved out of the way to a different Directory. But this alone will not certainly prevent one Version being inadvertently overwritten by the other.
I add a Version Code to the Name of the old copy, NOT the new. This old Database is placed in a new and temporary Directory named "Archive". Archive is made a SubDirectory of the original Directory. If all goes well, at some time the "Archive" SubDirectory and its contents are deleted. Otherwise, the old Version can be readily located and identified.

Multiple (Access) Versions of the same Database may exist simultaneously for a prolonged period. For example, there is a Database which I created to extract Table Definition(s) from a Database. This utility program is named TableD.Mdb for Table Definition. I use it in several Versions of Access. Therefore I converted the Version 2.0 copy of the program to both Version 7.0 and 8.0 - once. As a result of the Naming Convention that I use, I have TableD2.Mdb, TableD7.Mdb, and TableD8.Mdb - and NO wasted time.


2. Tables

2.A. Table Fields - General

2.A.1. Data Typing - General

I define most Fields as Text Data Type AND with "Null" as the Default Value.
I seldom use Numeric as a Data Type. Access performs arithmetic on Numbers stored in Text Fields just as easily as in Numeric Fields. There are a number of considerations regarding the management of numeric data. I choose Text Data Typing, rather than Numeric, because the former is less restrictive.
In general, a Field which may contain "Blank" Values can be defined so that either a Null Value or else a Zero-Length Value is stored. (It is not my purpose here to illuminate the subtle distinction between these 2.) I prefer the former for several reasons which I am not addressing here.
Field Length should be small and lengthened as necessary, rather than long and perhaps shortened later. The latter risks data loss if redefinition is elected, or else valuable time lost determining that there will be NO data loss.

2.A.2. Data Typing - Special

There are three Types of Data which should NOT be defined as Text. They are
Currency
Date/Time
Boolean (Yes/No)

Currency data should be defined as Currency Data Type, with a Default Value of Zero, explicitly declaring 2 decimal places.
Currency Data Type in Access is in fact a special variation of Double Precision Floating Point Number Data Type - except the decimal point is fixed, not floating. Currency arithmetic is more accurate than Floating Point.
Some Rounding Errors are prevented if the Currency Field's Decimal Places is explicitly defined as 2. The default, i.e., "Auto", Decimal Places for Currency Data Type is 4 NOT 2. So unless the decimals are explicitly declared, Access does arithmetic with 4 decimal points, but since you probably only format 2 as visible, you may see "random" rounding errors.
Currency Fields should always contain Zero, instead of "Null". Any arithmetic including even 1 "Null" Value will lead to a resulting Value of "Null", i.e., "Blank"!!! This behavior is called Null Propagation.

Date/Time data should be defined as Date/Time Data Type, with "Null" as the Default Value.
Date/Time Data Type, like Currency, is also a special variation of Double Precision Floating Point Number Data Type. The decimal point is fixed, not floating. The integer portion of the number is a Julian-type Date and the decimal portion is a 24-hour Time.
Date/Time Data Typing allows a very robust battery of intrinsic Access Date and/or Time Manipulating Functions to be available. For example, X number of days may be added/subtracted to any date with Access providing the result as a Calendar Date - adjusted (by Access) for any Leap Year, if appropriate!! Or these Functions can return the Calendar Date of say "the Friday previous to the Date in question, if that Friday is in the same Month". You get the idea.
Also formatting the display of Dates/Times allows almost anything meaningful to humans. However, errors occur with non-Date data in a Date Field when Access manipulates or displays it.

BEWARE: Normally when Access does a comparison, it is comparing one entire Field to another entire Field. This includes Date-Time Fields!! If you think that you are comparing 1 Date, without a Time, to another, and you are getting inconsistent results, think again. Access ALWAYS stores a Date AND Time in a Date/Time Data Type regardless of what is requested of it. Access will store a Default Time of Midnight any time that it is supposed to store a date only. That is, if the data source and/or input Formatting is appropriate.
There is another consideration. An idiosyncrasy of Access is that when it displays Midnight of some Date, it will not explicitly display Midnight, i.e., "0:00AM" or "12:00AM " It only displays the Date portion of the Data.

Boolean (Yes/No) Fields may be defined as Long Integer (Number), with a Default Value of "False", and formatted as "Yes/No". That is what I do.
It is true that Access has its own special (Boolean) Yes/No Data Type (a Short Integer). However, Boolean Fields in Sybase (perhaps other platforms, too) are Long Integers, not Short Integers, by Access' Definition. My preference means that the data is platform-independent. So I define an Access Field as a Long Integer (4 bytes), and treat it as a Field that contains a Boolean Value, instead of defining it as Access' Boolean Field (2 bytes). (Saving the 2 bytes, to me, is false economy.) In Access, it is OK to define a Long Integer Field with a Default Value of "False", and formatted as "Yes/No"!
Boolean Fields can contain a "Null" Value. However, this should not be allowed. The whole purpose of creating this Data Type is to explicitly indicate a Status - Yes or No. This datum should not be subject to interpretation!

Please note that the Naming of a Boolean Field and setting its Default Value is dependent upon the Field's purpose. Information is more understandable to people when declared in the affirmative. For example, more often a query is run for Active Customers, not Inactive. Therefore a Boolean Field Name in the Customer Table should be "Active" with a usual Value of "Yes". A Field Name of "Inactive" with a usual Value of "No" would convey the same information, but could be confusing.
I use the following approach to populate a Boolean Field. In the Data Entry Form, I set the Form Default Value to "True". This method saves the User a Key Stroke for a new Customer - who is likely to be Active if "New", anyway. However, in the Table Definition, the Field "Active" has a Default Value of "False". Thus at Table Level, Record(s) which are added singly or en masse are assumed to be Inactive until a User intentionally changes that status.

"Yes" or "No", "True" or "False", and -1 or 0, respectively, all mean the same thing to Access in the context of a Boolean Field. Use the Access' Reserved Words, not the numbers, for self-documenting purposes.
When displaying A Boolean Field, format it as "Yes/No".
When updating a Boolean Field in Code or Query, use "True" and "False", NOT "Yes" and "No". I have found in some contexts that Access accepted the former and refused to accept the latter.

Boolean Fields need not be Indexed Fields. This is one of the advantages of Boolean Fields - some Category of Data may be identified quickly. Queries can be highly optimized when the record selection criteria is based on the Yes/No Value of Boolean Field(s).

2.B. Table Fields - Special Purpose

There are Four Special Purpose Fields which can be very useful to the Developer. They are
Primary Key Field
Foreign Key Field
"RecordCreationDate" Field
"RecordLastChangeDate" Field

Primary Key / Foreign Key Field
"A Primary Key is one or more Fields whose Value or Values uniquely identify each Record in a Table." A Foreign Key is "One or more Table Fields that refer to the Primary Key Field or Fields in another Table." These are Microsoft definitions. Obviously a Foreign Key is meaningless without a corresponding Primary Key in some other Table. However, a Primary Key, with or without, a Foreign Key, can be very useful.
Social Security Number is like a Primary Key for the U.S. population. Each individual is uniquely identified when they become a member of the group. Also if their Name, or any other characteristics, change, their unique ID remains constant.
A Primary Key may be based on some actual datum, i.e., Social Security Number, Company Name plus City, OR some arbitrary datum, i.e., a random number, or a serial number. The Developer should give careful thought to the choice, since Access will not allow ANY duplicates in a Primary Key. (A Primary Key is a special Index.)
Including a Primary Key in the Definition of any Table is desirable, but not necessary, generally speaking. However, a Table with NO Primary Key can not be used in a Relationship, and it can be slower to sort and search.

I almost always use a Primary Key, of a specific type, with any Table. I add a Field with a Counter Data-Type to the Table to be used as a Primary Key. The Counter (or Auto-Number, beginning with Ver.7.0) Data-Type is defined "Sequential numbers automatically inserted by Microsoft Access. Numbering begins with 1. The Counter data type makes a good primary key field, and is compatible with the Number data type with the FieldSize property set to Long Integer."
Several advantages are realized with this kind of Primary Key. Since it is a number only, sorting is much faster than with letters, or mixed letters-and-numbers. Counter Data-Types can NOT be updated, i.e., data integrity is not corruptible by the User, Implicitly each Record has an indication of the sequence of its addition to the Table. The Counter-Type Primary Key does allow for the fastest Record retrieval.
I use this Field differently than some. Some Developers choose to display this Field, then allow the User to retrieve a Record based on this Primary Key's number. But I treat this as a piece of datum for the programmer's use only, and hide it from the User. I provide the User a more self-evident selection criteria, i.e., Customer Name. Then the programming uses the Primary Key to retrieve the Record. 

Primary Key Field Naming for this Counter-Type can be simple and informative. I use the same "Base Name" as the Table itself and append "ID". Thus "CustomerID" in my Database would implicitly be the Primary Key Field for the "Customer" Table, and not a "Customer Account Number" Field.
The Field "CustomerID", in context, would be the Primary Key, if located in the Table "tblCustomer". If found in "tblSalesPerson", this Field would be a Foreign Key connecting each SalesPerson with their corresponding Customer.

"RecordCreationDate" Field / "RecordLastChangeDate" Field
Two date Fields can provide the Developer with a TimeStamp of the creation and last change for each Record. I usually include both of these Fields in each Table. They are added for the benefit of the Developer. The former has a Default Value of "Now". The latter has no Default Value and must be updated by the programming. There may be instances where distinguishing 1 or more Records is necessary, and the only criteria can be one of these 2 Date/Times.
Incidentally this same information may be desirable, or necessary, to the User. If so include separate Fields for the User's purpose. It is ill-advised to confuse goals of the Developer with those of the User. Date Fields require little resources. So having 2 sets of Fields should not be an issue.


3. Queries

3.A. Query Types
Queries in Access are simply SQL Statements, which selectively retrieve or alter data. SQL is Structured Query Language. When the Query Window is opened to design a Query, Access is presenting the User with a User Interface to construct SQL. At the bottom of this Window is the QBE Grid, Query-By-Example. This Grid allows the User to use drop-down list boxes and drag-and-drop functionality to construct the components of the SQL. Switching to SQL View displays the SQL Statement which is being constructed in the Query Design Window. Conversely, If the User types a syntactically correct SQL Statement in the SQL View Window, the Query Design Window will graphically reflect that SQL!! (Actually a Union Query can not be graphically displayed in the QBE, and thus HAS to be typed into the SQL Window.)

Often a query is simply to show the User some chosen data. A very simple Query might include 1 Table, i.e., retrieve the mailing addresses from the Customer Table. However, often 2 or more Tables are involved, i.e., retrieve Customer Names along with the associated Salesperson. This would be a Query involving 2 Tables, a "Join" of 2 Tables. There are 2 basic Join Types in SQL, an Inner Join and an Outer Join.
Unfortunately the Join Type which Access selects by Default is the Inner Join. I seldom use this type because it is intrinsically misleading.

Here is the definitive distinction between Inner and Outer Joins. Access describes the former thus: "Only include rows where the joined Fields from both Tables are equal." An Outer Join is described as "Include ALL records from Table A and only the records from Table B where the joined Fields are equal."

Here is a real world example of this distinction. Suppose the User wants to view Customers and related Salespeople. An Inner Join would deliver all records from both Tables in some situations. However, any Customer, perhaps a new one, which had no assigned Salesperson, would NOT be retrieved. Likewise, any Salesperson, perhaps a new one, who had NO designated Customer(s) would NOT be retrieved. However, some Outer Join would likely satisfy the User's needs.

Effectively an Outer Join operates as though 1 or the other Table is the basis of the Query, and the other Table is lookup data, if available. Either retrieve ALL the Customers and their associated Salespeople, if there are any. Or else, retrieve ALL the Salespeople and their associated Customers, if there are any.


3.B. SQL Statement Types

The following skeletal syntax of various query types is only here as nice-to-know information. Several of these Types are in fact variations of the "SELECT…" Type. NOTE: The last character of any SQL Statement must be a semi-colon.

Append Query, multiple-record
INSERT INTO table SELECT-statement;
Append Query, single-record
INSERT INTO table VALUES ...;

Cross Tab Query

Delete Query
DELETE...
FROM table...JOIN table
WHERE...; 

Group By Query (a variation of SELECT-statement)
SELECT...
FROM table...JOIN table
WHERE...
GROUP BY...
HAVING...
ORDER BY ...;

Make Table Query (a variation of SELECT/GROUP BY-statement)
SELECT...
INTO new table
FROM table...JOIN table
WHERE...
GROUP BY...
HAVING...
ORDER BY ...;

Select Query
SELECT...
FROM table...JOIN table
WHERE...
ORDER BY ...;


SubQuery
(NOT IN Query is a type of SubQuery)
...(SELECT...)
A SubQuery must include a Select-Statement,
which is enclosed in parentheses.
A SubQuery may be used in a Select Clause,
Having Clause, or Where Clause.

Union Query (for tables or queries)
SELECT/GROUP BY-statement (or TABLE [table/query])
UNION SELECT/GROUP BY-statement (or TABLE [table/query])
UNION SELECT/GROUP BY-statement (or TABLE [table/query])
ORDER BY ...;

Update Query
UPDATE table...JOIN table
SET...
WHERE ...;


4. Forms

4.A. Access Basic Code OR Code-Behind-Form OR VBA Code: General
Access Basic Code or Code-Behind-Form or VBA Code are various names for Programming Code used in Access and Visual Basic.
Using Programming Code is an alternative to Macros. Both allow for Data Manipulation and User Interface changes to be made dynamically at program execution time. Each has its advantages and disadvantages.
Macros are fairly easy to use. Pick 1 course of action from a DropDown ListBox and there will be a subsequent choice to be made from another DropDown ListBox. The latter ListBox will be populated appropriate to its predecessor. With a little technical understanding one may accomplish a lot of programming for very little time.
However, one significant disadvantage to Macros is they allow no means of handling Program Errors. But Access Basic Code does.
Programming Errors include such things as the User entered something which was supposed to be a Date, but wasn't, as well as, the program expects to find some Table which is not available.

4.B. Access Basic Code: Specific Tips

Following are assorted Tips and Techniques about Programming and/or Forms.

4.B.1. "ComboBox: The NotInList Event"

The NotInList Event for a ComboBox on a Form is triggered when its LimitToList property is set to Yes, AND the User enters something which is not in the ComboBox list. Without an "Event Procedure", Access will give the User a generic error message.
The NotInList Event may be used to pop up a form and allow the User to add to the list "on-the-fly". I avoid doing this. I have found 2 other ways to make use of this Event.

(1) There may be situations where a ComboBox must have a selection AND the selection must be from the current list. The following Event Procedure code should suffice. This code in effect tells Access to ignore the "error", and simply replace the User's typing with the last value that the ComboBox held - no error and no error message. Add a Message Box if you like.

Sub cboCompanyName_NotInList (NewData As String, Response As Integer)

Dim ctlCurrent As Control

On Error Resume Next

Set ctlCurrent = Screen.ActiveControl
If TypeOf ctlCurrent Is ComboBox Then
Response = DATA_ERRCONTINUE
ctlCurrent = ctlCurrent.OldValue
End If

End Sub

(2) There may be situations where a ComboBox selection is optional, and the User should be allowed the choice to "erase" an earlier selection. OR the User may inadvertently open a ComboBox, where making a selection is neither required nor meaningful. Flexibility is accomplished by replacing "ctlCurrent.OldValue" in (1) with "Null", so the User can select from OR "blank" the ComboBox. Once again - no error and no error message. Using "Null" prevents the User being "trapped" into making a selection from the list.

I habitually use 1 of these 2 approaches with every ComboBox I create. I pause only long enough to ask myself: Will this ComboBox ALWAYS require a selection? If I'm not sure, I use approach (2). The Code segment "Dim ... End If" is generically designed, i.e., it works without specifically naming the Control that invokes it. So I copy-and-paste "Dim ... End If" from some other ComboBox, thus avoiding typing errors. I also avoid another type of error with the Code itself: suppose I accidentally copy-and-paste this code to a Control that is not a ComboBox?. "TypeOf ... Is ComboBox" prevents execution of this Code, if this Code is invoked by anything besides a ComboBox.

4.B.2. "Null Values: Reducing Confusion About IsNull"

Null values and If ... Then statements do not always produce the desired results. What we intuitively think of as a "Blank" Value has some subtle interpretations when it comes to Access (and the computer). I will not attempt to explain these subtleties. However, if you would like to learn more, I do recommend this: search Access Help for "nulls: an overview". You will be shown a topic entitled "Working with Nulls and Zero-Length Strings". In the article is this sentence "To enter a null value into a field, set the field's Required property to No, and then leave the field blank." (I use this sentence to conceptualize what a "Null" is.)

As to my suggestion, some of us don't get the results we expect with statements like:
(1) If expression = Null Then ... do something ...
(2) If expression <> Null Then ... do something ...
(3) If IsNull(expression) Then ... do something ...
(4) If Not IsNull(expression) Then ... do something ...

I use the following technique (where appropriate).
The trick is "do something" with the "If IsNull()" AND ALSO after "Else" instead of after "Then":

If IsNull(expression) Then
Else
... do something ...
End If

I hope this may reduce your problems with Null values; I get results this way, which are intuitive.

- John Jarvi