CODEWITHSUNDEEP
validationms-accessappendms-access-2013
user4734199
user4734199

Reputation:

Append query failing due to validation errors when no validation rules exist (Access)

I am trying to append data from one table to another in Access 2013. The destination table, [ActionItems], has no records yet.

The source table, [Students], has the necessary columns: [AutoID] (Autonumber), [Action Status] (Short text), [Action Status Last Updated] (Date/time), [Action Status Description] (Short text).

I have set the destination table to match the data types and fields (though the field names are slightly different, I have joined them appropriately in the append query). The [AutoID] field in the destination table is set to Number, Long Integer, as I've read suggested elsewhere.

If I view the append query in datasheet view, I see 731 records, just as I should. But when I try to run the query, I get the following error:

"Microsoft Access can't append all the records... set 0 field(s) to Null due to a type conversion failure... 0... key violations... 0... lock violations, and 731 record(s) due to validation rule violations."

There are NO validation rules in the destination table! Both text fields allow for zero length, and the [AutoID] field has no default value. I don't understand what I could be missing.

Here is my SQL (I realize my column names are rather wordy):

INSERT INTO [ActionItems] ( AutoID, [Action Status], [Action Status Last Updated], [Action Status Description] )
SELECT [Students].[AutoID (Do not use)], [Students].Status, [Students].[Status Last Updated], [Students].[Status Description]
FROM [Students];

If it helps, here is a link to a stripped-down version of the database: https://drive.google.com/file/d/0BysgnYaEVPnJemNnOUc3ZmFsWXM/view?usp=sharing

I have removed all other tables and any identifiable information. The only other major difference is that the primary key in the [Students] table was changed from a composite key ([First Name], [Last Name], and [Email]) to a single primary key ([AutoID (Do not use)]). I made no changes at all to the [ActionItems] table. Even with these changes, the error still occurs exactly as previously described.

Upvotes: 1

Views: 2556

Answers (1)

HansUp
HansUp

Reputation: 97101

The destination table, ActionItems, includes a field named Action Status Last Updated By. That field's Required property is set to Yes, and its Default Value property is blank. That means any time you add a new row to that table, you must supply a value for Action Status Last Updated By.

Notice your INSERT statement does not supply anything for Action Status Last Updated By. Therefore that INSERT does not add any rows.

In my copy of your database, I changed the field's Required property to No. After that change, executing your INSERT query added 731 rows to ActionItems ... one for every row in Students.

If you prefer a different solution, you could leave Required set to True and either put something in the field's Default Value property or alter the INSERT to supply a value for that field.

Upvotes: 1

Related Questions