• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Using Access 2007 How to prevent a record account number from being reused after it has been deleted

I need to prevent an account number that is created from being re-used and issued to another record after it has been deleted from a database.
0
Delores_C
Asked:
Delores_C
  • 2
2 Solutions
 
xtermieCommented:
If you simply delete it and the field type is number or text for example, anyone can type in a deleted number as it will not be there.
A simple solution would be to maintain the deleted account numbers in a separate table and create a lookup validation to that table that wont allow any number from that table to be reused as an account number.  Another simple way around this is not deleting the account number (as you will loose any other transactions associated with it and any history around it) is to include in your table where the Account number is the key an additional field, like a status and have values like Active, Inactive, Deleted etc. associated with it. If this field value is Deleted the account number should not appear in any list for a transaction.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Why would they be re-used?

The system you use to number the values in that field should be always unique.
(ex. an autonumber field, or a system that auto-increments: ab01, ab02, ab03, ...etc))

But, you have not given us any details on this table or this field, or the purpose of this table...

As always, can you First, take a step back and provide some background information...?

Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
The issue with Looking up the "used" values is that once the main list an the "deleted" lists get very long..., this will begin to take a longer and longer time (as the system will have to search more and more records for dupes)

Again, we need more info...


If this is the parent table, then your Account number should be auto-incremented in some way.

If this is in a child table, then you will have to provide more info on this system...

Jeff
0
 
hnasrCommented:
Before update of a new id, check if was deleted. If yes prompt message.
Deleting an existing record, record the id info in the accounts_deleted file.
Check this sample database.
checkDeletedIDs.accdb
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now