Override AutoNumber

[GordonRS]

Hi all.

I have inherited a database which contains a table of tasks and where the TaskID is an autonumber. Quite a bit of code has been written that refers to specific values but unfortunately one of those is not contained within the table (for background the reference is 10 and where the current value is now 1683).
So, I want to add TaskID 10 but of course I cant be specific about that value. If I change the field to be just a number, I can add the record but then of course Access won't allow me to change it back to AutoNumber.

Any ideas?

Regards,

Gordon

 

[RuralGuy]

I'm pretty sure you can append a record with the AutoNumber field set to any number not already in the sequence. Be careful you don't reset the AutoNumber seed. It sounds like Referential Integrity was *not* being enforced.

 

[neileg]

Autonumbers are a poor choice for a field that has a meaning to the user. Think about creating your own sequential numbering field and let the autonumber do its own thing and hide it from the users.

 

[GordonRS]

Thanks for the replies.

Unfortunately the append doesnt work - access complains about key violations.

Not a good feature of the database but its a pita to have to rework it just to get this record in.

Regards,

Gordon

 

[gemma-the-husky]

append should work

are you using a stored query. if so set a column to a value you know is not in the table, and set the field in the appendee table to be the autonumber field.

the only reason for this not to work is that you are either infringing another non-duplicating key in this table (which will obvioulsy be the case if there is such a key, as the data is otherwise exactly the same), or it is causing another cross reference problem with another table (not so likely)

 

[GordonRS]

Hi Gemma,

Thanks for the post. My fault - there was another foreign key constraint which was the problem - I just made the assumption that the error related to my autonumber field. That'll teach me!

Thanks all for the help.

Regards,

Gordon