ms access cant append all the records due to key violations

Hi,

question:

I would like to catch and handle this error (on Error doesn't catch this type of error)

How can I catch this error?

more details:

I'm running a function that inserts records into a table .

while something

mySerial = DMax("serial", "myTable") + 1
sqlstr = " INSERT INTO myTable ( serial SELECT " & mySerial & "; " )
docmd.runsql sqlstr

wend

On same cases the function halts on "ms access cant append all the records due to key violations".

Checking this further reveals that the that serial generated is already occupied - probably in the case where another user created a new record between generating mySerial to the point where actually runsql tried to commit the new record insert (?)

Can I see your database?

There is one more reason this error can happen...

You get this error message when you try to insert empty strings into a field that has its AllowZeroLength property set to No. Do this

1. Open your destination table in Design view.
2. Set the AllowZeroLength property of all text fields to Yes.
3. Save the changes to the table, and then close the table.

Try now...

Hi,

Its a MySQL DB connected using ODBC, however the AllowZeroLength is already set to YES on all text fields.

Any ideas how to catch this error?

I would like to do something like on Error goto handleErr and there to display the problmatic record on the form and then to resume the rest of the while loop, this will be a good solution for this issue, but I cannot seem to catch the error at all.

Thanks!

Anything? Anyone?

Why are you generating the serial yourself, rather than letting the database do it automatically (by using an Identity/AutoNumber/Serial data type) ?

That would not only take less code, but would also avoid the error and other issues with serial numbers that Identity etc deal with.