Solved

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

Posted on 2012-03-21
4
247 Views
Last Modified: 2012-03-23
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
Comment
Question by:Delores_C
  • 2
4 Comments
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 125 total points
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 30

Accepted Solution

by:
hnasr earned 125 total points
Comment Utility
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Outlook Free & Paid Tools
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now