Protecting the autonumber from being deleted when record is

[jac123]

I am using MS Access-2003. I want the user to be able to delete a record from form view. I have a field called "Serial #" that is an auto number. Is there a way to protect the auto number so it does not get deleted when the record is removed?

Thank you for your help.

 

[lameid]

I'm not sure why you would want to keep a record but delete it. Seems like opposite things..

You might change a status field on an Invoice from Active to Cancelled and query accordingly.

You might have a disposal date for an asset and still keep it and again query accordingly.

Does that help?

If you really want to for some reason (and I can't imagine a good one)...
You could remove delete permissions from the table and write a query that updates all the values but the autonumber to null or the default vale. Then put that button on the form. It would now be the only way to 'delete' something.

 

[KenReay]

Not sure what you mean.

The autonumber would not be re-used anyway. I am not totally sure about the situation where you delete the "last" (ie highest number) autonumber record, then compact the db and add another record, then it may re-use the autonumber which was jsut deleted, I have not tried it.

Perhaps, instead of deleting the record, you should have a colmn which marks the record as deleted, and then hide the "deleted" row from the user(s) by having a query which excludes the "marked" records.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lameid]

The autonumber would not be reused regardless of compacting the database.

 

[TheAceMan1]

How are ya jac123 . . .

[blue] I have a field called "Serial #" that is an auto number. [purple]Is there a way to protect the auto number so it does not get deleted when the record is removed?[/purple][/blue]

Not unless [blue]Serial#[/blue] is a [blue]PrimaryKey![/blue]. If this is true you wouldn't delete the record, but you'd Null all other fields (already mentioned by [blue]lameid[/blue]). Otherwise if [blue]Serial#[/blue] in not a PrimaryKey, you'd wind up with a bunch of orphaned records (no primarykey assigned). If [blue]Referential Integerity[/blue] is turned on, access won't allow orphan records anyway.

Perhaps if you explain better your need to preserve [blue]Serial#[/blue], we can better help you. As it stands it makes no sense to any of use . . .

See Ya! . . . . . .

Be sure to see thread181-473997