Common Access Tech Traps

ALERT! SERIOUS BUG IN JET 4.0
If you are using Access 2000 or later, and your database uses linked tables, be aware that there is an "Autonumber" bug. The bug occurs when you delete a row or rows from the table, then insert a row or rows into that table, in that order. You will know you are experiencing the bug if you get an error indicating that you are trying to create a duplicate record. What happens is that the new row is being given an existing autonumber, and since that is disallowed by Access, you are prompted with an error message. Microsoft is aware of this problem and says it is with the "Critical Issues Group," however they have made no commitment to fixing it as of yet. (April 2004)
The work-around is either not to use autonumbers in linked tables where you may need to delete then insert back to back, or to execute these operations as queries in the back end database.
January 2012:As far as we know, this bug still exists.

1. Why do I see #NAME# or #ERROR# in my text box instead of the data?
The text box is bound to a field that is spelled wrong, or doesn’t exist, or you are using a function or calculation as the Control Source and have used it incorrectly.

2. My Access database uses linked tables and is very slow. Why?
There are 2 features that can slow down linked tables:
(A) Table Property: Subdatasheet. For each new table you create, the default for this property is:AUTO. Open the table in design view. Click on VIEW/Properties. Find the SubDataSheetName property and set it to NONE.
(B) Database Option: Name AutoCorrect.The defaults are set on. You want to turn them off. Access 2000/2002/2003:From the TOOLS/Options menu choose the General Tab. On the right side, uncheck the items in the Name AutoCorrect area. Access 2007: Click on the "Office" button on the upper left. On the very bottom, click "Access Options.-->Current Database" Scroll down to the Name Autocorrect Area and uncheck all three options. Access 2010: Click on the "File Menu" button on the upper left. On the very bottom, click "Access Options.-->Current Database" Scroll down to the Name Autocorrect Area and uncheck all three options"

3. I created a query but it won’t let me add or change the data.
A query that involves more than one table can cause this. The Access HELP feature has a good description of when you can/can't update the data in a query.
In Access 2000/2002/2003 HELP, in the INDEX enter "Update." Find the help item called "When can I update data from a query?" In Access 2007/2010 the Table Of Contents item "Query" has a topic about updating:"Restrictions on fields that can be updated." This will explain what you need to know.

4. How do I get rid of that black arrow that appears on the left side of all my forms?
This is a form property called “Record Selectors.”
Open the form in design view. Click on VIEW/Properties. In the properties window, click the Format tab. Find the Record Selectors property and set it to NO.

5. Why is every other page of my report blank?
The width of the report is too wide for the paper/margin. Either reduce the left/right margins of the report by using the FILE/Page SetUp menu, or, drag the right edge of the report to the left to reduce its width. (Or, do both.)

6. I can’t seem to get an ampersand (&) to show on a label. I get an underscore instead.
For every ampersand you want to appear on a label, enter 2 consecutive ampersands.

7. How can I use a parameter query to get ALL records when I want them?
In the column for the field you are filtering, enter: like “*” & [Enter Text] & “*”
Instead of “Enter Text” write whatever you want your users to enter.
This works by allowing you to press the ENTER key when you want ALL the values for the field you are filtering. If you want a specific value, you can still enter that value.




Are You Stuck In An Access Tech Trap? rachel@rlevineconsulting.com