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.

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 2000/2002/2003 database uses linked tables and is very slow. Why?
There are 2 features that slow down these versions of Access.
(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. From the TOOLS/Options menu choose the General Tab. On the right side, uncheck the items in the Name AutoCorrect area.

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 HELP, in the INDEX enter "Update." Find the help item called "When can I update data from a query?"
In Access 97 HELP, in the INDEX enter "Update." Under the "update queries" category, find the item called "data that can be updated."

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