Append - validation rules? I have none!

[scubadiver007]

No records are being imported. I am getting a validation rule violation but I don't have any rules. The forename and surname are straightforward text boxes and the ID is an autonumber.

INSERT INTO table_candidate ( Cand_ID, Cand_forename, Cand_surname )
SELECT candidates.Cand_ID, candidates.Cand_forename, candidates.Cand_surname
FROM candidates;

1. Is your append query trying to assign values to the primary key field?
Could that be the source of the duplicate?

Yes but there are no duplicates

2. Do you have any other fields that are "Indexed: No Duplicates"? Any
compound indexes?

No

3. Is the query assigning values to a field that is a foreign key to another
table? Is it possible that these values do not match the values in that
other table?

No. All child tables are empty.

4. Is there a validation rule on the table itself?

I have no idea what this means.

5. Does the query attempt to assign a string with no characters in it (as
opposed to a Null value) to a text field that has its Allow Zero Length
property set to No?

Both fields are text and all records contain information

Required = No
Zero length = Yes

6. Is there a Default Value in one of the fields that is NOT being assigned
by the query? For example, a foreign key with zero as the Default Value?

No

7. Is there data that is outside the range a field can accept, e.g. an
integer larger than 32767, or a Null to a Yes/No field?

The length of the text in each record is not greater than the set limit

8. Is one of the query fields arriving in a format that Access is not
understanding, e.g. for a date, or for a currency?

No

 

[pr2-eugin]

Try dropping the Cand_ID in the INSERT statement and try again. Since it is AutoNumber Access will allocate one. You do not have to worry about it.

 

[scubadiver007]

That doesn't work either and I've tried each text field separately as well.

 

[pr2-eugin]

So this is the Query you currently have?

INSERT INTO table_candidate (Cand_forename, Cand_surname)
SELECT candidates.Cand_forename, candidates.Cand_surname
FROM candidates;

Is it possible to see a sample/stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[CJ_London]

4. Is there a validation rule on the table itself?

I have no idea what this means.

In table design validation rules can be set

Have they been set for fields you are not appending?

Both fields are text and all records contain information

Required = No
Zero length = Yes

5. Does the query attempt to assign a string with no characters in it (as
opposed to a Null value) to a text field that has its Allow Zero Length
property set to No?

Both fields are text and all records contain information

What about other fields in the table?

 

[scubadiver007]

Database with the source table (candidates) and destination table (Table_candidate) plus the append query with initial letters.

 

[pr2-eugin]

The problem is because the table_candidate has a column Cand_addrpc which is set to Required. So leaving that empty will not allow copy !

EDIT: On an extensive check most of the columns have the Required Property set to Yes.

 

[scubadiver007]

I didn't realise it would affect the whole table. Another newbie lesson learnt

:banghead:

 

[pr2-eugin]

If it is for the time being, just ignore any validation rules. Then after your import set the rules back up ! Good Luck !