Here's a new problem I've never encountered.

I have a button on an unbound form that simply appends a new record to a table (via an append query) that's not connected to the form at all. I keep getting an error message from the append query that the record cannot be added to the table due to key violations. The field that is restricted is the primary key. It is the autonumber field called lngRecNo. My query does NOT try to assign a value to this field at all because it will be generated on its own. The query does fill the other fields which have no restrictions.

Why would I get a key violation error when the autonumber primary key should be filling in on its own and Access would not violate its own rules when creating a new number? Any ideas?

I'm using Access XP (2002). Thanks in advance.

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Greg,

I've run into this before. Even though you have no restrictions on the other fields in the table, are any of those other fields related to another table? If they are, and you have the cascade update/delete property set on the relationship, it won't allow you to add a record.

Kevin

dartcoach

Kevin, negative on both counts. The query is in the front end database and the table is on the back end (i.e. split dB). It's a very simple append query.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Greg,

The only times I have come across this sort of error are when as Kevin states there is a relationship that causes a violation of data integrity or when I inadvertently tried to append the wrong fields. Unfortunately Access doesn't always give you a good explanation on the error. The last occasion this occurred for me was due to exactly what Kevin has described. I too have my database split but on the table end there was a relationship that had been created to facilitate a cascading delete. If your sure there are no relationships tripping you up then it has to be the data your appending or the fields your appending to. I think I would set up a select query based on you original criterea and examine the results. If all seems fine then I would export it to a spread sheet then manually pull that back into the tables. If that all works then I would say its something to do with the formulation of your append query. Not a very presise answer i know. Hope that it helps though.


Jim

OK... solved but definitely put this on the "Wall of Weird" as Chloe Sullivan would put it.

The autonumbering system in Access was violating itself. So I opened the backend dB and made a copy of the table. I delete all records in the original table. I compacted and repaired the database. I put back all the records in the table. The autonumbering started back up with the number 1 to 899. I closed the BE.

I opened the FE and now it is working as it should. Records can now be added to the BE because I reset the autonumbering. Weird, huh? That has never happened to me before.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

very odd. Sounds like a corrupted relationship but I haven't come across that before.