Not delete record, hide/deactivate?

[behedwin]

Hi

I have a set of forms connected to my tables.

Currently i let users remove records.
But id like to stop that and instead find a way to hide or deactivate the records instead of running the delete command.

Does access support something like this?
Or do i have to build something?

If i have to build something... im thinking of having a archive table for every table i create and move records to them instead of deleting... but this will be quite messy i believe....

any ideas on how to do this?

another idea is to create a column that gets an value "INACTIVE" and then i need to filter everything i do so those records are not shown... but i have no clue if this is doable or how i should approach it.

 

[xenou]

I would probably prefer to make sure that the ID you are using is never missing - in which case I would expect no errors on this SQL command. Assuming that this is not mission critical of course.

 

[Micron]

I believe that should any issue arise, such as the record being locked, you wouldn't know it. Not sure of that example per se but it will do to get my guess across, which is that it has to do with more than just bullet proof sql.

 

[xenou]

That's fine. I just don't think it's necessary in all situations that's all. I usually don't use that option. Shoot me.

 

[Micron]

Shoot me.

Rather flippant for a moderator?
Never said it was necessary in all situations but I did test 2 without it.
Put a lock on the record, ran an update sql and no message.
Put an index on a field (no dupes); attempted to duplicate a value and no message.
Added dbFailOnError and raised an error message both times. If there is a failure half way through the updating, you can't roll back without it and the companion transaction.

You're entitled stick to your habits. I just don't see why anyone would take the risk considering how easy it is to just add it for protection, regardless of whether or not a transaction is used.

 

[xenou]

My contribution was simply that it would be better to do something with the error. Since you advise using this option I just wanted to point out that if errors are uncaught then may end up with users confused by what happens at the time of failure. That's all. Whether it's needed or not, who knows. I'd need to know a lot more about how this database is used and what is going on with these forms and tables. Personally I consider not updating a locked record and not duplicating a key "correct" behavior in a lot of situations - Access is doing exactly what I'd expect. Like I said, it depends on the situation whether I'd need to know about it and mostly in my work it wouldn't.

 

[behedwin]

Thanks allot for both of your help!

Me as all new to access, just been working with it for two weeks. Clearly there are areas i have not yet even started to think about. My approach is often to build what i need and solve problems on the go. Error handling i never really understood in excel. So ill get to this soon ??.