Results 1 to 3 of 3
  1. #1
    DNRTech is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    32

    Strange validation error entering NULL data into SQL tables from Form

    HI, I first want to say thanks to this forum and its users, such a great resource!



    I am currently having an issue with entering null data into a linked SQL table via a form. Not all null data has an issue just certain fields that have conditions as assigned in SQL.

    The fields are populated by autofilling from a query of an Access table whos values based on values from SQL domain tables. When the first record is entered, the other fields are filled from an query using the method described by Maximus in a recent post. The fields populate fine for field in which there is dataand where there are null values in the query fields are not filled. When I try to complete a record in which the query populates a field with no data, from the query based on the Access table, I get the following error:

    Could not execute query; could not find linked table.

    [Microsoft][SQL Native Client 10.0][SQL Server]FieldInQuestion (#0)[Microsoft][SQL Native Client 10.0][SQL Server]NextFieldInQuestion (#0)[Microsoft][SQL Native Client 10.0][SQL Server]NextFieldInQuestion (#0)[SQL Native Client 10.0][SQL Server]NextFieldInQuestion (#0)[SQL Native Client 10.0][SQL Server]NextFieldInQuestion (#0)[SQL Server]Invalid object name 'ReT13' (#208)
    Here's the real annoying part but a clue. If I put a space in each box with null data I don't get this error and I can proceed to the next record. Also, for records autofilled by the query that do not have any null values they are entered fine without error.

    It seems that the problem is coming from the way that access is entering null values from the form into the SQL table. I have tried the following:

    1.Changed the Allow Zero Length to NO in the Access table that values are entered to SQL from.

    2.Adding a table in SQL that is has the assembled domain values and queried from that table linked back to Access in the same way I queried the original Access table.

    3.Setting default values to space and an "&" in the input mask property for each field that is giving me trouble.

    4.Other things I can't remember

    Same error each time!

    I think it has to do with how access enters null values from the form and not how it queries the info from the table. I'm almost completely stumped here. Let me know if anyone has an tips, insights, or resources or if anyone has any questions.

    Thanks!

  2. #2
    DNRTech is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    32
    I have some new insight as to the cause of my problem. It turns out the the linked table that cannot be found as listed in the first line of the error is a temporary table produced by the field trigger in order to validate the entry in SQL. It had been called Re13. When entering data into the table directly I don't get this error. It only occurs when entering the data into a form via the lookup table autofill query mentioned above. The values in the lookup table are indeed null and null values are required by the validation trigger when no data is present. I think this one might be out of my league. I'm going to rethink my approach...

  3. #3
    DNRTech is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    32
    The error was coming from the validation trigger in SQL when the control attempted to populate itself with a null value from the lookup table. I ended up using If/Then statements to populate the values instead of the lookup table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Entering data in forms with lookup tables
    By gretsch in forum Forms
    Replies: 1
    Last Post: 11-16-2010, 11:44 AM
  2. Data validation warning vs error
    By mafrank101 in forum Access
    Replies: 3
    Last Post: 07-14-2010, 05:05 PM
  3. Replies: 0
    Last Post: 03-15-2010, 02:38 AM
  4. Entering duplicate data in Form
    By cotri in forum Forms
    Replies: 1
    Last Post: 01-06-2010, 11:45 PM
  5. Replies: 7
    Last Post: 11-22-2009, 02:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums