Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: Access 2002 "Error 3071"

    I am working on a database with a one-to-many relationship.
    As of now there are four sub datasheets linked to my main table.

    My issue is this...
    The third sub datasheet displays this error when data in entered into it...
    "Error 3071"
    "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

    I am a new Access user and don't have many ideas as to how to fix this problem.
    Microsoft's Database tells me that "error 3071" is related to disk space however I'm not sure if this is even related to ver. 2002.
    http://support.microsoft.com/kb/186543/en-us

    I have had no success in making progress on this problem, all help is GREATLY APPRECIATED.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    How is data being entered into it (ie. via a form or via directly into the table itself?)

    If via a form (or subform), what kind of equations are behind the scenes or what kind of queries are being utilized. This error can often be associated with either code, the expression on a field on a form, or an expression in a query which is too complex. If it's found to be a query, post the sql statement. Otherwise, post any code which is happening. Does it happen after entering data in a specific field or when a specific field get's the focus. Any additional info you can give us versus saying it just happens when data entered into it would be extremely helpful.

    If via a table, are there any type of default equations set for any fields? Again, any additional details you can give us on when the error is specifically happening would be helpful.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like an errant Validation Rule to me.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2008
    Posts
    3
    Data is being entered via Form.

    I went threw and entered data into each value one-by-one to see which creates the problem and found that it is specific to the third subdata form.
    However it occurs when data is entered into any of the fields in this subform.

    -After entering data and saving, everything appears to work correctly. The data is saved and it all stays associated. The problem occurs when the form is reopened. What happens is; the "error 3071" displays and subform three turns from a normal appearance to a blank white space.

    -The same problem happens when I use the tables to enter the data.

    -I recreated subdata three, even reducing the number of fields, but the same thing happens.

    As far as equations and queries, there are not any. Everything is in its basic form, simple data entry. The only thing I have done is create a relationship between one of the fields in the tables.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have you tried compacting & repairing?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by armand.francois
    After entering data and saving, everything appears to work correctly. The data is saved and it all stays associated. The problem occurs when the form is reopened. What happens is; the "error 3071" displays and subform three turns from a normal appearance to a blank white space.
    hmm..Ok - I've seen this before where the form goes to a blank white space but darned if I can't recall the situation but the overflow error 3071 is usually associated with bad data or a corrupt table (or an overly complex sql statement or code which tries to calculate on bad data or an expression/equation too complex to resolve).

    Overflow type errors though which I've very often seen are common to data in the tables associated with incorrect date values. For example, a datetime defined field type which has a date in one or more records with a value such as 12/07 versus 12/02/07. If you have any datetime defined fields in any of the tables, look for bad date values!! If you are dealing with large recordsets, these can sometimes be difficult to spot but very often lead to an overflow type error. I'm betting you have a bad date value in one of the records. Especially this statement which it strongly leads me to believe....

    Quote Originally Posted by armand.francois
    - The same problem happens when I use the tables to enter the data.
    This would make sense in that you open the entire dataset to enter data directly into the tables or I'm guessing your subform doesn't have criteria in it and is also returning the entire data set when the form opens. A bad data value in one or more fields would then cause this error. I'm guessing if you had criteria in the subform's recordsource, you would possibly only get the error when you went to certain records or when it has to decipher/requery the entire dataset in the table.

    You mentioned also that you established relationships between the tables. Try running an unmatched query between the tables to see if you have any orphaned records in the relational table on the field you joined. Although rare, I've seen it where MSAccess let's you create a relationship between tables where orphaned records existed and this can be an issue. You can try removing and re-creating the relationship but first make sure you don't have records in the relational table which don't match records in the main table. If MSAccess got confused on the relationship when it was created, this would be highly suspect to causing the 3071 error.

    Also, does this happen regardless of which record you're on or does the error occur only on specific records. Is your subform query using criteria such as: Select * from MySubformTable where IDField = " & Forms!MyMainFormName!IDField & "". If it doesn't, you should use criteria which limits the records on the subform to the ID field on the main form as this would show up if the subform returns all records and then filters to the matching record on the mainform. You should always use criteria on a subform's recordsource query to limit the records returned to only those which match the joining ID field on the main form. Have you also tried creating a new empty table for the subform just to test to see if perhaps something with the table and the relationship to the main table itself might not be an issue? I'm betting if you created a new table for the subform, the problem would go away and you would discover the issue is not with the subform. To resolve your issue, you could create a new table for the subform and then run an append query to append the data from the other table to the new table (making sure you establish the relationship between the tables before running the append query - use the referential integrity option when establishing the relationship.) This might also help you spot any data issues in the table itself as the append query will most likely error out on bad data values or orphaned records.

    I'm assuming that you also have an autonumber field in the table for the subform. If not, create one - you should always have an autonumber field for any data tables. Not that it's likely the cause of the error but I'm also guessing you have your relationships defined correctly for the tables joining on an ID type field. Are your tables linked by a numeric ID field or are they linked by a text type field? Either way works but I'm throwing a few things out there.

    Another possible factor is any default equations (or validation expressions) for fields in the table design or any lookup tables associated with any fields (if you have anything setup in the Lookup tab for any fields in the table design - remove these as this is a terrible (horrible, horrible) feature and only leads to confusion and problems! You may need to create a new field and write in values for the ID stored though before removing anything setup in the Lookup tab.) The format or input mask for the field could also be an issue but unlikely.

    Other things which I would check (although unlikely since it happens even when you enter data directly into the tables), are to see if anything with the subform properties are perhaps set incorrectly (ie. the popup or modal form property is set to true.) I'd also check in the design view of the mainform (clicking on the subform) to see if the "Link Child Fields" and "Link Master Fields" have the field in them which links the two tables. Without going to the extent of creating a new mdb and importing all the objects into the new mdb, I also wonder if you aren't using any modules for the subform which could be a factor. I've seen it where a module such as the mousehook module coded in the subform has caused issues and isn't needed for the subform - just the mainform.

    I would really like to see the mdb to help resolve your issue. If you can zip and upload, one of us could probably find a solution to your problem quickly.

    I don't think disk space is the issue but again, make sure you have sufficient disk space remembering that an mdb needs to create an additional ldb file (although small) to operate. Again though, I really think the root of your error stems from an issue with the data itself. If compacting and repairing as StarTrekker suggested (which you should get in the habit of doing fairly often) doesn't solve the issue, look at the data (out of curiosity, approximately how many records are in the tables and how large is the mdb file?) Make sure to make backups of your mdb beforehand.
    Last edited by pkstormy; 07-21-08 at 23:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jul 2008
    Posts
    3
    Thank you both for the information.
    What I ended up doing was to remake the entire DB from scratch...

    The DB was created by someone else; I have been adding forms to make it easier to enter information.

    ... My Scratch BD is working flawlessly, but I believe there is already too much information in the original to do a transfer, ill have to check into this (I am having problems copping data / any suggestions?)

    This morning I recreated the subform that was giving me problems and it is now working. So as of now the original BD is working as needed.

    As said before, I am completely new to this program, however am very eager to learn new things!
    I have attached the scratch DB I created... would anyone be interested in looking it over and giving me suggestions on how I can make it function better?
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •