theory net

error messages in access

"there are two kind of people, those who run away from their mistakes, and those who learn from them"

table errors:

When you save a table you get this message if you have not assigned a field to be a primary key. This is what will happen:

Yes - Access will create a new autonumber field called ID and save the table.
No - The table is saved with no primary key.
Cancel - The table is not saved. You stay in design view and can create a primary key.

It is a common mistake to believe phone numbers are numbers. They are not. They are text. First, because of a possible hyphen/dash in the phone numbers (945-45589), secondly because no one will ever start to add phone numbers or find their averages.

If you type 945-45589 in a field with data type number you will see this message. The solution is to delete what you type and change the data type to text.

A related error that does not create an error message. You type 34.7, but Access changes it to 35. The problem is that you have chosen data type number with field size long integer, while you need field size double. So go back to the design view and change the field size.

Entries in primary keys have to be unique. Example: if MemberID is a primary key you can't repeat the number 7. If you do, you get this message. The solution is to delete the entry and type a unique value.

You will also get this error message if you set a field to primary key after you have entered some data if there are repetitions in the primary key field. The solution then is to take off the primary key, remove the repetitions in data view, and finally go back to design view to set the primary key.

Note: sometimes when you want to delete a record you get this error message. The sad fact is that Access will not allow you to delete the record before you have fixed the problem. So, just type any odd value in the primary key field and then you can delete.

A primary key needs a unique entry in every record and an empty or null value is not accepted. Simply type a unique value to solve this problem.

query errors:

When you use more than one table in a query you have to relate them on a common field. If the field is given a different data type in the two tables, you'll see this message. The solution is to go back to the tables' design view and make sure the data types and field sizes are the same.

You may have entered a criteria in a way the program is not able to comprehend. "Invalid syntax" means "wrong grammar." Click here to learn how to write criteria.

The string you typed can not be accepted.

When you append, the added records sometimes have the same values for certain fields. For instance you may afterwards have two records with FirstName "Peter". That is a problem if the field in question is a primary key since unique values are needed. The solution is to click "No", remove the primary key and append again.

Note: If you try to append more than once to a table with a primary key this error is bound to happen. If that is the case, praise yourself lucky for the warning, click "No" and do nothing further. :)